MySQL Data Type Dive: ENUM – With examples.

ENUM data types are an interesting lot to me. I have previously blogged – in a high-level sense – on incorporating them with a TRIGGER for a sort of check constraint, which MySQL does not have. But what exactly is an ENUM? What are its advantages and disadvantages over other string data types? When would you use it and why? Join me and we will learn together…


Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
OS and DB used:

  • Xubuntu Linux 16.04.5 LTS (Xenial Xerus)
  • MySQL 5.7.23

High-Level Overview

Suppose I have this simple table representing books shared in a book club for 3 specific genres: ‘Western’, ‘Fantasy’, and ‘Military’.
(Note: This example would be stored better with a normalized schema but I am only interested in covering the ENUM data type so normalization practices are not covered in this blog post.)

In truth, any of the other MySQL string data types (e.g., VARCHAR and TEXT) could have been used for this example but herein lies a potential use case for the ENUM.

When you need a string data type column to only store allowable values from a preferred list, this is one area where an ENUM is a strong candidate.
The official documentation from section 11.4.4 The ENUM Type, mentions that there are storage benefits from using ENUM‘s. Exact storage size information is located in section 11.8 Data Type Storage Requirements. I’ll leave you to refer to that resource for additional specific details if interested.

For this oversimplified example, we are only accepting book genre types from the 3 values listed as shown for the genre column in the book_club table definition.

1
2
3
4
5
6
7
8
9
mysql> DESC book_club;
+-------+--------------------------------------+------+-----+---------+-------+
| Field | Type                                 | Null | Key | Default | Extra |
+-------+--------------------------------------+------+-----+---------+-------+
| id    | int(11)                              | YES  |     | NULL    |       |
| title | text                                 | YES  |     | NULL    |       |
| genre | enum('Western','Fantasy','Military') | YES  |     | NULL    |       |
+-------+--------------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Below I INSERT some mock data to work with so we can begin to explore and understand ENUM‘s:

1
2
3
4
5
mysql> INSERT INTO book_club(id, title, genre)
    -> VALUES (101, 'Dry Trough', 'Western'), (533, 'Dagger of Darkness', 'Fantasy'),
    -> (121, 'Magic Staff', 'Fantasy'), (282, '50 Caliber', 'Military');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

And here is the present data:

1
2
3
4
5
6
7
8
9
10
mysql> SELECT * FROM book_club;
+------+--------------------+----------+
| id   | title              | genre    |
+------+--------------------+----------+
|  101 | Dry Trough         | Western  |
|  533 | Dagger of Darkness | Fantasy  |
|  121 | Magic Staff        | Fantasy  |
|  282 | 50 Caliber         | Military |
+------+--------------------+----------+
4 rows in set (0.00 sec)

You want to know what books are stored for the ‘Fantasy’ genre. Of course, we can determine that easily with the WHERE clause:

1
2
3
4
5
6
7
8
9
10
mysql> SELECT *
    -> FROM book_club
    -> WHERE genre = 'Fantasy';
+------+--------------------+---------+
| id   | title              | genre   |
+------+--------------------+---------+
|  533 | Dagger of Darkness | Fantasy |
|  121 | Magic Staff        | Fantasy |
+------+--------------------+---------+
2 rows in set (0.00 sec)

Yet, since the genre column is an ENUM, we can also use its numbered index position in the WHERE clause:

1
2
3
4
5
6
7
8
9
10
mysql> SELECT *
    -> FROM book_club
    -> WHERE genre = 2;
+------+--------------------+---------+
| id   | title              | genre   |
+------+--------------------+---------+
|  533 | Dagger of Darkness | Fantasy |
|  121 | Magic Staff        | Fantasy |
+------+--------------------+---------+
2 rows in set (0.00 sec)

Confused? Let me explain.
Each element of the ENUM column is assigned an index starting at 1.

To know the index position for each ENUM value, you can issue a query like this:

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT genre, genre + 0 AS index_position
    -> FROM book_club;
