A Comprehensive Guide to Using MySQL ENUM

The ENUM data type in MySQL is a string object that stores one value from a predefined list of possible values. It is useful when a column should contain a fixed set of values, such as "small", "medium", "large" or "pending", "approved", "rejected".

1. Why Use ENUM?

✔ Efficient Storage – Uses 1 or 2 bytes instead of full string values.
✔ Faster Queries – Since ENUM stores values as index numbers, comparisons are faster.
✔ Ensures Data Integrity – Only allows valid, predefined values.

2. MySQL ENUM Syntax

column_name ENUM('value1', 'value2', ..., 'valueN')

ENUM columns must have a predefined list of values.
The maximum number of values in a ENUM column can store 65,535.

3. Creating a Table with ENUM

Example: Order Status

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

✔ The status column only allows one of these four values.

Inserting Values

INSERT INTO orders (status) VALUES ('shipped');
INSERT INTO orders (status) VALUES ('delivered');

✔ The inserted values must match ENUM values; otherwise, MySQL throws an error.

4. Retrieving ENUM Values

Select ENUM values normally

SELECT * FROM orders WHERE status = 'shipped';

✔ Retrieves orders where the status is 'shipped'.

Sort ENUM values

SELECT * FROM orders ORDER BY status;

✔ ENUM values are sorted based on index numbers (not alphabetically).

To order ENUM values in custom order, use FIELD():

SELECT * FROM orders ORDER BY FIELD(status, 'pending', 'shipped', 'delivered', 'canceled');

5. Adding or Removing ENUM Values

If you need to modify an ENUM column:

Add a New ENUM Value

ALTER TABLE orders MODIFY COLUMN status ENUM('pending', 'shipped', 'delivered', 'canceled', 'returned');

✔ Adds 'returned' as a new option.

Remove an ENUM Value (Recreate the ENUM column)

Create a new ENUM column without the unwanted value.
Copy data from the old column.
Drop the old column and rename the new one.

Example:

ALTER TABLE orders CHANGE status status_old ENUM('pending', 'shipped', 'delivered', 'canceled');
ALTER TABLE orders DROP COLUMN status_old;

✔ MySQL does not provide a direct way to remove ENUM values.

6. Checking ENUM Column Information

Show ENUM Values in a Column

SHOW COLUMNS FROM orders LIKE 'status';

✔ Displays the list of ENUM values for status.

Get ENUM Index Values

Each ENUM value has an index number starting from 1:

SELECT status, FIELD(status, 'pending', 'shipped', 'delivered', 'canceled') AS status_index FROM orders;

✔ Returns the ENUM value along with its index.

7. Converting ENUM to Integer

To retrieve ENUM values as integers:

SELECT status, status+0 AS status_index FROM orders;

✔ This converts ENUM values into their numeric index.

8. ENUM vs. VARCHAR: Which One to Use?

Feature	ENUM	VARCHAR
Storage	1-2 bytes	Full string size
Performance	Faster (stores as index)	Slower for predefined values
Flexibility	Limited to predefined values	Any text allowed
Altering Values	Complex (ALTER needed)	Easy to update

Use ENUM when:
✅ You have fixed values (e.g., status, categories).
✅ You need faster performance and smaller storage.

Use VARCHAR when:
❌ You expect values to change often.
❌ You need greater flexibility.

9. Common Errors & Solutions

Error	Cause	Solution
`Data truncated for column 'status'`	Inserting an invalid ENUM value	Ensure the value exists in ENUM definition
`Invalid default value for ENUM column`	Using a value not in ENUM as default	Set a valid ENUM value as default
`Incorrect integer value`	Treating ENUM as integer incorrectly	Use `FIELD()` or `+0` conversion

10. Summary

ENUM stores predefined string values efficiently.
Faster and more storage-efficient than VARCHAR for fixed values.
Values are stored as numbers internally but retrieved as strings.
Modifying ENUM values requires ALTER TABLE.
Use ENUM when values are fixed, otherwise use VARCHAR.

Would you like a practical example of ENUM in a real-world project? 🚀

Souy Soeng

A Comprehensive Guide to Using MySQL ENUM