ENUM Data Type
In this page:
What is ENUM?
ENUM restricts a column to one value chosen from a fixed list you define at table-creation time, such as small, medium, large — it's a lightweight alternative to a full lookup table for a small, stable set of options.
Example: What is ENUM?
CREATE TABLE shirts (size ENUM('small', 'medium', 'large'));
INSERT INTO shirts VALUES ('medium');Querying ENUM Fields
Even though you insert and query ENUM values as ordinary strings, MySQL actually stores them internally as compact integer indexes, which keeps the column small and fast to compare.
Example: Querying ENUM Fields
CREATE TABLE shirts (size ENUM('small', 'medium', 'large'));
INSERT INTO shirts VALUES ('medium');
SELECT * FROM shirts WHERE size = 'medium';ENUM Numeric Indexing
Each defined ENUM value gets an implicit index starting at 1 (with 0 reserved for invalid/empty entries), and you can technically insert or filter by that numeric index instead of the string.
Example: ENUM Numeric Indexing
CREATE TABLE shirts (size ENUM('small', 'medium', 'large'));
INSERT INTO shirts VALUES ('medium');
SELECT * FROM shirts WHERE size + 0 = 2;Handling Invalid ENUM Values
In strict SQL mode, inserting a value not present in the ENUM's defined list raises an error and rejects the row; outside strict mode, MySQL may instead silently store an empty string.
Example: Handling Invalid ENUM Values
CREATE TABLE shirts (size ENUM('small', 'medium', 'large'));
INSERT INTO shirts VALUES ('extra-large'); -- rejected in strict SQL modeModifying ENUM Options
Adding a new allowed value to an ENUM requires an ALTER TABLE that redefines the full column, which can be a slow, table-rewriting operation on very large tables — a real tradeoff against ENUM's compactness.
Example: Modifying ENUM Options
ALTER TABLE shirts MODIFY size ENUM('small', 'medium', 'large', 'x-large');Chapter Quiz — Complete all 8 topics to unlock
0/8 topics done
Complete these topics first: