😬 The Silent Gotcha in MySQL: ENUMs and Non-Strict SQL Mode

#mysql #webdev #prisma #programming

If you're using MySQL and rely on ENUM columns for validation, you might be silently inserting bad data without realizing it. Let's talk about what happens when SQL mode is not strict.

🧪 The Setup

You define a table like this:

CREATE TABLE users (
  marital_status ENUM(
    'single',
    'married',
    'unmarried',
    'divorced',
    'separated',
    'widowed',
    'never_married'
  ) NOT NULL
);

Looks solid, right? Now, let’s try inserting an invalid value:

INSERT INTO users (marital_status) VALUES ('unknown');

😱 What Happens in Non-Strict Mode?

If you're not in strict SQL mode, MySQL won't throw an error. It will:

Insert the value as an empty string ''
Silently trigger a warning
Store it as the internal ENUM value 0

Yep, your invalid input just became... nothing.

SELECT * FROM users;
-- Output:
-- | marital_status |
-- |----------------|
-- |                |  <-- EMPTY STRING!

🔍 Why This Happens

Internally, MySQL maps ENUM values to integers (and enum in MySQL has index starting at 0 docs):

Value      Index (internal)
''(empty)  0
'single'   1
'married'  2
...        ...

When you insert an invalid value, MySQL stores 0, which corresponds to ''.

🔥 How to Avoid It: Enable Strict Mode

Use strict SQL mode to make MySQL behave properly:

SET sql_mode = 'STRICT_TRANS_TABLES';

Now try that bad insert again:

INSERT INTO users (marital_status) VALUES ('unknown');
-- ❌ ERROR 1265 (01000): Data truncated for column 'marital_status' at row 1

Perfect. You’re now protected from silent failures.

💡 Pro Tip: Make It Permanent

Edit your MySQL config file:
On Linux (/etc/mysql/my.cnf) or macOS:

[mysqld]
sql_mode=STRICT_TRANS_TABLES

Then restart MySQL.

DEV Community