+----------+----------------+
| genre    | index_position |
+----------+----------------+
| Western  |              1 |
| Fantasy  |              2 |
| Fantasy  |              2 |
| Military |              3 |
+----------+----------------+
4 rows in set (0.00 sec)

Values in an ENUM column are case-sensitive to that of their state at the time of table creation, for query results.
Here is a simple example (Note the mixed-case spelling):

1
2
3
mysql> CREATE TABLE food_item(id INTEGER,
    -> item ENUM('SPAGHETTI', 'HaMbUrGeR', 'poultry'));
Query OK, 0 rows affected (0.27 sec)

With a similar query as above, denoting the internal index position of the ENUM column, here is the returned case:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT item, item + 0 AS index_position
    -> FROM food_item;
+-----------+----------------+
| item      | index_position |
+-----------+----------------+
| SPAGHETTI |              1 |
| SPAGHETTI |              1 |
| HaMbUrGeR |              2 |
| poultry   |              3 |
| poultry   |              3 |
+-----------+----------------+
5 rows in set (0.01 sec)

However, case sensitivity is not an issue during INSERT‘s.(Note opposite case spelling for values SPAGHETTI and poultry):

1
2
3
4
mysql> INSERT INTO food_item(id, item)
    -> VALUES(88, 'SpAgHeTtI'), (181, 'POULTRY');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

As reflected in the query results below, returned item column values are in the same case as they were during table creation, regardless of the mixed case inserted above:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT item, item + 0 AS index_position
    -> FROM food_item;
+-----------+----------------+
| item      | index_position |
+-----------+----------------+
| SPAGHETTI |              1 |
| SPAGHETTI |              1 |
| HaMbUrGeR |              2 |
| poultry   |              3 |
| poultry   |              3 |
| SPAGHETTI |              1 |
| poultry   |              3 |
+-----------+----------------+
7 rows in set (0.00 sec)

What about inserting values not in the ENUM list?

Returning to the example book_club table, let’s INSERT a row that does not have an acceptable value for the genre column:

1
2
3
mysql> INSERT INTO book_club(id, title, genre)
    -> VALUES (764, 'Broken Diamond', 'Romance');
ERROR 1265 (01000): Data truncated for column 'genre' at row 1

This error occurs because strict SQL mode is currently enabled. As I will cover in a forthcoming blog post, you can utilize certain characteristics of the ENUM with a TRIGGER to check – and handle – these types of INSERT‘s. Therefore, I won’t go too deep into that as of now.

However, since I am a nice guy, I will mention that the index value for NULL is NULL and the empty string’s (e.g., ' ') index value is 0 (zero).

Useful Tidbit

For a quick and handy way to determine the ENUM column values established during table creation, you can use the SHOW command like below:

1
2
3
4
5
6
7
mysql> SHOW COLUMNS FROM book_club LIKE 'genre';
+-------+--------------------------------------+------+-----+---------+-------+
| Field | Type                                 | Null | Key | Default | Extra |
+-------+--------------------------------------+------+-----+---------+-------+
| genre | enum('Western','Fantasy','Military') | YES  |     | NULL    |       |
+-------+--------------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

Ordering Nuances

There are nuances when using an ORDER BY clause on ENUM columns, which can be better understood with a generic example.
We have a ‘fruit’ table composed of an ENUM column and an INTEGER id containing the below data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> DESC fruit_en;
+-------+----------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type                                                                             | Null | Key | Default | Extra |
+-------+----------------------------------------------------------------------------------+------+-----+---------+-------+
| id    | int(11)                                                                          | YES  |     | NULL    |       |
| fruit | enum('Pear','Cherry','Orange','Banana','Pineapple','Grape','Kiwi','Apple','Fig') | YES  |     | NULL    |       |
+-------+----------------------------------------------------------------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM fruit_en;
+------+-----------+
| id   | fruit     |
+------+-----------+
|    2 | Cherry    |
|    3 | Orange    |
|    4 | Fig       |
|    5 | Apple     |
|    6 | Kiwi      |
|    7 | Pineapple |
|    8 | Grape     |
|    9 | Cherry    |
|   10 | Pear      |
|   13 | Banana    |
+------+-----------+
10 rows in set (0.00 sec)

