Search code examples
mysqlsqlenumsphpmyadminnull

Enum value is not set, BUT is not null

I have a row in a table that is an enum type, but can also be NULL by default.
Empty string ('') is not one of the possible enum value, and yet, after some time, I found out that all of the entries I thought to be null were actually set to an empty string.

Fixing this wasn't a problem. But, I'm willing to know how this could even happen in the first place, -to make sure I don't get any of these ever again,- but so far I haven't been able to recreate new entries with an empty string as value.

What could I have that would cause an enum value to be set neither to null nor any of the possible values?

Solution

  • If someone tries to insert a value to the enum that is not in its defined list of values, and strict mode is not enforced, then the value will be truncated to ''.

    mysql> create table t (e enum('a','b','c'));
    
mysql> insert into t set e='d';
ERROR 1265 (01000): Data truncated for column 'e' at row 1

mysql> set session sql_mode=''; -- disable strict mode

mysql> insert into t set e='d';
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'e' at row 1 |
+---------+------+----------------------------------------+

mysql> select * from t;
+------+
| e    |
+------+
|      |
+------+

    https://dev.mysql.com/doc/refman/en/sql-mode.html says:

    Strict mode produces an error for attempts to create a key that exceeds the maximum key length. When strict mode is not enabled, this results in a warning and truncation of the key to the maximum key length.

    It doesn't say so explicitly, but "exceeds the maximum length" also includes "not an element of the enum."

    https://dev.mysql.com/doc/refman/en/enum.html says:

    An enumeration value can also be the empty string ('') or NULL under certain circumstances:

    ...

    • If you insert an invalid value into an ENUM (that is, a string not present in the list of permitted values), the empty string is inserted instead as a special error value.