MySQL ENUM 使用指南

在本教程中,我们将学习如何使用 MySQL ENUM 数据类型来定义存储枚举值的列。

在 MySQL 中,一个 ENUM 是一个字符串的列表,它定义了一个列中允许的值,列的值只能是创建列时定义的允许值列表中的的一个。

MySQL ENUM 数据类型列适合存储状态和标识等有限数量的固定值的数据。

MySQL ENUM 数据类型具有以下优点:

  • 列值的可读性更强。
  • 紧凑的数据存储。MySQL 存储 ENUM 时只存储枚举值对应的数字索引 (1, 2, 3, …)。

MySQL ENUM 语法

要是使用 ENUM 数据类型,这是它的语法:

ENUM ('v1', 'v2', ..., 'vn')

这里,

  • ENUM 是一个关键字,用来声明一个枚举类型。
  • v1vn 是此 ENUM 类型的可选项列表,使用 ENUM 类型的列的值只能是上面值中的其中一个。
  • 枚举值只能是字符串。

要定义 ENUM 列,请使用以下语法:

CREATE TABLE table_name
(col_name ENUM ('v1','v2', ..., 'vn'));

ENUM 数据类型中,您可以拥有多个枚举值。但是,将枚举值的数量保持在 20 以下是一种很好的做法。

MySQL ENUM 实例

让我们看看下面的例子。

假设,我们有一个订单表用来存放电子商务订单。其中,订单状态 state 只有四种状态,如下表:

状态名 状态值
未支付 Unpaid
已支付 Paid
已发货 Shipped
已完成 Completed

那么我们就要为 state 列使用 ENUM 类型。

使用 MySQL ENUM 列

请使用以下 CREATE TABLE 语句:

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    state ENUM('Unpaid', 'Paid', 'Shipped', 'Completed') NOT NULL
);

现在,我们已经创建好了 orders 表,其中 state 列为 ENUM 数据类型,并且它将只接受四个值: Unpaid, Paid, ShippedCompleted。同时,按照列定义时的顺序,Unpaid, Paid, ShippedCompleted 的索引分别为 1, 2, 3, 4

插入 MySQL ENUM 值

要将插入数据ENUM 列中,请使用预定义列表中的枚举值。否则,MySQL 会给出错误。 例如,以下语句向 orders 表中插入一个新行。

INSERT INTO orders(title, state)
VALUES ('Apples', 'Paid');

除了枚举值,您还可以使用枚举成员的数字索引将数据插入到 ENUM 列中。例如:

INSERT INTO orders(title, state)
VALUES ('Bananas', 2);

在这个例子中,我们没有使用 Paid 枚举值,而是使用了值 2。由于 Paid 的索引是 2,所以是可以接受的。

让我们继续向 orders 表中添加更多行:

INSERT INTO orders(title, state)
VALUES ('Pears', 'Shipped'),
    ('Peaches', 'Completed');

因为我们定义了 state 一个 NOT NULL 列,当你插入一个新行而不指定state 列的值时,MySQL 将使用第一个枚举成员作为默认值。请执行以下 SQL 语句:

INSERT INTO orders(title) VALUES('Oranges');

这里,我们没有 state 列指定值,MySQL 将默认插入第一个枚举成员 Unpaid

我们可以查询表中的所有行验证刚刚的所有操作。

SELECT * FROM orders;
+----+---------+-----------+
| id | title   | state     |
+----+---------+-----------+
|  1 | Apples  | Paid      |
|  2 | Bananas | Paid      |
|  3 | Pears   | Shipped   |
|  4 | Peaches | Completed |
|  5 | Oranges | Unpaid    |
+----+---------+-----------+

在非严格 SQL 模式下,如果在 ENUM 列中插入无效值,MySQL 将使用带有数字索引 0 的空字符串 '' 进行插入。如果启用了严格 SQL 模式,尝试插入无效 ENUM 值将导致错误。

请注意,如果将 ENUM 列定义为可为空的列,则该列可以接受 NULL 值。

过滤 MySQL ENUM 值

以下语句获取所有的状态为已付款的订单:

SELECT * FROM orders WHERE state = 'Paid';
+----+---------+-------+
| id | title   | state |
+----+---------+-------+
|  1 | Apples  | Paid  |
|  2 | Bananas | Paid  |
+----+---------+-------+

由于枚举成员 Paid 对应的数字索引是 2,因此以下查询返回相同的结果集:

SELECT * FROM orders WHERE state = 2;

排序 MySQL ENUM 值

MySQL 根据索引号对 ENUM 值进行排序。因此,枚举成员的顺序取决于它们在枚举列表中的定义方式。

因为,state 列定义为 state ENUM('Unpaid', 'Paid', 'Shipped', 'Completed') NOT NULL,那么当我们对 state 列升序排序时, Unpaid 是排在最前面的,而 Completed 是排在最后面的。

请执行下面的语句并观察输出:

SELECT * FROM orders ORDER BY state;
+----+---------+-----------+
| id | title   | state     |
+----+---------+-----------+
|  5 | Oranges | Unpaid    |
|  1 | Apples  | Paid      |
|  2 | Bananas | Paid      |
|  3 | Pears   | Shipped   |
|  4 | Peaches | Completed |
+----+---------+-----------+

因此,如果你需要按照枚举列进行排序,那么在创建列时按照要排序的顺序定义枚举值。

MySQL ENUM 的缺点

MySQL ENUM 带来一些好处,比如可读性和存储效率,但是它也有以下缺点:

  1. 更改枚举成员需要使用ALTER TABLE 语句重建整个表,这在资源和时间方面都是昂贵的。

  2. 获取完整的枚举列表很复杂,因为您需要访问 information_schema 数据库:

    SELECT column_type
    FROM information_schema.COLUMNS
    WHERE TABLE_NAME = 'orders'
        AND COLUMN_NAME = 'state';
    
  3. 因为 ENUM 它不是 SQL 标准的,因此,移植到其他 RDBMS 可能是一个问题。

  4. 枚举列表是不可重用的。例如,上面 orders 表中的状态枚举值,不能重用到新建的其他表上。

  5. 枚举值是字符串,不能包含更多的信息。比如我们需要在每个订单状态上添加超时属性。

结论

在本教程中,我们向您介绍了 MySQL ENUM 数据类型以及如何使用它来定义存储枚举值的列。