How MySQL handles the empty and null values for enumerations?

MySQL accepts empty values for enumeration only if SQL mode is not set as TRADITIONAL, STRICT_TRANS_TABLES or STRICT_ALL_TABLES. Otherwise, MySQL would not accept empty values and throws an error. As we know that each enumeration value is having an index value, the empty value would have 0 index value.

Example

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

mysql> Insert into result(id, name,grade) values(100, 'Raman', '');
ERROR 1265 (01000): Data truncated for column 'Grade' at row 1

Now, after changing SQL mode we would be able to insert empty string as follows −

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

mysql> Insert into result(id, name, grade) values(100, 'Raman', '');
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> select * from result;
+-----+-------+-------+
| Id  | Name  | Grade |
+-----+-------+-------+
| 100 | Raman |       |
+-----+-------+-------+
1 row in set (0.00 sec)

MySQL accepts NULL values for enumeration only if we do not specify NOT NULL with ENUM column. As we know that each enumeration value is having an index value, the index value of NULL is NULL.

Example

mysql> Insert into result(id, name, grade) values(101, 'Rahul', NULL);
Query OK, 1 row affected (0.04 sec)

The query above will insert NULL value because we do not specify NOT NULL in ENUM column declaration.

mysql> select * from result;
+-----+-------+-------+
| Id  | Name  | Grade |
+-----+-------+-------+
| 100 | Raman |       |
| 101 | Rahul | NULL  |
+-----+-------+-------+
2 rows in set (0.00 sec)

Seetha

Published on 20-Feb-2018 10:56:59