MySQL - ENUM

MySQL：ENUM 数据类型

ENUM（枚举符的缩写）是 MySQL 中的一种字符串数据类型，它允许列的值从预定义允许的字符串值列表中选择。这些值在你定义表模式时指定。

在内部，MySQL 将每个 ENUM 成员存储为一个小整数（其索引），这可以非常节省存储空间。列表中的第一个成员索引为 1，第二个为 2，依此类推。

定义和使用 ENUM 列

你通过提供可接受的字符串值列表来定义 ENUM 列。

一个 ENUM 列最多可以有 65,535 个不同的成员。如果定义允许，NULL 值和空字符串 '' 也是允许的。

语法

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

示例：任务管理系统

让我们创建一个 tasks 表，其中 status 列只能是少数几个特定值之一。

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'
);

现在，让我们向 tasks 表中插入一些有效记录。

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

如果你尝试插入一个不在预定义列表中的值，MySQL（在严格 SQL 模式下）将拒绝该操作并引发错误。

-- 这将以错误失败
INSERT INTO tasks (title, status) VALUES ('Review PRs', 'waiting');

-- 错误 1265 (01000)：列 'status' 的数据在第 1 行被截断

使用 ENUM 的数字索引

虽然你应始终优先使用字符串值以提高清晰度，但 MySQL 允许你使用 ENUM 成员的基于 1 的数字索引来插入和过滤数据。

对于我们的 tasks.status 枚举：'pending'=1, 'in_progress'=2, 'completed'=3, 'cancelled'=4。

示例：通过索引插入和过滤

-- 使用 'cancelled' 的数字索引（4）插入新任务
INSERT INTO tasks (title, status) VALUES ('Plan Q3 roadmap', 4);

-- 获取所有 'in_progress'（索引 2）状态的任务
SELECT * FROM tasks WHERE status = 2;

SELECT 查询的输出

id	title	status
1	Design the new UI	in_progress

警告： 强烈不建议依赖数字索引。它会使查询难以阅读且极其脆弱。如果 ENUM 成员的顺序发生变化，你的查询将默默地中断，指向错误的数据。

何时使用（以及何时避免）ENUM

ENUM 由于其存储效率而可能具有吸引力，但应极其谨慎地使用。

可接受的用例

仅对那些真正静态且永远不会改变的值使用 ENUM。例如：

• 一周中的日子：ENUM('Mon', 'Tue', ...)
• 具有明确名称的二进制状态：ENUM('active', 'inactive')（尽管 BOOLEAN 通常更好）。
• 具有固定、通用且不变选项集的数据。

何时避免 ENUM

避免对任何可能更改的数据使用 ENUM，即使是偶尔更改。示例包括：

• 产品类别
• 用户角色或权限
• 标签
• 可能演变的工作流中的状态

ENUM 的危险和缺点

• 不灵活性： 修改 ENUM 成员列表需要 ALTER TABLE 语句。在大型表上，这是一个非常昂贵且缓慢的操作，可能会锁定表并导致长时间停机。
• 可移植性差： ENUM 是 MySQL 特有的功能。如果你需要将数据库迁移到 PostgreSQL 或 SQL Server 等其他系统，你将不得不重构你的模式。
• 脆弱性： 如前所述，如果 ENUM 定义发生更改，依赖数字索引的查询可能会默默中断。即使重新排序成员也会改变它们的索引。
• 应用程序耦合： 完整的可能值集在数据库模式中定义。如果你的应用程序需要此列表（例如，填充下拉菜单），它无法轻松查询。这会将你的应用程序逻辑与数据库结构紧密耦合。

最佳实践：查找表替代方案

对于几乎所有用例，带有外键的查找表是比 ENUM 更优越、更具可伸缩性和更灵活的替代方案。

示例：使用查找表重构 tasks

-- 1. 创建状态查找表
CREATE TABLE task_statuses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- 2. 使用可能的值填充它
INSERT INTO task_statuses (name) VALUES ('pending'), ('in_progress'), ('completed'), ('cancelled');

-- 3. 修改原始表以使用外键
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);
)

查找表方法的优点

• 灵活性： 添加新状态只是简单地向 task_statuses 表执行 INSERT 操作。无需昂贵的 ALTER TABLE 操作。
• 丰富数据： task_statuses 表可以扩展更多列，例如 description、is_final_state 等。
• 可移植性： 这种模式是标准 SQL，适用于所有关系数据库系统。
• 可发现性： 应用程序可以轻松查询 task_statuses 表，以获取 UI 下拉菜单的所有可能选项列表。

在应用程序代码中使用 ENUM

如果你必须使用一个已存在的、使用 ENUM 的模式，现代数据库驱动程序会将其作为字符串处理。以下是与我们第一个 tasks 示例中的 ENUM 列进行交互的安全、现代示例。

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



此 Python 示例使用参数化查询，正确处理枚举的字符串值。

```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}':") # 状态为 '{status_to_find}' 的任务：
        for row in cursor:
            print(f"  ID: {row['id']}, Title: {row['title']}") # ID：{row['id']}，标题：{row['title']}

此 mysql2/promise 示例演示了将 ENUM 值作为简单字符串插入和查询。

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}':`); // 状态为 '${new_status}' 的任务：
    rows.forEach(row => console.log(`  ID: ${row.id}, Title: ${row.title}`)) // ID：${row.id}，标题：${row.title}

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

使用 JDBC，你可以对 PreparedStatement 使用 setString 来处理 ENUM 值。

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); // 状态为 '%s' 的任务：
                while (rs.next()) {
                    System.out.printf("  ID: %d, Title: %s\n", // ID：%d，标题：%s
                        rs.getInt("id"), rs.getString("title"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

PDO 在预处理语句中无缝处理 ENUM 值作为字符串。

<?php
// config.php with $pdo connection (包含 $pdo 连接的 config.php)
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"; // 状态为 '$status_to_find' 的任务：
foreach ($tasks as $task) {
    echo sprintf("  ID: %d, Title: %s\n", $task['id'], $task['title']); // ID：%d，标题：%s
}
?>