A Comprehensive Guide to Using MySQL ENUM

A Comprehensive Guide to Using MySQL ENUM

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)

  1. Create a new ENUM column without the unwanted value.
  2. Copy data from the old column.
  3. 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?

FeatureENUMVARCHAR
Storage1-2 bytesFull string size
PerformanceFaster (stores as index)Slower for predefined values
FlexibilityLimited to predefined valuesAny text allowed
Altering ValuesComplex (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

ErrorCauseSolution
Data truncated for column 'status'Inserting an invalid ENUM valueEnsure the value exists in ENUM definition
Invalid default value for ENUM columnUsing a value not in ENUM as defaultSet a valid ENUM value as default
Incorrect integer valueTreating ENUM as integer incorrectlyUse 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? 🚀

Soeng Souy

Soeng Souy

Website that learns and reads, PHP, Framework Laravel, How to and download Admin template sample source code free.

Post a Comment

CAN FEEDBACK
close