ENUM & SET Types — MySQL

MySQL ENUM and SET Types

ENUM and SET are MySQL's native types for columns with a predefined set of allowed values. They store string labels but represent them internally as integers, giving you human-readable data with compact storage. Understanding their internals helps you use them correctly — and know when to avoid them entirely.

The ENUM Type

ENUM defines a column that can hold exactly one value from a fixed list of strings. Think of it as a dropdown with a fixed set of options.

SQL

CREATE TABLE orders (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer  VARCHAR(100) NOT NULL,
  status    ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
            NOT NULL DEFAULT 'pending',
  priority  ENUM('low', 'normal', 'high', 'urgent') NOT NULL DEFAULT 'normal'
);

-- Insert with enum values
INSERT INTO orders (customer, status, priority) VALUES
  ('Alice', 'pending', 'normal'),
  ('Bob',   'shipped', 'high');

-- Filter by ENUM value
SELECT * FROM orders WHERE status = 'shipped';

-- ENUM is case-insensitive by default (like VARCHAR with ci collation)
SELECT * FROM orders WHERE status = 'SHIPPED';  -- works

-- Show the column definition
SHOW COLUMNS FROM orders LIKE 'status';

+--------+------------------------------------------------------+------+-----+---------+
| Field  | Type                                                 | Null | Key | Default |
+--------+------------------------------------------------------+------+-----+---------+
| status | enum('pending','processing','shipped','delivered',   | NO   |     | pending |
|        | 'cancelled')                                         |      |     |         |
+--------+------------------------------------------------------+------+-----+---------+

ENUM Storage Internals

MySQL stores ENUM values as integers, not strings. The first value in the list is 1, the second is 2, and so on. An ENUM with up to 255 members uses 1 byte; up to 65,535 members uses 2 bytes.

This means:

ENUM is very compact — a status with 5 options uses 1 byte vs. 10+ bytes for VARCHAR
ENUM values sort by their integer index, not alphabetically
Querying by number works: <code>WHERE status = 2</code> matches the second value
ENUM values are validated — inserting an unlisted value raises an error (in strict mode) or inserts an empty string

SQL

-- Demonstrate ENUM numeric index
SELECT status + 0 AS numeric_index, status
FROM orders;
-- 'pending' = 1, 'processing' = 2, 'shipped' = 3, etc.

-- ENUM sorts by index, not alphabetically
SELECT DISTINCT status FROM orders ORDER BY status;
-- Returns: pending, processing, shipped, delivered, cancelled
-- NOT alphabetical order!

-- To sort alphabetically, cast to CHAR:
SELECT DISTINCT status FROM orders ORDER BY CAST(status AS CHAR);
-- Returns: cancelled, delivered, pending, processing, shipped

Note

ENUM sorting by internal index (not alphabetically) is a common source of confusion. If you need alphabetical sorting, either sort explicitly with CAST, or use a lookup table approach where you can define sort order explicitly.

Inserting Invalid ENUM Values

SQL

-- In strict SQL mode (recommended), invalid values raise an error:
INSERT INTO orders (customer, status) VALUES ('Carol', 'returned');
-- Error 1265: Data truncated for column 'status' at row 1

-- Without strict mode, MySQL inserts an empty string:
-- status would be '' (the zero-value for ENUM)

-- Check for corrupted empty-string enum values
SELECT * FROM orders WHERE status = '';

Altering ENUM Values

Adding a value to the END of an ENUM list is a fast, metadata-only operation in MySQL 8.0. Modifying or removing values, or adding values in the middle of the list, requires a full table rebuild (which locks the table).

SQL

-- FAST: adding a value to the end (metadata-only in MySQL 8.0)
ALTER TABLE orders
  MODIFY COLUMN status
  ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded')
  NOT NULL DEFAULT 'pending';

-- SLOW (full table copy): inserting a value in the middle
ALTER TABLE orders
  MODIFY COLUMN status
  ENUM('pending', 'processing', 'packed', 'shipped', 'delivered', 'cancelled')
  NOT NULL DEFAULT 'pending';

-- SLOW: removing a value (requires updating all rows that had that value first)
-- Step 1: update all rows with the old value
UPDATE orders SET status = 'cancelled' WHERE status = 'refunded';
-- Step 2: remove from the ENUM definition
ALTER TABLE orders
  MODIFY COLUMN status
  ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
  NOT NULL DEFAULT 'pending';

Warning

Never remove an ENUM value while any rows in the table still hold that value. Those rows get the empty string value, which can break application logic and queries silently. Always migrate data before removing ENUM members.

The SET Type

SET is similar to ENUM but allows a column to hold zero or more values from a fixed list simultaneously — like multiple checkboxes. Each selected value is stored as a bit in an integer, allowing up to 64 members.

SQL

CREATE TABLE user_notifications (
  user_id   INT UNSIGNED PRIMARY KEY,
  notify_on SET('email', 'sms', 'push', 'slack', 'webhook')
            NOT NULL DEFAULT 'email'
);

