How MySQL Handles Empty and Null Values for Enumerations

In MySQL, the ENUM data type allows you to create a column with specified values like 'A', 'B', 'C', and 'D'. This feature is useful for maintaining data consistency, as it restricts entry to a specific set of values.

However, the behavior of ENUM columns can vary depending on whether the NOT NULL constraints are applied and also depends on SQL modes.

How to handle ENUM values with SQL modes

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 throw an error. As we know each enumeration value has an index value, the empty value would have 0 index value.

Example 1

In this example, we will understand what happens when we set SQL MODE as 'Traditional'

SET SQL_MODE ='Traditional';

Let us create a table the query is as follows -

CREATE TABLE Results (
    Id INT,
    Name VARCHAR(50),
    Grade ENUM('A', 'B', 'C', 'D')
);

When we insert empty string values into the table MySQL will not accept as if we have set the SQL MODE as Traditional which enforces strict data entry.

INSERT INTO Results(Id, Name, Grade) values(100, 'Raman', '');

Following is the output of the above code ?

ERROR 1265 (01000): Data truncated for column 'Grade' at row 1

The ENUM column rejects empty strings due to the Traditional mode, which enforces strict data requirements.

Example 2

In this Example we will set SQL MODE to an empty value, MySQL permits empty strings('') for ENUM fields.

SET SQL_MODE ='';

Now, we will insert sample data to verify the Empty value mode. To do this we use INSERT command.

INSERT INTO Results(Id, Name, grade) values(100, 'Raman', '');

Let us check whether the empty value is inserted or not by fetching the data using SELECT command.

SELECT * FROM  Results;

Following is the output of the above query ?

With unrestricted SQL mode, MySQL accepts the empty string and assigns it an index value of 0.

Example 3

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

Insert into Results(Id, Name, Grade) values(101, 'Rahul', NULL);

The query above will insert NULL value because we do not specify NOT NULL in ENUM column declaration. To verify this we need to fetch the data. The query is as follows -

SELECT * FROM Results;

Following is the output of the above query ?

Since grade does not have a NOT NULL constraint, MySQL accepts NULL as a valid value and the index value of NULL is NULL.