mysql - ENUMs and sql_mode

I've been doing a few experiments with ENUMs and SQL_MODE.

Not a lot of people realise that in MySQL, an ENUM can actually contain one other value as well as the specified ones - no, not NULL, but ANOTHER value, specifically, the empty string ''.

This can lead to trouble. The empty string is entered by MySQL when it tries to insert an invalid value into the column. It does also give a warning, but nobody takes any notice of those right?

Demo schema:

CREATE TABLE enumtest (
 id int not null auto_increment,
 name varchar(100) NOT NULL,
 status ENUM('ok','broken','decommissioned','narnia'),
 PRIMARY KEY(id),
 KEY(name),
 KEY(status)
);

Now let's try some inserts...

INSERT INTO enumtest (name,status) VALUES
 ('Mark','ok'),
 ('Fred','broken'),
 ('Bob','decommissioned');

So far so good. Now how about:

mysql> insert into enumtest (name,status) VALUES ('bust','wibble');
Query OK, 1 row affected, 1 warning (0.00 sec)

Ok, so it inserted something, and we got a warning. What's there now?

 SELECT * FROM enumtest;
+----+------+----------------+
| id | name | status         |
+----+------+----------------+
|  1 | Mark | ok             | 
|  2 | Fred | broken         | 
|  3 | Bob  | decommissioned | 
|  4 | bust |                | 
+----+------+----------------+
4 rows in set (0.00 sec)

So what's happened is, we've attempted to insert an invalid value (wibble) and it's put an empty string in instead (empty string is not a null, as we defined the column NOT NULL).

So what's the fix? SET sql_mode to traditional:

mysql> SET SQL_MODE='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO enumtest (name,status) VALUES ('breakme','wibble');
ERROR 1265 (01000): Data truncated for column 'status' at row 1

Much better. We can now actually get ENUMs that only contain values they're supposed to. Good.

Conclusion: use sql_mode TRADITIONAL whenever possible.