MySQL ENUM and SET: How They Work and When to Avoid Them

An ENUM column accepts one value from a fixed list that you declare. A SET column accepts zero or more values from a list. Both are MySQL-specific.

query.sql

SQL

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending', 'paid', 'shipped', 'cancelled') NOT NULL DEFAULT 'pending'
);
INSERT INTO orders (status) VALUES ('paid'), ('pending'), ('shipped');
SELECT id, status, status + 0 AS stored_number FROM orders ORDER BY id;

Output

OUTPUT

+----+---------+---------------+
| id | status  | stored_number |
+----+---------+---------------+
|  1 | paid    |             2 |
|  2 | pending |             1 |
|  3 | shipped |             3 |
+----+---------+---------------+
3 rows in set

Internally MySQL stores a number: 1 for the first value, 2 for the second, and so on. That is why ENUM takes 1 or 2 bytes, much less than text.

Invalid values are rejected

query.sql

SQL

INSERT INTO orders (status) VALUES ('refunded');

Output

OUTPUT

ERROR 1265 (01000): Data truncated for column 'status' at row 1

The sorting surprise

An ENUM sorts by its number, which is the declaration order, not alphabetically:

query.sql

SQL

SELECT status FROM orders ORDER BY status;

Output

OUTPUT

+---------+
| status  |
+---------+
| pending |
| paid    |
| shipped |
+---------+
3 rows in set

pending (1) came before paid (2) and shipped (3). To sort alphabetically, use ORDER BY CAST(status AS CHAR).

SET: several values at once

query.sql

SQL

CREATE TABLE profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    interests SET('reading', 'cycling', 'chess', 'music') NOT NULL DEFAULT ''
);
INSERT INTO profiles (interests) VALUES ('reading,chess'), ('music'), ('');
SELECT id, interests FROM profiles WHERE FIND_IN_SET('chess', interests) > 0;

Output

OUTPUT

+----+---------------+
| id | interests     |
+----+---------------+
|  1 | reading,chess |
+----+---------------+
1 row in set

The trade-offs

Good

Small and fast.
The list is validated by the database.
Readable in queries ('paid', not 2).

Bad

Changing the list needs ALTER TABLE. Adding a value at the end is quick (instant or in place). Inserting in the middle, renaming, removing or reordering rebuilds the table, and it can change the stored numbers.
The sort order surprise above.
Business data changes. A “status” or “category” list usually grows. Each change becomes a schema migration and a deployment.
Using numbers as values (ENUM('1','2')) causes confusing bugs, because status = 1 compares with the index, not the text.
Other databases handle enums differently, so moving data is harder.

Better alternatives

Need	Better choice
A short list that almost never changes (`'draft'`, `'published'`)	`ENUM` is fine, or `VARCHAR` with a `CHECK` constraint
A list that business users or admins edit	A lookup table with a foreign key (`status_id`)
A list of labels attached to many items (tags)	A many-to-many table (Many-to-Many)
Many independent flags	Separate boolean columns, or a relation table

query.sql

SQL

CREATE TABLE order_statuses (
    id TINYINT UNSIGNED PRIMARY KEY,
    code VARCHAR(20) NOT NULL UNIQUE,
    label VARCHAR(50) NOT NULL
);
INSERT INTO order_statuses VALUES (1, 'pending', 'Pending payment'), (2, 'paid', 'Paid'), (3, 'shipped', 'Shipped');

CREATE TABLE orders2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status_id TINYINT UNSIGNED NOT NULL,
    FOREIGN KEY (status_id) REFERENCES order_statuses (id)
);
INSERT INTO orders2 (status_id) VALUES (2);
SELECT o.id, s.code, s.label FROM orders2 AS o JOIN order_statuses AS s ON s.id = o.status_id;

Output

OUTPUT

+----+------+-------+
| id | code | label |
+----+------+-------+
|  1 | paid | Paid  |
+----+------+-------+
1 row in set

A new status is now an INSERT, not an ALTER TABLE. You can add labels, translations and sort orders to the lookup table.

lightbulb

My rule

Use ENUM for a list that is small, stable and technical (a type that only your code sets). Use a lookup table for anything a human may want to add to.