What is ENUM in MySQL: Definition and Usage

ENUM in MySQL is a special data type that allows you to store one value from a predefined list of strings. It is useful for columns where the value must be one of a limited set of options, like days of the week or status types.

How It Works

Think of ENUM as a dropdown menu in a form where you can only pick one option from a fixed list. When you create an ENUM column, you define all possible values it can hold. Internally, MySQL stores these values as numbers, but when you query the data, it shows the string you chose.

This makes ENUM efficient because it uses less storage than storing full strings every time. It also helps keep data clean by preventing invalid entries since only the listed values are allowed.

Example

This example creates a table with an ENUM column for the status of an order. The status can only be 'pending', 'shipped', or 'delivered'.

sql

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  status ENUM('pending', 'shipped', 'delivered') NOT NULL
);

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

SELECT * FROM orders;

Output

id | status
---|---------
1  | pending
2  | shipped

When to Use

Use ENUM when you have a column that should only contain a few specific values. For example, user roles like 'admin', 'editor', 'viewer', or product sizes like 'small', 'medium', 'large'.

This helps avoid typos and invalid data, and can improve performance and storage efficiency compared to using plain text columns.

Key Points

• ENUM stores one value from a fixed list of strings.
• It saves space by storing values as numbers internally.
• Only predefined values are allowed, preventing invalid data.
• Good for columns with limited, known options.
• Changing the list of values requires altering the table.

Key Takeaways

ENUM restricts a column to predefined string values for data consistency.

It stores values efficiently by using internal numeric indexes.

Ideal for fields with limited, fixed options like status or categories.

Changing allowed values requires modifying the table structure.

Using ENUM helps prevent invalid or unexpected data entries.