MySQL ENUM data is a string data type with a value chosen from the list of permitted values. You set these permitted values during the table creation as shown below:
CREATE TABLE Product
(
id int NOT NULL PRIMARY KEY,
productName varchar(30) NOT NULL,
color enum('blue','red','yellow','black','white') NOT NULL DEFAULT 'blue'
);
Easy, isn’t it?
For starters, data validation is instant without another table and a foreign key. Under strict server mode, this means you can’t force a wrong entry. This is great!
Or is it?
Like any other thing in the world, it’s not always a happily ever after.
After reading the following 12 key facts about MySQL ENUM, you can decide if it is good for your next database or table in MySQL.
For this article, the MySQL version is 8.0.23, and the storage engine is InnoDB.
1. MySQL ENUM is a Key/String Value Pair Type
MySQL ENUM is a key/value pair. Values are strings, and keys are index numbers.
But where’s the index?
MySQL automatically assigns numbers as they appear on your list. So, whether it’s about a shortlist of colors, customer types, salutations, or payment methods, numbers will be assigned. That is a fixed list that will never expand. Think of 20 or fewer items and values that will never have further attributes. Otherwise, you need a table.
But how are these indexes numbered?
2. MySQL ENUM Index Starts with 1 But Can Be NULL or Zero
I’ll start with an example.
CREATE TABLE people
(
id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
lastname varchar(30) NOT NULL,
firstname varchar(30) NOT NULL,
middlename varchar(30) NOT NULL,
gender enum('Male','Female') NOT NULL DEFAULT 'Female',
country enum('United States', 'Canada', 'Brazil',
'United Kingdom','Poland','Ukraine', 'Lithuania',
'Japan','Philippines','Thailand', 'Australia','New Zealand')
DEFAULT 'United States',
modifieddate datetime NOT NULL DEFAULT NOW()
);
There are 2 MySQL ENUM values here: gender and country. Let me start with the gender column that contains 2 values: Male and Female. The index for Male is 1, and Female is 2. This means that key indexes start with 1.
From this simple example, you can identify the index for the country column. It has 12 values. It starts with the United States with an index of 1 and ends with New Zealand with an index of 12.
Note: this index doesn’t refer to the table indexes that we use for fast searches.
Besides these numbers from 1 to 65,535, ENUM columns can also be NULL or zero. In our example, the country column accepts NULL. So, aside from indexes 1 to 12, NULL is another possible index with a NULL value.
You can also have a 0 index. This happens in the following situations:
- The server mode for your MySQL is not strict.
- You insert a value that is not on the list of permitted values.
- Then, the insert will succeed, but the value is an empty string with an index of zero.
To avoid errors, always use a strict server mode.
3. MySQL ENUM Limits the Possible Values in a Column
Under strict mode, the country column in our example earlier will only accept 12 possible values. So, if you try to do this, the “Data truncated for column ‘country'” error will be thrown:
INSERT INTO people (lastname, firstname, middlename, gender, country)
VALUES ('Choi', 'Seungcheol', '','Male','South Korea');
The error message below occurred because South Korea is not on the enumerated list.
The error message is the same as in MySQL Workbench.
If the MySQL Server used here is case-sensitive, this will not be accepted either:
INSERT INTO people (lastname, firstname, middlename, gender, country)
VALUES ('Hemsworth', 'Chris', '', 'MALE', 'united states');
Why? We defined the gender as Male, not MALE. And the country is United States, not united states.
In the end, enumerated values in MySQL ENUM data type act like foreign key constraints but without another table.
Aside from this, there’s another benefit MySQL ENUM data provide.
4. Friendly Output Without the Use of JOIN
No need for JOINs, but the output is friendly. Let’s take the below ENUM in MySQL example to explain it:
SELECT * FROM people
WHERE country = 4;This query will retrieve people from the United Kingdom. By default, you see the strings you defined in the ENUM column. But internally, the numbered indexes are stored. Here’s the result:
Note: The data you see was generated using dbForge Studio for MySQL’s data generating tool. I generated 50,000 names using the tool.
Meanwhile, the same output can be achieved when using a separate table and a join.
SELECT
p.id
,p.lastname
,p.firstname
,p.middlename
,CASE WHEN p.gender = 'M' THEN 'Male' ELSE 'Female' END AS gender
,c.countryname AS country
,p.modifieddate
FROM people_no_enums p
LEFT JOIN country c ON p.country = c.id
WHERE p.country = 4;
So, should you use MySQL ENUM to avoid JOINs altogether? Definitely not! This is good for a small but fixed list. More data with an indefinite number of rows and more attributes requires a table. And to make a friendlier output like in Figure 2, you will also need a JOIN. Having a separate table is more flexible and requires nothing from the developer when data is live. This is not the case with Enumdatatype.
5. Filter MySQL Enumeration by Index or String Value
In point #4, you saw an example with a WHERE clause to filter with an ENUM column. It used the index to specify the country. So, this will work too:
SELECT * from people
WHERE country IN (1,3,5)
AND gender = 1;You can also use the string value, like the one below:
SELECT * FROM people
WHERE country='Philippines'
AND gender = 'Female';6. Sorting is by Index
Sorting can be a little tricky. ENUM values are stored according to their index numbers, not the value. Check out the code below and the output that follows in Figure 3.
SELECT DISTINCT
country AS CountryName
,country + 0 AS CountryId
FROM people
ORDER BY country;
If you want the sorting to be based on value, cast the column to a CHAR, like the one below.
SELECT DISTINCT
country AS CountryName
,country + 0 AS CountryId
FROM people
ORDER BY CAST(country AS char);
How about this?
SELECT DISTINCT
country AS CountryName
,country + 0 AS CountryId
FROM people
ORDER BY CountryName;From the looks of it, the value will be used for sorting. But that is not the case. The output will be the same as in Figure 3. ORDER BY with a CAST is the best way to sort by value.
7. MySQL ENUM Storage is Up to 2 Bytes Only
According to the official documentation, the MySQL ENUM default storage involves the index. The resulting table is more compact compared to storing the values. One (1) byte for enumerations with 1 to 255 possible values. Two (2) bytes for 256 to 65,535 possible values.
But there’s a secret I want to tell you.
Of course, when storage is concerned, the values will occupy more than the index. Since proper table design produces a smaller storage footprint, let’s create another table with a separate country table.
CREATE TABLE country
(
id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
countryname varchar(30) NOT NULL,
modifieddate datetime DEFAULT NOW()
);
CREATE TABLE people_no_enums
(
id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
lastname varchar(30) NOT NULL,
firstname varchar(30) NOT NULL,
middlename varchar(30) NOT NULL,
gender char(1) not NULL,
country tinyint DEFAULT 1,
modifieddate datetime NOT NULL DEFAULT NOW()
);Now, let’s insert the same data.
INSERT INTO country (id, countryname, modifieddate)
VALUES (1, 'United States', NOW()), (2, 'Canada', NOW()), (3, 'Brazil', NOW()),
(4, 'United Kingdom', NOW()), (5, 'Poland', NOW()), (6, 'Ukraine', NOW()),
(7, 'Lithuania', NOW()), (8, 'Japan', NOW()), (9, 'Philippines', NOW()),
(10, 'Thailand', NOW()), (11, 'Australia', NOW()),
(12, 'New Zealand', NOW());
INSERT INTO people_no_enums
SELECT
p.id
,p.lastname
,p.firstname
,p.middlename
,CASE WHEN p.gender = 1 THEN 'M' ELSE 'F' END AS gender
,c.id
,p.modifieddate
FROM people p
LEFT JOIN country c ON p.country = c.countryname;To do that, we use the INFORMATION_SCHEMA.TABLES table. See the code below:
SELECT
table_name,
ROUND(((data_length + index_length)), 2) AS "Size in Bytes"
FROM information_schema.TABLES
WHERE table_schema = "testenumsdb"
AND TABLE_NAME LIKE 'people%'
ORDER BY (data_length + index_length) DESC;
A normalized table without ENUM columns compared to a table with it requires the same size in bytes. Both have 50,000 records of the same names using the InnoDB storage engine. But of course, the new country table will occupy space too. You need to weigh the other advantages and disadvantages of using ENUM.
8. MySQL ENUM is for String Literals Only
MySQL ENUM accepts string literals only. So, the code below won’t work:
CREATE TABLE Product
(
id int NOT NULL PRIMARY KEY,
productName varchar(30),
color enum('red','orange',CONCAT('red','orange'))
);The CONCAT function inside the Enumdatatype is not allowed as well as other valid SQL expressions.
9. MySQL ENUM Can’t Be Reused
From this point, you’ll see the dark side of MySQL ENUM.
First, you can’t reuse it. You’ll have to duplicate the same color, size, and priority enumerations if you need them in another table. A design like the one in Figure 6 below is impossible with ENUMs.
To use ENUM in the 2 tables above, you need to duplicate the priorities list on the 2 tables.
10. Adding More Values Requires Altering the Table
In the gender list earlier, we tried to use 2 items only: Male and Female. What if your company decides to embrace the LGBTQ? You need to run an ALTER TABLE and add to the end of the enumeration Lesbian, Gay, Bisexual, Transgender, and Queer. Here’s the code:
ALTER TABLE people
MODIFY COLUMN gender
enum('Male','Female','Lesbian','Gay','Bisexual','Transgender','Queer')
NOT NULL DEFAULT 'Male';Running this on my laptop with 50,000 records only took less than a second. Larger and more complex tables will consume a bit more time. If the gender list is a table, all you need is to insert the 5 new values.
Renaming a value will also need ALTER TABLE. A separate table only requires an easy UPDATE statement.
11. You Can’t Easily List Down Possible Values
Do you populate dropdown lists or grouped radio buttons from a table? It’s easy when you have a country table. Do a SELECT id, countryname FROM country, and you’re ready to populate the dropdown list.
But how will you do this with MySQL ENUM?
First, get the column information from INFORMATION_SCHEMA.COLUMNS table, like this:
/* Get the possible values for country ENUM. */
SELECT
TABLE_NAME
,COLUMN_NAME
,COLUMN_TYPE
FROM information_schema.columns
WHERE TABLE_SCHEMA='testenumsdb'
AND TABLE_NAME = 'people'
AND COLUMN_NAME = 'country';
Then, you must parse that string and format it before you populate a dropdown list. Quite archaic, isn’t it?
But there’s one last thing.
12. MySQL ENUM is Non-Standard
ENUM is a MySQL extension to the ANSI SQL standard. Other RDBMS products do not support this. So, refer to the appropriate MySQL tutorial before starting your projects. If you need to port your MySQL database full of ENUMs to SQL Server, for example, you need to do a workaround. Workarounds will vary depending on how you design the target table in SQL Server.
Bottomline
You must weigh the pros and cons of using MySQL ENUM. Having a separate table with proper normalization applied is the most flexible in the uncertain future.
We welcome additional points. So, head on to the Comments section below and tell us about it. You can also share this on your favorite social media platforms.
