The ENUM data type in MySQL is a helpful data type for specifying a valid range of values.
In this guide, you’ll learn all about the MySQL ENUM feature, including:
- what the ENUM feature is
- how to use it on your database
- some disadvantages of this feature
Let’s get into it.
What is the MySQL ENUM Data Type?
In MySQL, ENUM is a data type. It lets you specify a list of valid values when you set the data type (e.g. in a Create Table statement), and store these values in an efficient way.
Enum is short for enumeration, which is a term in software development for a valid list of values.
This description sounds a lot like a check constraint on a table, but there is a difference.
An ENUM data type actually defines how the data is stored. Values are stored as numbers but can be viewed as their corresponding text value. A check constraint just limits the values to those you specify.
Let’s take a look at how you create an ENUM data type.
How to Create an ENUM Data Type
To create an ENUM data type, you add a column with the ENUM data type to a table:
ENUM('value 1', 'value 2', ...)This is easily done using a Create Table statement.
Here’s an example of a customer order table that stores information about orders placed on an eCommerce website.
CREATE TABLE cust_order (
id INT,
order_date DATE,
status ENUM('Not Started', 'Processing', 'In Transit', 'Delivered')
);The status column is of type ENUM. It has four possible values:
- Not Started
- Processing
- In Transit
- Delivered
The values stored in this field are actually numbers, not text values. These numbers correspond to the values in the ENUM specification here.
This has the advantage of saving space: a single number is smaller to store than many text characters.
So, here’s how this example would translate
| Value stored in table | Value shown |
| 1 | Not Started |
| 2 | Processing |
| 3 | In Transit |
| 4 | Delivered |
We’ll see how we can work with these values shortly in this guide.
Insert a Value into an ENUM Column
To insert values into an ENUM column you can refer to either the text value itself or the numeric index.
For example, this statement inserts a new record using the text value of Not Started:
INSERT INTO cust_order (id, order_date, status)
VALUES (1, 20211009, 'Not Started');This next statement inserts an order using the index of 1, which would translate to a status of Not Started.
INSERT INTO cust_order (id, order_date, status)
VALUES (2, 20211009, 1);We can insert a different value into the table:
INSERT INTO cust_order (id, order_date, status)
VALUES (3, 20211009, 4);What if we insert a NULL value?
INSERT INTO cust_order (id, order_date, status)
VALUES (4, 20211011, NULL);Here’s what happens:
1 row inserted.
The result depends on if the column is allowed NULL values (i.e. it does not have a NOT NULL constraint):
- If NULLs are allowed (there is no NOT NULL constraint), the NULL value is valid and it is inserted.
- if NULLS are not allowed (there is a NOT NULL constraint), the value used is the one with an index of 1 (the first value).
What if we insert an invalid value (one that does not exist in the ENUM list)?
INSERT INTO cust_order (id, order_date, status)
VALUES (5, 20211012, 'Returned');Here’s what happens:
The result depends on if your MySQL server is in strict SQL more or non-strict SQL mode:
- Strict SQL Mode: an error is shown
- Non-Strict SQL mode: an empty string value ” and an index of 0 are used
Using ENUM in a WHERE Clause
You can work with ENUM values in a WHERE clause. You can filter them on either the ENUM value (the text value) or the index.
Here’s a query to find all orders that are Not Started:
SELECT id, order_date, status
FROM cust_order
WHERE status = 'Not Started';Results:
| id | order_date | status |
| 1 | 2021-10-09 | Not Started |
| 2 | 2021-10-09 | Not Started |
We can see that only the orders that are Not Started are shown.
The same query can be written using the index value of 1.
SELECT id, order_date, status
FROM cust_order
WHERE status = 1;Results:
| id | order_date | status |
| 1 | 2021-10-09 | Not Started |
| 2 | 2021-10-09 | Not Started |
Here’s a query that finds all orders that are delivered (index of 4)
SELECT id, order_date, status
FROM cust_order
WHERE status = 4;Results:
| id | order_date | status |
| 3 | 2021-10-09 | Delivered |
Ordering Results Based On ENUM Values
Queries can be ordered using the ENUM values. However, they are ordered by their index numbers and not by their ENUM values.
For example, this query shows orders sorted by their status.
SELECT id, order_date, status
FROM cust_order
ORDER BY status ASC;Results:
| id | order_date | status |
| 4 | 2021-10-11 | (null) |
| 1 | 2021-10-09 | Not Started |
| 2 | 2021-10-09 | Not Started |
| 3 | 2021-10-09 | Delivered |
We can see that the results are sorted by Status, in the order of Not Started, Processing, In Transit, and Delivered.
The status values are not in alphabetical order because the index values are used. The Not Started value is shown near the top because it has an index of 1, but the NULL value is shown first.
Difference Between ENUM and SET in MySQL
There’s another data type in MySQL called SET that looks pretty similar.
Here’s how you could declare a SET data type:
CREATE TABLE set_test (
test_attribute SET('black', 'grey', 'white')
);It looks the same when it is declared.
However, the difference is in the values it accepts.
SET is a collection of values. You can store any combination of the allowed values in the column:
- ‘black’
- ‘grey’
- ‘white’
- ‘black’, ‘white’
- ‘grey’, ‘white’, ‘black’
An ENUM is a single value. You can only store one of the allowed values.
Why ENUM in MySQL is a Bad Idea
If you’ve worked with ENUM in the past, or read about it online, you might be wondering if it’s a good or a bad feature to use.
It has a few benefits:
- smaller storage than the actual string values
- validation of data
However, a great article by Chris Komlenic highlights quite a few reasons why using ENUM in MySQL is actually a bad idea.
- The values in the ENUM are actually data but are stored in the table definition or metadata of the table, which is not the best place for it.
- Changing one of the ENUM values is hard, as you need an ALTER TABLE statement and is traditionally harder to do
- You can’t add extra attributes or information to describe it (archive data, sort order, etc)
- Getting a list of different ENUM values is hard. You’ll have to query the information_schema and parse the values
- The performance gains are small, if at all.
- You can’t use the ENUM values in other areas of the system
- They translate invalid data to empty strings
- Limited portability to other databases (but this is not something that’s done very often)
I would be hesitant to use ENUM at all in a database. I agree with Chris’ assessment of the issues with ENUM and suggestions for only using ENUM:
- When you’re storing unchanged values; and
- you never need to store additional info; and
- the list of values is more than 2 and less than 20
Alternative – A Lookup Table
So if ENUM is not an ideal solution, what’s the alternative?
A lookup table.
You can create a lookup table that stores a unique ID and the value you want to use.
You can then use a foreign key from your main table to the lookup table.
There are many advantages to this:
- Data can be easily updated (Insert, Update, or Delete statements)
- Extra information can be added (such as marking a record as archived, or descriptions)
- You can easily get a list of all available values
- The foreign key constraint ensures that valid values must be related
So, if you’re planning on using an ENUM data type in MySQL, consider using a lookup table instead.
Conclusion
The ENUM data type in MySQL is a handy data type for specifying the valid values for a column. It has some helpful sorting features and it allows you to filter and insert by string value or by the index number.
However, they have a few disadvantages, and lookup tables may be a better solution.