Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 75 ยท Data Types

ENUM and SET in Practice

See how MySQL ENUM and SET store values as numbers, how they sort, why ALTER TABLE is risky, and when a lookup table or CHECK constraint is the better choice.

ENUM and SET are MySQL's way of saying "this column may only hold values from this list". An order's status may be 'pending', 'paid' or 'shipped'; a user's notifications may be any combination of 'email', 'sms' and 'push'. The idea is attractive: the database enforces the list, the table definition documents it and the storage is tiny.

In practice, both types come with traps, and many experienced MySQL developers avoid them. This lesson explains how they really work, what bites, and two alternatives, a lookup table and a CHECK constraint. Live examples use CHECK and foreign keys on SQLite, because only a MySQL server has ENUM and SET themselves.

ENUM versus SET

ENUM Exactly one value from a list.
SET Any combination of listed values.

Both are stored as numbers behind a string face.

1 / 2

How ENUM works

SQL
CREATE TABLE orders (
  id     BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  status ENUM('pending', 'paid', 'shipped', 'cancelled') NOT NULL DEFAULT 'pending'
);

INSERT INTO orders (status) VALUES ('paid'), ('pending'), ('shipped');

An ENUM column stores a number, the position of the value in the list, starting at 1. With up to 255 members it uses 1 byte, and up to 65,535 members 2 bytes. When you read the column you see the string, and when you compare it to a string, MySQL translates. This design has some behaviors that surprise people:

  • Numbers are accepted as indexes. INSERT ... VALUES (2) stores 'paid', not the text '2'. And WHERE status = 2 compares by position. Mixing numbers and strings is a source of bugs. The trap is worst with lists that look like numbers, such as ENUM('10','20','30'): the string '20' is the second member, but the number 20 is out of range, and the number 2 selects '20'.
  • Invalid values. In strict mode (the default) an invalid value is an error. Without strict mode, MySQL stores the special error value '' (index 0), and your data silently contains something not on the list. See SQL Modes and Strictness.
  • Case and padding. Matching follows the column's collation, so with the default case-insensitive collation 'PAID' is accepted and stored as 'paid'. Trailing spaces are removed.
  • NULL is a separate thing, index NULL, allowed only if the column is nullable.

Sorting follows the list, not the alphabet

ORDER BY on an ENUM column sorts by index number, that is, by the order of the definition list:

SQL
SELECT status FROM orders ORDER BY status;
-- pending, paid, shipped   (the order they were declared)

If you want alphabetical order, you need ORDER BY CAST(status AS CHAR). That cast also makes the sort unable to use an index. The definition order can be an advantage when it matches a workflow (pending, paid, shipped), but it also means that the order of the list is part of your data semantics: reorder it, and sorting changes.

You can reproduce the definition-order sort in SQLite with a CASE expression, which is what MySQL does internally with the stored index. The stand-in below also shows the alphabetical alternative:

