MySQL ENUM vs CHECK Constraint: Which Value List Changes Copy the Table

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: On MySQL 8.4, appending a value to the end of an ENUM of up to 255 values is a metadata change that takes no time, while inserting one in the middle, reordering, or removing one copies the whole table and holds back writes. Removing a value that rows still hold fails with error 1265. An enforced CHECK on a VARCHAR copies the table for every change to its list, appends included, and a lookup table needs no ALTER TABLE at all.

On MySQL 8.4, an ENUM is the cheapest value list to extend and among the most expensive to edit any other way. Appending a value to the end of the list changes only the table definition and finished in 0.00 seconds on a million rows. Inserting a value in the middle, reordering the list or removing a value copies the whole table and holds back writes while it does, and removing a value that rows still hold fails outright. A CHECK (status IN (...)) on a VARCHAR copies the table for every change to its list, appends included. So whether ENUM or CHECK is the better choice depends on how the list will change, not on how it looks today.

Everything below ran on MySQL 8.4.11 in a throwaway Docker container on a laptop. The table is tasks, with 1,000,000 rows and a status ENUM('open','done','blocked') NOT NULL DEFAULT 'open' column. 400,000 rows hold open, 599,964 hold done and 36 hold blocked. Each change ran on a fresh copy of the table, and the timings are there to compare kinds of change, not to predict your server.

MySQL ENUM vs CHECK constraint: which is cheaper to change?

The ENUM is cheaper only for an append within its storage width (and a change of letter case): every other ENUM edit, and every edit to an enforced CHECK list, copies the table.

MySQL stores an ENUM value as a number, the position of the value in the list, and keeps the strings in the table definition. Appending a string leaves every stored number pointing at the same value, so there is nothing to rewrite. Anything that moves a value to a different position, or takes one away, changes what the stored numbers mean, so MySQL rebuilds the table to write them again. The online DDL reference says it in one line: "Adding members in the middle of the list causes renumbering of existing members, which requires a table copy."

Neither MySQL nor PostgreSQL can alter a check constraint's expression, so changing the list means DROP CHECK and ADD CONSTRAINT ... CHECK, and adding a check makes MySQL read every row to validate it. MySQL does that by copying the table, even when the new list only adds a value and no row could fail it. The DROP CHECK alone is instant; it is the ADD that costs. PostgreSQL can split the cost with NOT VALID and a later VALIDATE CONSTRAINT; MySQL has no equivalent. Its NOT ENFORCED check is added instantly, but it enforces nothing, and switching it to ENFORCED copies the table.

A copy is not only slow, it holds back writes. On the same table grown to 3,000,000 rows, inserting 'review' in the middle of the list copied for 4.94 seconds. A single-row INSERT sent 0.7 seconds in waited 4.33 seconds, while a SELECT by primary key sent at the same moment came back at once. Appending 'archived' to the same 3,000,000 rows took 0.00 seconds.

Does adding a value to a MySQL ENUM copy the table?

Not if the value goes at the end and the list stays within the same storage width.

An ENUM takes 1 byte per row up to 255 values and 2 bytes from 256 up to its limit of 65,535; a SET takes 1, 2, 3, 4 or 8 bytes for its up to 64 members, per the storage requirements table. When an append crosses one of those widths, every row has to be rewritten at the new width, so the append copies the table like any other change. That is why the 9th SET member copied and the 10th did not, and why the 256th ENUM value copied. Few lists ever reach 255 values, but 8 SET members is an easy boundary to cross.

To make the cost visible before production, name the algorithm in the migration:

ALTER TABLE tasks MODIFY status ENUM('open', 'done', 'blocked', 'archived') NOT NULL DEFAULT 'open', ALGORITHM=INSTANT; -- Query OK, 0 rows affected (0.00 sec) ALTER TABLE tasks MODIFY status ENUM('open', 'review', 'done', 'blocked') NOT NULL DEFAULT 'open', ALGORITHM=INSTANT; -- ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. -- Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE. 

The second statement changes nothing and says why. Without the clause it would have copied the table, because MySQL picks the cheapest algorithm it can and does not name it; the only sign is 1000000 rows affected instead of 0 rows affected, after the copy has run. The same clause catches a migration generated from a model where someone inserted 'review' where it reads well, rather than at the end.

Order matters for a second reason. MySQL sorts an ENUM by position, not alphabetically, so ORDER BY status on this table returns open, done, blocked. Appending keeps that order stable; putting a new value anywhere but last in a sort means inserting it or reordering the list, and both copy the table.

How do I remove a value from a MySQL ENUM?

Move every row off the value first, then remove it, and expect the removal to copy the table.

With strict mode on, the MySQL 8.4 default, removing a value that any row holds fails before anything changes: ERROR 1265 (01000): Data truncated for column 'status' at row 2. Without strict mode, the same statement succeeds with one warning per row, 36 here, and those rows now hold the empty string, which the ENUM documentation calls the error value, with index 0. SELECT status + 0 shows it. Nothing tells the application that 36 tasks lost their status. So before the change:

