Mysql 中 ENUM 的用法

一、ENUM 数据类型的定义和用法

ENUM 是一个字符串对象，表示从允许的字符串值列表中选择的单个值。定义 ENUM 字段时，需要指定该字段可以接受的所有可能值。

1. 基本语法

定义ENUM字段时，必须指定允许的值列表。语法如下：

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

在这个语法中：

• column_name 是列名。
• 'value1', 'value2', 'value3', ... 是该字段允许的字符串值列表。
• NOT NULL 是一个约束，表示字段不能为空。如果不指定，默认情况下ENUM字段可以为空。

2. 创建包含 ENUM 字段的表

以下是一个使用ENUM类型的实际示例，用于创建一个用户表，其中包含性别和用户状态字段：

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    gender ENUM('Male', 'Female', 'Other') NOT NULL,
    status ENUM('Active', 'Inactive', 'Pending') DEFAULT 'Pending'
);

在这个示例中：

• gender 字段的值必须是 'Male', 'Female', 或 'Other' 之一。
• status 字段的值必须是 'Active', 'Inactive', 或 'Pending' 之一，并且默认值为 'Pending'。

3. 插入数据

向包含 ENUM 字段的表中插入数据时，只能使用 ENUM 定义时指定的值：

INSERT INTO users (username, gender, status) VALUES ('Alice', 'Female', 'Active');

如果尝试插入未定义的值，MySQL会返回错误。例如，下面的查询会失败：

INSERT INTO users (username, gender, status) VALUES ('Bob', 'Unknown', 'Active');

此语句会失败，因为 'Unknown' 不是 gender 字段的有效值。

4. 查询数据

可以像查询普通字符串字段一样查询 ENUM 字段：

SELECT * FROM users WHERE gender = 'Female';

也可以使用条件查询和排序：

SELECT * FROM users ORDER BY status;

ENUM 字段按其定义顺序排序，这意味着在这个例子中，'Active' 会在 'Inactive' 和 'Pending' 之前。

二、ENUM 的内部存储机制

ENUM 类型在内部是以整数存储的，每个值对应一个整数索引。例如，如果 gender ENUM('Male', 'Female', 'Other')，则在内部，MySQL 会将 'Male' 存储为 1，'Female' 存储为 2，'Other' 存储为 3。使用 ENUM 可以在节省存储空间的同时加快某些查询的速度。

1. 查询索引

你可以使用以下语句来查看 ENUM 字段的存储值：

SELECT gender + 0 FROM users;

这条查询将返回 gender 字段的整数表示，例如 1 表示 'Male'，2 表示 'Female'。

三、ENUM 的优点和局限性

1. 优点

• 数据完整性：ENUM 确保列值只能是预定义的集合中的某个值，从而提高了数据的完整性，防止无效值插入。
• 节省存储空间：由于 ENUM 在内部以整数存储，节省了存储空间，特别是当枚举值的字符串较长时。
• 查询优化：在某些情况下，由于内部使用整数存储，ENUM 类型可以加快查询速度。

2. 局限性

• 扩展性差：如果需要添加或修改 ENUM 列中的值，需要使用 ALTER TABLE 语句，这在大型表上可能很耗时且复杂。
• 排序限制：ENUM 的排序是根据定义的顺序，而不是字母顺序或数值顺序。如果排序需求发生变化，可能需要重新定义 ENUM 的顺序。
• 限定于单选：ENUM 字段一次只能存储一个值，如果需要存储多个选项（如多选框的情况），需要使用 SET 类型或通过关联表来实现。

四、ENUM 的实际应用场景

ENUM 类型在一些特定的场景下非常有用，以下是几个常见的应用场景：

1. 状态字段

ENUM 非常适合用于表示实体的状态。例如，订单状态可以是 'Pending', 'Processing', 'Shipped', 'Delivered'：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_status ENUM('Pending', 'Processing', 'Shipped', 'Delivered') DEFAULT 'Pending'
);

2. 性别、类别等有限选项

对于性别、类别、级别等字段，ENUM 提供了一种有效的存储方式，确保只有预定义的值才能被插入。

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100),
    emp_gender ENUM('Male', 'Female', 'Other')
);

3. 简单标签系统

在某些简单的标签系统中，ENUM 可以用于定义固定的标签集合，比如文章的类别或标签：

CREATE TABLE articles (
    article_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    category ENUM('Technology', 'Health', 'Lifestyle', 'Finance') NOT NULL
);

五、扩展和管理 ENUM 列

ENUM 列的管理涉及到表结构的更改，特别是当需要添加或删除 ENUM 的值时，可能会遇到挑战。你需要使用 ALTER TABLE 语句来修改 ENUM 列：

1. 添加新的 ENUM 值

ALTER TABLE users MODIFY gender ENUM('Male', 'Female', 'Other', 'Prefer not to say');

这个语句在 gender 列中添加了一个新的选项 'Prefer not to say'。

2. 删除 ENUM 值

要删除一个 ENUM 值，必须重新定义 ENUM 的值列表，删除不需要的值。注意，删除前要确保表中没有记录使用了该值，否则会导致数据丢失或转换为默认值：

ALTER TABLE users MODIFY gender ENUM('Male', 'Female', 'Other');

3. 重排 ENUM 的顺序

如果你希望改变 ENUM 的排序顺序，可以通过重新定义 ENUM 列来实现：

ALTER TABLE users MODIFY status ENUM('Inactive', 'Pending', 'Active');

这个语句将 status 列的顺序更改为 'Inactive', 'Pending', 'Active'。

六、总结

ENUM 是MySQL中一种非常有用的数据类型，尤其适合存储预定义的、有限的字符串值。它通过确保字段值只能是预定义的集合中的一个值，帮助维护数据的完整性。ENUM 在存储空间和查询效率方面也具有一定的优势。

然而，ENUM 类型也有其局限性，例如扩展性不强、排序固定、一次只能选择一个值等。因此，选择 ENUM 类型时，需要根据具体的应用场景和需求权衡其优缺点。