SQL Sorting by definition order versus alphabetically (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

The rows come out as pending, pending, paid, shipped, cancelled, the workflow order. Replace the ORDER BY with ORDER BY status to see alphabetical order, which is what a VARCHAR column would give. In MySQL you can also use ORDER BY FIELD(status, 'pending', 'paid', 'shipped', 'cancelled') on any string column to pick an order explicitly.

How SET works

SET stores any subset of up to 64 listed values as a bitmask: each member gets a bit (1, 2, 4, 8, ...), and the column stores the sum in 1 to 8 bytes.

SQL
CREATE TABLE preferences (
  user_id       BIGINT UNSIGNED PRIMARY KEY,
  notifications SET('email', 'sms', 'push') NOT NULL DEFAULT 'email'
);

INSERT INTO preferences VALUES (1, 'email,push'), (2, 'sms');

SELECT user_id FROM preferences WHERE FIND_IN_SET('push', notifications);
-- 1
SELECT user_id FROM preferences WHERE notifications & 4;   -- bit for 'push'
-- 1

SET is compact, but it breaks the first normal form: several values live in one column, so you cannot index "who wants push notifications" efficiently, cannot attach extra data to a choice (when did they opt in?) and cannot join to the list. Most designs are better served by a small child table with one row per choice.

The ALTER pitfalls

Changing the list is where ENUM hurts most. The cost depends on what you change:

  • Appending a new value at the end of the list is a metadata-only change in modern MySQL when the storage size does not change (for example from 20 to 21 members). It is fast and does not copy the table.
  • Adding a value in the middle, reordering, or crossing the 255-member boundary changes the stored numbers and rebuilds the table. On a large table that can mean a long operation.
  • Removing or renaming a value that rows still use is dangerous. The stored index no longer matches, rows may fail the conversion or be set to the error value '', depending on the mode. You must first update the rows.
  • ALTER statements repeat the whole list. ALTER TABLE orders MODIFY status ENUM('pending','paid','shipped','cancelled','refunded') NOT NULL needs every existing value spelled again. Forget one, and the data changes under it.
SQL
-- Safe append (fast)
ALTER TABLE orders
  MODIFY status ENUM('pending', 'paid', 'shipped', 'cancelled', 'refunded') NOT NULL DEFAULT 'pending',
  ALGORITHM = INSTANT;

Adding the clause ALGORITHM=INSTANT is a good habit: if MySQL cannot do it that way, it fails instead of silently copying a big table. We look at these modes in ALTER TABLE and Online DDL.

Beyond ALTER, there are everyday limits. An ENUM cannot carry extra attributes (a label for display, a sort order, an "is active" flag), and every application that needs the list of valid values has to run SHOW COLUMNS or parse the definition. Other databases, and ORMs, treat the list differently, which complicates moves between systems.

Alternative 1: a CHECK constraint

Since MySQL 8.0.16, CHECK constraints are enforced. A plain VARCHAR column with a CHECK gives you a readable value stored as text, standard SQL and a list you can change with an ordinary ALTER TABLE that drops and re-adds the constraint.

SQL
CREATE TABLE orders (
  id     BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  CONSTRAINT chk_orders_status CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);

The cost is that VARCHAR(20) stores more bytes than a 1-byte ENUM, and sorting is alphabetical. You can run the same logic live. The statement uses INSERT OR IGNORE, which makes SQLite skip rows that break a constraint. That mimics MySQL's INSERT IGNORE here, but a real MySQL server in strict mode would reject a bad row with an error unless you used IGNORE:

SQL A CHECK constraint doing the job of ENUM

Press Run (or Ctrl + Enter) to see the result here.

Only paid and shipped are stored. refunded is not on the list. PAID also fails, because SQLite's IN compares case-sensitively here. Under MySQL's default collation 'PAID' would match the list and pass. That is a collation difference you will meet again in Character Sets and Collations.

Alternative 2: a lookup table

The most flexible design is a table of allowed values and a foreign key:

SQL
CREATE TABLE order_statuses (
  code       VARCHAR(20) PRIMARY KEY,
  label      VARCHAR(60) NOT NULL,
  sort_order TINYINT UNSIGNED NOT NULL,
  is_active  BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE orders (
  id     BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  FOREIGN KEY (status) REFERENCES order_statuses (code)
);

New values become an INSERT, not an ALTER TABLE. Extra attributes live next to each value. Reports can join for labels and ordering. The price is a join (or a cached copy of the small table in your application), and foreign key enforcement requires InnoDB. For compact keys you can use a TINYINT id as the primary key instead of the code, as the Foreign Keys in MySQL lesson explains.

The live example needs PRAGMA foreign_keys = ON, which is how SQLite switches enforcement on (InnoDB enforces it always). Try adding INSERT INTO orders (status) VALUES ('refunded'); at the end: the foreign key rejects it, which is exactly what you want from a list of allowed values.

SQL A lookup table with a foreign key

Press Run (or Ctrl + Enter) to see the result here.

The query returns the four orders in workflow order with their friendly labels, the same ordering an ENUM gives, but the order lives in data you can change with an UPDATE.

Which one should you pick?

  • Lookup table: the default for values that business people might extend or rename, or that need labels, colors or flags.
  • CHECK constraint: a good fit for small, stable lists you want to keep as readable text, for example 'asc'/'desc'.
  • ENUM: acceptable for tiny, truly fixed lists (days of the week, a binary mode switch) where compact storage and definition-order sorting help, and you accept the ALTER rules. Always quote values as strings and never pass numbers.
  • SET: usually replace with a child table. A bitmask in a plain integer column is the other compact option.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How does MySQL store an ENUM value?

    An ENUM column stores a 1-based index in 1 or 2 bytes and shows the matching string when you read it.
  2. 2. In what order does ORDER BY sort an ENUM column?

    ORDER BY compares the stored index numbers, so the order follows the declaration. Use CAST(col AS CHAR) for alphabetical order.
  3. 3. Which ALTER TABLE change to an ENUM is the cheap one?

    Appending at the end keeps existing numbers the same, so MySQL can often change only the metadata. The other changes rewrite stored values or risk data loss.
  4. 4. Why is a lookup table often better than an ENUM?

    A lookup table keeps the list in data. You can add, rename or deactivate values and store labels or sort order, without ALTER TABLE.

Summary

  • ENUM stores a 1-based index (1 or 2 bytes) and SET stores a bitmask of up to 64 members. Both show strings but are numbers inside.
  • Sorting an ENUM follows the definition order. Numeric values are treated as indexes, which invites bugs.
  • Appending to the end of an ENUM list is cheap, while reordering, inserting in the middle or removing values can rebuild the table or lose data.
  • SET breaks first normal form, so a child table is usually better.
  • A CHECK constraint or a lookup table with a foreign key avoids most ENUM problems.

Next: Choosing the Right Data Types.