MySQL - ENUM

MySQL: The ENUM Data Type

The ENUM (short for enumerator) is a string data type in MySQL that allows a column to have a value chosen from a predefined list of allowed string values. These values are specified when you define the table schema.

Internally, MySQL stores each ENUM member as a small integer (its index), which can be very storage-efficient. The first member in the list has an index of 1, the second is 2, and so on.

Defining and Using ENUM Columns

You define an ENUM column by providing the list of acceptable string values.

An ENUM column can have a maximum of 65,535 distinct members. The NULL value is also permitted, as is an empty string '' if allowed by the definition.

Syntax

CREATE TABLE table_name (
    column_name ENUM('value1', 'value2', 'value3', ...)
);

Example: A Task Management System

Let’s create a tasks table with a status column that can only be one of a few specific values.

CREATE TABLE tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    status ENUM('pending', 'in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'pending'
);

Now, let’s insert some valid records into the tasks table.

INSERT INTO tasks (title, status)
VALUES
    ('Design the new UI', 'in_progress'),
    ('Write the API documentation', 'completed'),
    ('Deploy to staging server', 'pending');

If you try to insert a value that is not in the predefined list, MySQL (with a strict SQL mode) will reject the operation and raise an error.

-- This will FAIL with an error
INSERT INTO tasks (title, status) VALUES ('Review PRs', 'waiting');

-- ERROR 1265 (01000): Data truncated for column 'status' at row 1

Working with ENUM’s Numeric Indexes

While you should always prefer using the string values for clarity, MySQL allows you to insert and filter data using the 1-based numeric index of the ENUM members.

For our tasks.status enum: 'pending'=1, 'in_progress'=2, 'completed'=3, 'cancelled'=4.

Example: Inserting and Filtering by Index

-- Insert a new task using the numeric index for 'cancelled' (4)
INSERT INTO tasks (title, status) VALUES ('Plan Q3 roadmap', 4);

-- Fetch all tasks that are 'in_progress' (index 2)
SELECT * FROM tasks WHERE status = 2;

Output of the SELECT query

id	title	status
1	Design the new UI	in_progress

Warning: Relying on numeric indexes is strongly discouraged. It makes queries hard to read and extremely brittle. If the order of ENUM members changes, your queries will break silently, pointing to the wrong data.

When to Use (and When to Avoid) ENUM

ENUM can be tempting due to its storage efficiency, but it should be used with extreme caution.

Acceptable Use Cases

Use ENUM only for values that are truly static and will never change. For example:

• Days of the week: ENUM('Mon', 'Tue', ...)
• Binary states with clear names: ENUM('active', 'inactive') (though BOOLEAN is often better).
• Data with a fixed, universal, and unchanging set of options.

When to AVOID ENUM

Avoid ENUM for any data that might change, even infrequently. Examples include:

• Product categories
• User roles or permissions
• Tags or labels
• Statuses in a workflow that might evolve

The Dangers and Disadvantages of ENUM

• Inflexibility: Modifying the list of ENUM members requires an ALTER TABLE statement. On large tables, this is a very expensive and slow operation that can lock the table and cause significant downtime.
• Poor Portability: ENUM is a MySQL-specific feature. If you ever need to migrate your database to another system like PostgreSQL or SQL Server, you will have to refactor your schema.
• Brittleness: As mentioned, queries relying on numeric indexes can break silently if the ENUM definition changes. Even reordering the members will change their indexes.
• Application Coupling: The complete set of possible values is defined in the database schema. If your application needs this list (e.g., to populate a dropdown), it can’t easily query it. This tightly couples your application logic to the database structure.

Best Practice: The Lookup Table Alternative

For almost all use cases, a lookup table with a foreign key is the superior, more scalable, and more flexible alternative to ENUM.

Example: Refactoring tasks with a Lookup Table

-- 1. Create the lookup table for statuses
CREATE TABLE task_statuses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- 2. Populate it with the possible values
INSERT INTO task_statuses (name) VALUES ('pending'), ('in_progress'), ('completed'), ('cancelled');

-- 3. Modify the original table to use a foreign key
CREATE TABLE tasks_refactored (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    status_id INT NOT NULL,
    CONSTRAINT fk_status FOREIGN KEY (status_id) REFERENCES task_statuses(id)
);

Advantages of the Lookup Table Approach

• Flexibility: Adding a new status is a simple INSERT into task_statuses. No expensive ALTER TABLE is needed.
• Rich Data: The task_statuses table can be expanded with more columns, like description, is_final_state, etc.
• Portability: This pattern is standard SQL and works across all relational database systems.
• Discoverability: The application can easily query the task_statuses table to get the list of all possible options for a UI dropdown.

Using ENUMs in Application Code

If you must work with an existing schema that uses ENUM, modern database drivers handle them as strings. Here are secure, modern examples of interacting with the ENUM column from our first tasks example.

Python (mysql-connector)
Node.js (async/await)
Java (JDBC)
PHP (PDO)



This Python example uses parameterized queries, which correctly handle the string value of the enum.

```python
# main.py
import mysql.connector

config = {
    'user': 'root', 'password': 'password', 'host': '127.0.0.1', 'database': 'your_database'
}

status_to_find = 'completed'

with mysql.connector.connect(**config) as cnx:
    with cnx.cursor(dictionary=True) as cursor:
        query = "SELECT id, title, status FROM tasks WHERE status = %s"
        cursor.execute(query, (status_to_find,))

        print(f"Tasks with status '{status_to_find}':")
        for row in cursor:
            print(f"  ID: {row['id']}, Title: {row['title']}")

This mysql2/promise example demonstrates inserting and querying ENUM values as simple strings.

main.js

import mysql from 'mysql2/promise';

async function main() {
  let connection;
  try {
    connection = await mysql.createConnection({ /* connection config */ });

    const new_status = 'pending';
    const [rows] = await connection.execute(
      'SELECT id, title FROM tasks WHERE status = ?',
      [new_status]
    );

    console.log(`Tasks with status '${new_status}':`);
    rows.forEach(row => console.log(`  ID: ${row.id}, Title: ${row.title}`))

  } finally {
    if (connection) await connection.end();
  }
}
main();

With JDBC, you can use setString on a PreparedStatement to work with ENUM values.

Main.java

import java.sql.*;

public class MysqlEnumExample {
    // ... connection details ...

    public static void main(String[] args) {
        String sql = "SELECT id, title FROM tasks WHERE status = ?";
        String statusToFind = "in_progress";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, statusToFind);

            try (ResultSet rs = pstmt.executeQuery()) {
                System.out.printf("Tasks with status '%s':\n", statusToFind);
                while (rs.next()) {
                    System.out.printf("  ID: %d, Title: %s\n",
                        rs.getInt("id"), rs.getString("title"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

PDO handles ENUM values as strings seamlessly in prepared statements.

<?php
// config.php with $pdo connection
require 'config.php';

$status_to_find = 'completed';
$stmt = $pdo->prepare('SELECT id, title FROM tasks WHERE status = :status');
$stmt->execute(['status' => $status_to_find]);

$tasks = $stmt->fetchAll();

echo "Tasks with status '$status_to_find':\n";
foreach ($tasks as $task) {
    echo sprintf("  ID: %d, Title: %s\n", $task['id'], $task['title']);
}
?>