- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Articles
- 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?
- Set a custom value for NULL or empty values in MySQL
- MySQL query to display only the empty and NULL values together?
- 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
- Check for NULL or empty variable in a MySQL stored procedure
- How to update empty string to NULL in MySQL?
- MySQL query to set values for NULL occurrence
- MySQL update column to NULL for blank values
- Looping in JavaScript to count non-null and non-empty values
- Check for NULL or NOT NULL values in a column in MySQL
- How to skip blank and null values in MySQL?
- Ignore null values in MySQL and display rest of the values
- Checking for Null or Empty in Java.

Advertisements