Example
Examples of what happens when NULL and 'bad-value' are stored into nullable and not nullable columns. Also shows usage of casting to numeric via +0.
CREATE TABLE enum (
e ENUM('yes', 'no') NOT NULL,
enull ENUM('x', 'y', 'z') NULL
);
INSERT INTO enum (e, enull)
VALUES
('yes', 'x'),
('no', 'y'),
(NULL, NULL),
('bad-value', 'bad-value');
Query OK, 4 rows affected, 3 warnings (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 3
mysql>SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1048 | Column 'e' cannot be null |
| Warning | 1265 | Data truncated for column 'e' at row 4 |
| Warning | 1265 | Data truncated for column 'enull' at row 4 |
+---------+------+--------------------------------------------+
3 rows in set (0.00 sec)
What is in the table after those inserts. This uses "+0" to cast to numeric see what is stored.
mysql>SELECT e, e+0 FROM enum;
+-----+-----+
| e | e+0 |
+-----+-----+
| yes | 1 |
| no | 2 |
| | 0 | -- NULL
| | 0 | -- 'bad-value'
+-----+-----+
4 rows in set (0.00 sec)
mysql>SELECT enull, enull+0 FROM enum;
+-------+---------+
| enull | enull+0 |
+-------+---------+
| x | 1 |
| y | 2 |
| NULL | NULL |
| | 0 | -- 'bad-value'
+-------+---------+
4 rows in set (0.00 sec)
