Related Articles
Introduction
This article continues my series on MySQL data types. Previously, I covered numeric types and date/time types. This article focuses on SET and ENUM types, specialized data types that can be used for limited lists of data and make schemas more understandable, but can also cause problems if not used carefully. I’ll first look at what they are, then give examples of common mistakes and how to avoid them.
What Are ENUM and SET Types?
ENUM
- A string object that can hold a single value chosen from a predefined list.
- Example use case: order statuses (
'pending','paid','shipped','cancelled'). - Internally stored as an integer index (1-based).
- Supports up to 65,535 elements (like
SMALLINT).
SET
- A string object that can hold any combination of values from a predefined list.
- Example use case: user permissions (
'read','write','delete'). - Internally stored as a bitmap (each bit represents whether a value is present).
- Supports up to 64 elements (like
BIT(64)).
Syntax and Examples
Here is an example table for orders with status field that used ENUM type to specify available order statuses:
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
status ENUM(‘pending’,’paid’,’shipped’,’cancelled’) NOT NULL DEFAULT ‘pending’
);
To insert two orders to the table:
INSERT INTO orders () VALUES ();
INSERT INTO orders (status) VALUES (‘paid’);
Selecting all rows with an additional numeric representation of the status field:
SELECT id, status, status+0 FROM orders;
+—-+———+———-+
| id | status | status+0 |
+—-+———+———-+
| 1 | pending | 1 |
| 2 | paid | 2 |
+—-+———+———-+
2 rows in set (0.000 sec)
Here is an example table with SET field:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
permissions SET(‘read’,’write’,’delete’) NOT NULL
);
Inserting a user with multiple permissions:
INSERT INTO users (permissions) VALUES (‘read,write’);
Common Pitfalls And How To Avoid Them
Of course, I can begin by noting that if you are using very old MySQL versions or settings that are not sufficiently strict, you may overlook errors related to writing values that are not included in the field definition. In this case, an empty value will be written into the field. But I hope you can use fairly recent MySQL versions. I also hope that you understand these field types range limitation and do not try to store more than 64 values in sets.
So, the most common problem is related to changes in field definitions. Imagine someone adding a new value to the middle of the ENUM variants list:
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
status ENUM(‘pending’,’paid’,’shipped’) NOT NULL DEFAULT ‘pending’
);
INSERT INTO orders (status) VALUES (‘paid’);
SELECT id, status, status+0 FROM orders;
+—-+———+———-+
| id | status | status+0 |
+—-+———+———-+
| 1 | paid | 2 |
+—-+———+———-+
1 rows in set (0.000 sec)
ALTER TABLE orders MODIFY status ENUM(‘pending’,’accepted’,’paid’,’shipped’) NOT NULL DEFAULT ‘pending’;
As MySQL stores ENUM fields as integers internally, here is what we will have:
SELECT id, status, status+0 FROM orders;
+—-+———-+———-+
| id | status | status+0 |
+—-+———-+———-+
| 1 | accepted | 2 |
+—-+———-+———-+
1 rows in set (0.000 sec)
OOOPS! You need to remember that reordering or inserting a new variant anywhere but the end changes the index mapping, leading to data mismatches.
The second thing I want to mention is that while MySQL stores enums and sets as integers, it sorts and compares them as collated strings when selecting. This can surprise developers expecting numeric order.
Conclusion
ENUM and SET can be powerful tools for constraining values directly in your schema. But with their convenience come caveats around ordering, defaults, and silent coercion. By understanding their behavior and following best practices, you can avoid many of the common pitfalls and keep your data consistent.
Stay tuned for more articles in this series exploring other MySQL data types and how to use them effectively.