In similar fashion, we have another ‘fruit’ table but this one uses a VARCHAR data type to store the fruit name:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> DESC fruit_vc;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| fruit_t | varchar(12) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM fruit_vc;
+------+-----------+
| id   | fruit_t   |
+------+-----------+
|    2 | Cherry    |
|    3 | Orange    |
|    4 | Fig       |
|    5 | Apple     |
|    6 | Kiwi      |
|    7 | Pineapple |
|    8 | Grape     |
|    9 | Cherry    |
|   10 | Pear      |
|   13 | Banana    |
+------+-----------+
10 rows in set (0.00 sec)

So how does ORDER BY operate on ENUM columns?
First, let’s see with the VARCHAR column:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> SELECT *
    -> FROM fruit_vc
    -> ORDER BY fruit_t ASC;
+------+-----------+
| id   | fruit_t   |
+------+-----------+
|    5 | Apple     |
|   13 | Banana    |
|    2 | Cherry    |
|    9 | Cherry    |
|    4 | Fig       |
|    8 | Grape     |
|    6 | Kiwi      |
|    3 | Orange    |
|   10 | Pear      |
|    7 | Pineapple |
+------+-----------+
10 rows in set (0.00 sec)

As you would expect, sorting is performed in alphabetical order for the VARCHAR column with a query in ascending (ASC) order.

Yet, with the ENUM you get somewhat different results:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> SELECT *
    -> FROM fruit_en
    -> ORDER BY fruit ASC;
+------+-----------+
| id   | fruit     |
+------+-----------+
|   10 | Pear      |
|    2 | Cherry    |
|    9 | Cherry    |
|    3 | Orange    |
|   13 | Banana    |
|    7 | Pineapple |
|    8 | Grape     |
|    6 | Kiwi      |
|    5 | Apple     |
|    4 | Fig       |
+------+-----------+
10 rows in set (0.00 sec)

Not quite the ordering you would expect.
It does look familiar does it not?
Let’s review a snippet from the table definition:

1
| fruit | enum('Pear','Cherry','Orange','Banana','Pineapple','Grape','Kiwi','Apple','Fig')

Yes, you are correct. ENUM‘s are sorted by their acceptable values in that order they were implemented during table creation. The same applies to ORDER BY DESC queries (not shown).

Pros, Cons, Tips, and Reminders

  • ENUM values must be a quoted string literal.
  • The ENUM element’s index has no relation or bearing on a table INDEX. They are not one and the same.
  • ENUM column values are case sensitive for returned query results, identical to their state at the time of table creation.
  • ENUM columns are sorted by their order of implementation established during table creation.
  • ENUM data types use less internal storage than those of other character types.
  • You must use ALTER TABLE to change an established ENUM data type value which may be expensive in terms of locking and resources. (I learned this and other valuable information in the blog post, A Comprehensive Guide to Using MySQL ENUM
  • MySQL’s ENUM is not the same as other vendor’s versions so compatibility may be an issue if moving to another RDBMS.
  • You can add another new element to an existing ENUM column with the ALTER TABLE command but you cannot change an existing individual element value.
  • As with anything, just because it is there does not necessarily mean you have to use it. Some use cases may not benefit from or justify an ENUM.

This dive into the ENUM data type has been nothing short of stellar for me with all I have discovered, learned, and shared here. But, there are still many things to cover that I have left out of this blog post.

Why? In a follow-up blog post, I will use a TRIGGER to incorporate some validation checking for ENUM columns, thus covering those areas skipped here. Be sure and visit that post when it drops!

Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!

Explore the official MySQL 5.7 Online Manual for more information.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.