What is MySQL ENUM? (Top 12 Key Facts You Need to Know)

MySQL ENUM is a string data type with a value chosen from a list of permitted values. You set these permitted values during 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?

However, 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 this 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.

CodingSight - MySQL ENUM

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? The next point will explain.

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 ENUMs 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. Take, for example, the country column. It 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, a “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 is boxed in red below. It occurred because South Korea is not on the enumerated list.

MySQL ENUM Limits the Possible Values in a Column
Figure 1. Error is thrown after inserting invalid data in a MySQL ENUM column

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 act like foreign key constraints but without another table.

Aside from this, there’s another benefit MySQL ENUMs provide.

4. Friendly Output Without the Use of JOIN

That’s right. No need for JOINs, but the output is friendly. Here’s an 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:

Friendly Output Without the Use of JOIN
Figure 2. The friendly output of gender and country columns without a JOIN

NoteThe 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. But 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 ENUM.

5. Filter 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. It is based on the index, 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;
Sorting is by Index
Figure 3. ORDER BY with an ENUM column sorts the index, not the value

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);
Sorting is by Index
Figure 4. Result set after casting the ENUM column to 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 is Stored Up to 2 Bytes Only

According to the official documentation, the storage of MySQL ENUMs 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;
MySQL ENUM is Stored Up to 2 Bytes Only
Figure 5. Storage size comparison between a table with ENUM columns and a table without it

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 ENUM 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.

MySQL ENUM Can’t Be Reused
Figure 6. The same Priorities list is used by 2 tables. This is impossible with MySQL ENUM

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 that’s it. 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';
You Can’t Easily List Down Possible Values
Figure 7. List of possible values from Country ENUM

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, 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.

{coding}Sight

A community platform for IT specialists

Leave a Reply

Your email address will not be published. Required fields are marked *