Understanding the ENUM Data Type in MySQL: Complete Guide with Examples

1. Overview of the ENUM Type

What Is the ENUM Type?

The ENUM (enumeration) type in MySQL is a data type that stores exactly one value from a predefined list. Since only specific predefined strings can be saved in the column, it ensures data consistency and prevents invalid values from being inserted.

For example, when a user chooses one option from limited choices such as gender or product category, using the ENUM type eliminates unnecessary error-checking logic. The following is an example of creating a table containing an ENUM column:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category ENUM('食品', '衣料品', '家電', '家具') NOT NULL
);

In this example, the category column can store only one of the following values: “食品”, “衣料品”, “家電”, or “家具”. This helps simplify data management and reduces the risk of incorrect input.

Main Use Cases of ENUM

The ENUM type is commonly used in scenarios such as:

• Status management: Tracking workflow stages such as “未開始”, “進行中”, or “完了”.
• Category definition: Managing predefined options such as product categories, user types, or job positions.
• Ranking or level grouping: Game difficulty levels (“初級”, “中級”, “上級”) or product ratings (“良い”, “普通”, “悪い”).

2. Advantages and Disadvantages of ENUM

Advantages

1. Improved data consistency
   ENUM allows only values included in its predefined list, enabling consistent data handling. For example, when managing gender, only defined values such as “男性” or “女性” are permitted, preventing invalid inputs.
2. Efficient storage usage
   Each ENUM value is stored internally as an integer index. As a result, it consumes less storage compared to VARCHAR. For example, values like ‘small’ or ‘large’ consume more space as VARCHAR, but ENUM stores them efficiently using numeric indexes.

Disadvantages

1. Lack of flexibility
   ENUM values are fixed. Adding new options requires altering the table structure, making ENUM unsuitable for frequently changing datasets.
2. Complex error handling
   If an invalid value is inserted, MySQL may generate an error or store an empty string. This can complicate debugging for developers.

3. Configuring ENUM and Practical Usage

Basic Setup and Error Handling

To configure an ENUM type, simply define the list of valid strings. The following example defines an ENUM column in a table:

CREATE TABLE shirts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    size ENUM('XS', 'S', 'M', 'L', 'XL') NOT NULL
);

Here, the size column accepts only “XS”, “S”, “M”, “L”, or “XL”. Attempting to insert a value not in the list (e.g., ‘XXL’) results in a Data truncated error. This prevents invalid data and maintains consistency.

Practical Usage Example

The following example uses ENUM to manage user roles such as “管理者”, “一般ユーザー”, and “ゲスト”:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role ENUM('管理者', '一般ユーザー', 'ゲスト') NOT NULL
);

Using an ENUM column makes it easy to maintain consistent permissions and role-based control.

4. Indexing and Handling NULL with ENUM

Using Indexes

ENUM values are assigned integer indexes based on their position in the list. For example:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    size ENUM('S', 'M', 'L', 'XL')
);

“S” has index 1, “M” index 2, and so on. These indexes can be used in WHERE clauses:

SELECT * FROM products WHERE size = 2;

This query retrieves records where the size corresponds to “M”.

Handling NULL and Empty Strings

If NULL is allowed, the column may store NULL as a valid state even if not in the ENUM list. Additionally, invalid data inserted as an empty string is stored with index 0, making incorrect inputs easy to detect.

5. Character Sets and Collations for ENUM

How to Set Character Sets and Collations

ENUM, like CHAR or VARCHAR, supports character set and collation definitions. This is important when handling multilingual data or sorting operations. Example:

CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    language ENUM('日本語', '英語', '中国語') 
        CHARACTER SET utf8 
        COLLATE utf8_general_ci
);

This example specifies UTF-8 and a general-purpose collation.

6. Extending ENUM and Alternatives

Techniques for Expanding ENUM

Since ENUM lacks flexibility for dynamic values, you can include an “Other” option and store free-form input in a separate column:

ALTER TABLE products 
MODIFY COLUMN category ENUM('食品', '衣料品', '家電', '家具', 'その他') NOT NULL,
ADD COLUMN category_other VARCHAR(255) DEFAULT NULL;

This allows storing values not included in the ENUM list while maintaining structure.

Alternative Data Types: SET or VARCHAR

Depending on requirements, alternatives include:

• SET type: Stores multiple selections from a list.
• VARCHAR: Offers maximum flexibility without predefined constraints.