17
What is the difference between ENUM and SET data types?
Level: Basic — Tests your knowledge of MySQL's special "list-based" string types.
Both ENUM and SET restrict a column to values from a predefined list, but they differ in how many values a single cell can hold.
- ENUM: Stores exactly one value from the allowed list (like a single-choice dropdown). Can hold up to 65,535 distinct members.
- SET: Stores zero, one, or many values from the allowed list (like multi-select checkboxes). Can hold up to 64 distinct members.
How they store data: Internally both store numeric indexes, not the strings themselves — which makes them compact and fast. ENUM stores one index; SET stores a bitmask combining multiple selections.
Side-by-Side Comparison
| Feature |
ENUM |
SET |
| Values per cell |
Only one |
Zero, one, or many |
| Max members |
65,535 |
64 |
| Internal storage |
Single index |
Bitmask |
| Analogy |
Radio button |
Checkboxes |
| Example use |
status, gender, size |
hobbies, permissions, tags |
Common Gotcha
Watch Out
- Inserting a value not in the list stores '' (empty) in non-strict mode.
- ENUM/SET are hard to modify later — adding options needs an ALTER.
Best Practice
- Use for small, stable lists that rarely change.
- For frequently changing options, use a lookup table instead.
Quick Example
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
-- ENUM: pick exactly one
gender ENUM('Male', 'Female', 'Other'),
-- SET: pick one or more
hobbies SET('Reading', 'Music', 'Sports', 'Coding')
);
-- ENUM takes a single value
INSERT INTO users (gender, hobbies)
VALUES ('Male', 'Reading,Coding'); -- SET holds multiple values
-- Query rows that include a particular SET member
SELECT * FROM users WHERE FIND_IN_SET('Coding', hobbies) > 0;
Interviewer tip: The one-liner they want — "ENUM allows exactly one value from a list (single-choice), while SET allows multiple values from a list (multi-choice). Both are stored internally as numbers."