MySQL ENUM

The ENUM data type in MySQL is a string object. It allows us to limit the value chosen from a list of permitted values in the column specification at the time of table creation. It is short for enumeration, which means that each column may have one of the specified possible values. It uses numeric indexes (1, 2, 3…) to represent string values.

MySQL ENUM data type contains the following advantages:

• Compact data storage where the column may have a limited set of specified possible values. Here, the string values automatically used as a numeric index.
• It allows readable queries and output because the numbers can be translated again to the corresponding string.
• It can accept many data types like integer, floating-point, decimal, and string.

Syntax

The following are the syntax used to define ENUM data type in the column:

In the above syntax, we have defined only three ENUM values, but it can be increased according to our needs. Here, we have to make sure that the enumeration values should always keep inside the quoted string literal.

MySQL allows us to define the ENUM data type with the following attributes:

NOT NULL: By default, the ENUM data type is NULL. If we do not want to allow the NULL values, it is required to use the NOT NULL property while specifying the ENUM column.

NULL: It is a synonym for DEFAULT NULL, and its index value is always NULL.

DEFAULT: When a value is not specified in the column, the ENUM data type inserts the default value. In other words, if the INSERT statement does not provide a value for this column, then the default value will be inserted. The DEFAULT expression does not allow to insert function. ENUM data type in My SQL includes the DEFAULT values as NULL or an empty string ('').

MySQL ENUM Example

Let us understand how ENUM data type works in MySQL with the following illustration. Here, we are going to create a table named "shirts" that contains three columns: id, name, and size.

The size column uses the ENUM data type and has small, medium, large, and x-large sizes. MySQL maps these enumeration member to a numeric index where small=1, medium=2, large=3, and x-large=4 respectively. Execute the following query to create a table:

Next, we need to insert the values into the table. During insertion, the enumeration values can be inserted either as the string literal or its numeric indexes, and both are the same. Execute the following statement to insert the values into the table:

Now, execute the SELECT statement to see the inserted values into the table:

The following image contains all the above query results that contain the enumeration values in string literals only:

ENUM Sorting

MySQL sorts the enumeration values based on their numeric indexes that depend on the order in which we have inserted the data in the column specification. For example, if we have defined the enumeration as ENUM ('b', 'a', '', 'c'). Then, b comes before a, the empty string comes before c (non-empty string), and NULL value comes before other values.

So, if we do not want to get unexpected result with ENUM data type using ORDER BY clause, follow these rules:

• Define the enumeration values in the alphabetic order.
• It is to make sure that the column name is in lexical order rather than index number.

The following example explains enumeration sorting more clearly. So, if you want to get the size of the shirts in a specific order, execute the statement below:

This query will give the output as below where we can see that the size of shirts is in descending order:

Limitations of ENUM Data Type

The following are the disadvantages of ENUM data type in MySQL:

1. If we want to modify the enumeration values/ members, it can be done by rebuilding the entire table using the ALTER TABLE command. It makes expensive use of our resources and time also.

2. We cannot use an expression with enumeration members. For example, This CREATE statement does not execute because it uses CONCAT() function for creating enumeration members.

3. We cannot use the user variable as an enumeration member. It can be seen in the below example:

4. It is recommended that we should not use the numeric values as enumeration members.

5. It is complex to get a complete information enumeration list because we need to access the information_schema database.

6. We may face an issue during the porting of ENUM to other RDBMS because many databases do not support this data type.

7. We cannot add more attributes to the enumeration lists.