The World of MySQL & MariaDB

MySQL ENUM Type: Guide to Usage, Benefits & Pitfalls

Data Types
目次

1. Overview of ENUM Type

What is ENUM Type

MySQL’s ENUM (enumeration) type is a data type that stores a single value chosen from a predefined list. Because only the specified strings in the list can be saved in the column, it helps maintain data consistency and prevents invalid data entry. For example, when a user selects one option from a limited set such as gender or product category, using an ENUM type can eliminate unnecessary error checking. Below is an example of creating a table with 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 four values “食品”, “衣料品”, “家電”, or “家具”. This simplifies data management and reduces the risk of incorrect entries.

Main Uses of ENUM Type

ENUM type is primarily used for the following purposes:
  • Status Management: Enumerate states such as “未開始”, “進行中”, “完了” to manage project progress.
  • Categorization: Used when managing predefined category options for products, user types, job titles, etc.
  • Ranking: For game difficulty levels (“初級”, “中級”, “上級”) or product ratings (“良い”, “普通”, “悪い”), etc.

2. Advantages and Disadvantages of the ENUM Type

Advantages

  1. Improved Data Consistency With ENUM, only values from the specified list can be stored, ensuring data consistency and easier management. For example, when managing gender, only specific values such as “Male” or “Female” are allowed, preventing incorrect input.
  2. Storage Efficiency ENUM assigns an integer index to each value in the list, so values are actually stored as integers, saving storage space compared to VARCHAR. For example, storing size information like ‘small’ or ‘large’ in a VARCHAR consumes more storage, whereas defining them with ENUM allows for efficient storage.

Disadvantages

  1. Lack of Flexibility ENUM only allows fixed choices, so adding new values requires altering the table structure. Therefore, it is unsuitable for cases where options need to grow dynamically.
  2. Difficulty in Error Handling Inserting an invalid value can cause an error or result in an empty string being stored, which can make debugging more complex for developers.

3. Setting and Using ENUM Types

Basic Configuration and Error Handling

ENUM type setting simply specifies the strings that can be stored in the list. Below is an example of defining an ENUM column in a table:
CREATE TABLE shirts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    size ENUM('XS', 'S', 'M', 'L', 'XL') NOT NULL
);
In this case, the “size” column can store only one of the five values “XS”, “S”, “M”, “L”, or “XL”. If you try to insert a value not in the list (e.g., ‘XXL’), a Data truncated error occurs. This prevents storing values outside the list and helps maintain data consistency.

Practical Example

Next, here’s an example of using an ENUM type to manage user roles (“Administrator”, “Regular User”, “Guest”).
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role ENUM('管理者', '一般ユーザー', 'ゲスト') NOT NULL
);
When assigning different permissions based on role, using an ENUM column makes it easier to maintain data integrity.

4. Indexes of ENUM Types and Handling of NULL

Using Indexes

ENUM values are assigned indexes starting from 1 according to the order in the list. For example, consider an ENUM column that stores size information as follows:
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    size ENUM('S', 'M', 'L', 'XL')
);
‘S’ is assigned index 1, ‘M’ index 2, and so on. This index can also be used in WHERE clause conditions, enabling efficient data manipulation.
SELECT * FROM products WHERE size = 2;
This query retrieves records where the size is ‘M’.

Handling NULL and Empty Strings

Allowing NULL in an ENUM column lets you store NULL values even when they are not in the list. Additionally, if an empty string is inserted as erroneous data, it is stored as index 0, making incorrect entries identifiable.

5. Character Set and Collation for ENUM Type

How to Set Character Set and Collation

Like CHAR and VARCHAR, the ENUM type can specify a character set and collation. This is especially important for multilingual support and when performing searches that consider collation. Below is an example:
CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    language ENUM('日本語', '英語', '中国語') CHARACTER SET utf8 COLLATE utf8_general_ci
);
In this example, the UTF-8 character set and a common collation are specified.

6. Extensibility of ENUM Types and Alternatives

Techniques for Extending

Because ENUM types lack the flexibility to store arbitrary values in a column, they are unsuitable for data that changes dynamically. In this case, you can add an “Other” option and provide a separate free‑input column:
ALTER TABLE products 
MODIFY COLUMN category ENUM('Food', 'Clothing', 'Electronics', 'Furniture', 'Other') NOT NULL,
ADD COLUMN category_other VARCHAR(255) DEFAULT NULL;
In this way, items not present in the ENUM type are stored in a separate VARCHAR column, allowing dynamic handling.

SET and VARCHAR Types as Alternatives

As an alternative to ENUM, considering the SET type, which can store multiple choices, or the more flexible VARCHAR type is also an option.