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 06:26:59

• Related Questions & Answers
• Return only the non-empty and non-null values from a table and fill the empty and NULL values with the corresponding column values in MySQL?
• MySQL query to display only the empty and NULL values together?
• Set a custom value for NULL or empty values in MySQL
• MySQL query to convert empty values to NULL?
• Implement two conditions for a single column in MySQL for null and empty value
• Display 1 for NULL values in MySQL
• MySQL query to set values for NULL occurrence
• MySQL update column to NULL for blank values
• Check for NULL or empty variable in a MySQL stored procedure
• Checking for Null or Empty in Java.
• Ignore null values in MySQL and display rest of the values
• Looping in JavaScript to count non-null and non-empty values
• How does pandas series argsort handles nan values?
• How to skip blank and null values in MySQL?
• Support for Enumerations in Python