-- A user who wants email and push notifications
INSERT INTO user_notifications VALUES (1, 'email,push');
INSERT INTO user_notifications VALUES (2, 'email,sms,slack');
INSERT INTO user_notifications VALUES (3, '');  -- no notifications

-- Find users who have email enabled (contains 'email' in the set)
SELECT * FROM user_notifications WHERE FIND_IN_SET('email', notify_on);

-- Alternative using bitwise AND
SELECT * FROM user_notifications
WHERE notify_on & (1 << 0);  -- bit 0 = 'email'

-- Update a user's notification preferences
UPDATE user_notifications
SET notify_on = 'email,sms'
WHERE user_id = 1;

SET Storage Internals

SET stores each selected value as a bit in an integer:

Members	Storage
1-8	1 byte
9-16	2 bytes
17-24	3 bytes
25-32	4 bytes
33-64	8 bytes

SQL

-- See the numeric representation
SELECT notify_on, notify_on + 0 AS numeric_value
FROM user_notifications;

-- notify_on = 'email,push' = bits 0 and 2 set = 1 + 4 = 5

ENUM vs SET vs Lookup Table

The decision between ENUM, SET, and a separate lookup table depends on whether the allowed values will change and how frequently:

Approach	Pros	Cons	Use when
ENUM	Compact, validated, self-documenting schema	Schema change to add/modify values, no metadata	Truly stable values (order status, gender, direction)
SET	Compact multi-value storage, validated	Schema change to modify, hard to query	Small fixed set of flags/options (notification types)
Lookup table + INT FK	Add values with INSERT, rich metadata (sort order, label), standard SQL	Join required for display, slightly more complex queries	Values that grow or change, need labels/descriptions
CHECK constraint	Standard SQL, no special type needed	Just a constraint, no metadata or special storage	Simple validation on VARCHAR/INT columns

The Lookup Table Pattern

SQL

-- Alternative to ENUM using a lookup table
CREATE TABLE order_statuses (
  id          TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code        VARCHAR(20) NOT NULL UNIQUE,
  label       VARCHAR(50) NOT NULL,
  sort_order  TINYINT UNSIGNED NOT NULL,
  is_terminal BOOLEAN NOT NULL DEFAULT FALSE,  -- final states
  color       CHAR(7)  -- hex color for UI display
);

INSERT INTO order_statuses (code, label, sort_order, is_terminal, color) VALUES
  ('pending',     'Pending',     1, FALSE, '#FFA500'),
  ('processing',  'Processing',  2, FALSE, '#0080FF'),
  ('shipped',     'Shipped',     3, FALSE, '#8000FF'),
  ('delivered',   'Delivered',   4, TRUE,  '#00A000'),
  ('cancelled',   'Cancelled',   5, TRUE,  '#FF0000');

CREATE TABLE orders (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer  VARCHAR(100) NOT NULL,
  status_id TINYINT UNSIGNED NOT NULL DEFAULT 1,
  FOREIGN KEY (status_id) REFERENCES order_statuses(id)
);

-- Adding a new status requires only an INSERT — no ALTER TABLE
INSERT INTO order_statuses (code, label, sort_order, is_terminal, color)
VALUES ('refunded', 'Refunded', 6, TRUE, '#FF6600');

Tip

For most real-world applications, the lookup table pattern is more maintainable than ENUM. You can add new values without an ALTER TABLE, add metadata (labels, colors, sort order), and query the options easily. Use ENUM only for values that are truly permanent and well-understood.

When NOT to Use ENUM

Values that will evolve: If you expect to add new values regularly (product categories, country codes, user roles), use a lookup table instead. ALTER TABLE on large tables is painful.
Values with metadata: If each option needs a label, color, sort order, or description, a lookup table is far more appropriate.
Cross-database compatibility: ENUM is a MySQL/MariaDB extension. PostgreSQL doesn't support it the same way — using VARCHAR with a CHECK constraint is more portable.
Multi-value scenarios: ENUM only holds one value. For multiple selections, SET (limited to 64 items) or a junction table is needed.
Application-driven validation: If your application already validates the value before inserting, the ENUM constraint adds complexity with limited benefit over VARCHAR.

Practical ENUM Example: User Status

SQL

CREATE TABLE users (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email         VARCHAR(254) NOT NULL UNIQUE,
  status        ENUM('active', 'inactive', 'suspended', 'deleted')
                NOT NULL DEFAULT 'active',
  role          ENUM('user', 'moderator', 'admin')
                NOT NULL DEFAULT 'user',
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Query patterns
SELECT COUNT(*) FROM users WHERE status = 'active';
SELECT COUNT(*) FROM users WHERE status != 'deleted' GROUP BY role;

-- Transition logic
UPDATE users
SET status = 'suspended'
WHERE last_login_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)
  AND status = 'active';

-- Find all valid statuses (query the schema)
SELECT SUBSTRING(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE) - 6) AS enum_values
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'myapp'
  AND TABLE_NAME = 'users'
  AND COLUMN_NAME = 'status';