SELECT COUNT(*) FROM tasks WHERE status = 'blocked'; -- 36 UPDATE tasks SET status = 'open' WHERE status = 'blocked'; ALTER TABLE tasks MODIFY status ENUM('open', 'done') NOT NULL DEFAULT 'open'; 

Renaming a value is the same three steps: append the new name (instant), move the rows with an UPDATE, remove the old name (a copy). On a large table the UPDATE is the expensive step, one transaction over every row that holds the value, so run it in batches and have the application accept both names while the rows move. Changing 'done' to 'completed' in place is refused INSTANT with 1846 and fails with 1265 on rows holding 'done'. There is no shorter route, with one exception: a change of letter case only. Under a case-insensitive collation such as the default utf8mb4_0900_ai_ci, relabelling 'done' as 'Done' was instant and every row read back as Done. Under utf8mb4_bin or utf8mb4_0900_as_cs the same statement failed with 1265, because there 'done' and 'Done' are different values. The default follows the same rule: DEFAULT 'Open' on a list holding 'open' is accepted under _ci and stored as 'open', and refused on a status ENUM('open','done') COLLATE utf8mb4_bin column with ERROR 1067 (42000): Invalid default value for 'status'.

If the copy is too expensive, the alternative is not to remove the value at all: leave it in the list, stop the application writing it, and remove it the next time the table is rebuilt for another reason.

Which should you use: ENUM, CHECK or a lookup table?

Use an ENUM for a list that only grows at the end, a lookup table for a list that changes often or carries data of its own, and a CHECK when portability matters more than the cost of changing it.

The removal row matters as much as the add row: every option refuses to orphan a row, but only the lookup table does it without an ALTER TABLE on the table that holds the rows. Its cost is a join to read the label and one more table in the diagram.

How Schemity shows what an ENUM change costs

Schemity's impact analysis reports which MySQL ENUM edits copy the table and how many rows hold a value you removed, before the migration runs.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. After you connect to MySQL, an ENUM column's values appear in the field dialog's Values box, and editing them there becomes a planned ALTER TABLE ... MODIFY statement.

Impact analysis applies the rules measured above:

• Append a value and there is no finding, because MySQL changes only the definition. Append the 256th ENUM value or the 9th SET member and the table rewrite appears.
• Insert, reorder or remove a value and the findings include "Rewrites tasks to change status" with the row estimate, and a note that changing the type of status holds back writes to tasks while it reads the same estimated rows.
• Remove a value and a "Can fail on existing data" finding appears ("Loses data" on a server without strict mode), with "Count exactly" to count the rows that still hold it. The count runs only when you ask, as a read-only SELECT COUNT(*) that matches the removed values, so 36 blocked rows show up before MySQL reports them as error 1265.
• A change of letter case is matched the way the column's collation compares: no finding under _ci, a failing removal under _bin or _cs.

Appending archived after open, done and blocked in the Values box is the first case, and the Impact drawer stays empty:

Remove blocked instead and the same drawer shows three findings: the change can fail on existing data, it rewrites tasks, and it holds back writes while it reads the estimated ~997.6K rows, 42 MB on disk.

Schema lint adds one rule for the same column type: mysql-enum-default-not-listed reports an ENUM or SET default that is not one of its values, which MySQL refuses with error 1067, compared under the column's collation. That is how the diagram lets you see the impact of every change to a value list: the append and the insert look alike in a migration file, and only one of them copies the table.

MySQL ENUM changes, in one list

Only an append within the same storage width, or a change of letter case under a _ci collation, is free; everything else rewrites the table or fails.

• Appending to an ENUM or SET is INSTANT, unless it crosses 255 ENUM values or a SET storage width (8, 16, 24 or 32 members).
• Inserting in the middle, reordering and removing a value copy the table and hold back writes until the copy ends.
• Removing a value that rows hold fails with 1265 in strict mode and silently empties those rows without it; move them first.
• Changing an enforced CHECK list copies the table every time, appends included; a lookup table changes with an INSERT.
• Add ALGORITHM=INSTANT to an append so MySQL refuses, rather than copies, if the change is not one, and set lock_wait_timeout so even an instant change cannot queue reads behind an open transaction.

The other MySQL ALTER TABLE changes that copy, from a VARCHAR crossing 255 bytes to a foreign key added with checks on, are timed in MySQL ALTER TABLE: which changes lock the table, and what happens to the statements before a failed one is covered in which failed migrations roll back. The same choice on PostgreSQL, where enums are a type of their own, is compared in Postgres enum vs check constraint vs lookup table, and if you are converting a MySQL schema to PostgreSQL, every ENUM needs that decision anyway.