Introduction to MySQL ENUM

In MySQL, a ENUM is a list of strings that define the allowed values ​​for a column, and the value of the column can only be one of the list of allowed values ​​defined when the column was created.

MySQL ENUM data type columns are suitable for storing a limited amount of fixed-valued data such as state and identity.

MySQL ENUM data types have the following advantages:

• Column values ​​are more readable.
• Compact data storage. MySQL stores only the numeric index ( 1, 2, 3, …) corresponding to the enumeration value.

MySQL ENUM syntax

Here is the syntax of ENUM data type:

ENUM ('v1', 'v2', ..., 'vn')

Here,

• ENUM is a keyword used to declare an enumeration type.
• v1 to vn is an optional list of this ENUM type, the column of ENUM type can only accept one of the above values.
• Enumeration values ​​can only be strings.

To define a ENUM column, use the following syntax:

CREATE TABLE table_name
(col_name ENUM ('v1','v2', ..., 'vn'));

In a ENUM data type, you can have multiple enumeration values. However, it is good practice to keep the number of enumeration values ​​below 20.

MySQL ENUM example

Let’s look at the example below.

Suppose, we have an order table to store e-commerce orders. Among them, the order status has only four statuses, as shown in the following table:

Then we’re going to use the ENUM type for the state column.

Difine MySQL ENUM column

Please use the following CREATE TABLE statement :

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    state ENUM('Unpaid', 'Paid', 'Shipped', 'Completed') NOT NULL
);

Now, we’ve created the table orders with an ENUM data type column state, and it will only accept one of four values: Unpaid, Paid, Shipped and Completed. At the same time, according to the order in which the columns were defined, the indexes of Unpaid, Paid, Shipped and Completed are 1, 2, 3 and 4 respectively.

Insert ENUM value

To insert data into a ENUM column , you must use an enumeration value from a predefined list. Otherwise, MySQL will give an error. For example, the following statement inserts a new row into the table orders.

INSERT INTO orders(title, state)
VALUES ('Apples', 'Paid');

you can use a numberic index for ENUM columns . E.g:

INSERT INTO orders(title, state)
VALUES ('Bananas', 2);

In this example, we used 2 instead of Paid. Since the index of Paid is 2, so 2 is acceptable.

Let’s go ahead and add more rows to the table orders:

INSERT INTO orders(title, state)
VALUES ('Pears', 'Shipped'),
    ('Peaches', 'Completed');

Because column state is a NOT NULL column, when you insert a new row with a null value for the state column, MySQL will use the first enum value as the default value. Please execute the following SQL statement:

INSERT INTO orders(title) VALUES('Oranges');

Here, we didn’t specify a value for the state column, MySQL inserted the first enum member Unpaid.

We can query all the rows in the table to verify all the operations just done.

SELECT * FROM orders;

+----+---------+-----------+
| id | title   | state     |
+----+---------+-----------+
|  1 | Apples  | Paid      |
|  2 | Bananas | Paid      |
|  3 | Pears   | Shipped   |
|  4 | Peaches | Completed |
|  5 | Oranges | Unpaid    |
+----+---------+-----------+

In non-strict SQL mode, if an invalid value is inserted into a ENUM column, MySQL will use an empty string '' with a numeric index 0. If strict SQL mode is enabled, attempting to insert an invalid ENUM value will result in an error.

Note that a ENUM column can accept NULL values ​​if it is defined as a nullable column.

Filter MySQL ENUM values

The following statement gets all orders with status paid:

SELECT * FROM orders WHERE state = 'Paid';

+----+---------+-------+
| id | title   | state |
+----+---------+-------+
|  1 | Apples  | Paid  |
|  2 | Bananas | Paid  |
+----+---------+-------+

Since the numeric index of Paid is 2, the following query returns the same result set:

SELECT * FROM orders WHERE state = 2;

Sort MySQL ENUM values

MySQL sorts the ENUM values order by the numeric index. Therefore, the order of enum members depends on how they are defined in the enum list.

Because the state column is defined as state ENUM('Unpaid', 'Paid', 'Shipped', 'Completed') NOT NULL, then when we sort the state column in ascending order, it Unpaid is the first, and Completed is the last.

Please execute the following statement and observe the output:

SELECT * FROM orders ORDER BY state;

+----+---------+-----------+
| id | title   | state     |
+----+---------+-----------+
|  5 | Oranges | Unpaid    |
|  1 | Apples  | Paid      |
|  2 | Bananas | Paid      |
|  3 | Pears   | Shipped   |
|  4 | Peaches | Completed |
+----+---------+-----------+

So, if you need to sort by an enum column, you should give an correct order in the definition of the column.

Disadvantages of MySQL ENUM

MySQL ENUM brings some benefits, such as readability and storage efficiency, but it also has the following disadvantages:

1. Altering enumeration members requires rebuilding the entire table using the ALTER TABLE statement, which is expensive in terms of resources and time.

2. Getting the full list of enumerations is complicated because you need to access the information_schema database:

   SELECT column_type
   FROM information_schema.COLUMNS
   WHERE TABLE_NAME = 'orders'
       AND COLUMN_NAME = 'state';
   

3. Because ENUM is not SQL standard, porting to other RDBMSs can be a problem.

4. Enumeration lists are not reusable. For example, the state enumeration values ​​in the above orders table cannot be reused on other table definition.

5. Enumeration values ​​are strings and cannot contain more information. For example, we need to add a timeout attribute on each order status.

Conclusion

In this tutorial, we introduced the MySQL ENUM data type and how to use it to define columns that store enumeration values.