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? 🚀