vlambda博客
学习文章列表

这段时间在做一个项目，里边有很多枚举型的字段，这让我想起来了MySQL的枚举类型ENUM，想在新项目启用，所以做了一下调研和测试，发现里边有许多需要注意的，所以便有了此篇文章。

ENUM 枚举型介绍

在MySQL中，ENUM是一个字符串对象，其值是从列创建时定义的允许值列表中选择的。

ENUM数据类型提供以下优点：

• 节省存储空间，MySQL ENUM使用数字索引(1，2，3，…)来表示字符串值。

• 可读查询和输出，数字将转换回查询结果中的相应字符串。

创建和使用ENUM列

枚举值必须是带引号的字符串文字。例如，可以创建一个包含ENUM如下列的表 ：

1
2
3
4

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);

这样，size字段只接受 x-small, small, medium, large, x-large这5个值。

插入数据：

1
2

INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
  ('polo shirt','small');

更新数据：

1
2
3
4
5

UPDATE shirts SET size = 'small' WHERE size = 'large';

输出：
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

如果更新或者插入的数据不存在：

1
2
3
4
5
6
7
8

INSERT INTO shirts (name, size) VALUES ('dress','big');
输出：
Data truncated for column 'size' at row 1

UPDATE shirts SET size = 'small' WHERE size = 'big';
输出：
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

采用ENUM类型记录'medium'这个字符串，只需要一个字节，如果采用varchar，则需要6个字节。如果是100万条记录，就是100万和600万的差距，如果是1000万……

枚举文字的索引值

每个枚举值都有一个索引，这个索引值是按枚举类型里的顺序从1开始的，在这个例子中，1、2、3、4、5分别代表 x-small, small, medium, large, x-large 。所以除了可以用枚举里的字符串插入、更新之外，还可以用序号。

插入一条数据，size为1：

1
2
3

INSERT INTO shirts (name, size) VALUES ('dress', 1);
输出：
Query OK, 1 row affected (0.05 sec)

这时可以看到dress的值为对应序号1的x-small：

1
2
3
4
5
6
7
8

SELECT * FROM shirts WHERE name = 'dress';
输出：
+-------+---------+
| name  | size    |
+-------+---------+
| dress | x-small |
+-------+---------+
1 row in set (0.00 sec)

空字符串错误的索引值为0，NULL值的索引是 NULL，例如，指定为的列ENUM('x-small', 'small', 'medium', 'large', 'x-large')对应的索引表如下：

ENUM类型的缺点

等义带来的一个坑

看到这里，细心的观众可能发现了ENUM类型的一个坑。

1
2

INSERT INTO shirts (name, size) VALUES ('dress','x-small');
INSERT INTO shirts (name, size) VALUES ('dress',1);

上述两条语句在MySQL中是等义的，如果size的字符串对象为 ENUM('0', '1', '2', '3', '4')，这时候插入数据：

1
2

INSERT INTO shirts_index (name, size) VALUES ('dress shirt',1);
INSERT INTO shirts_index (name, size) VALUES ('polo shirt','1');

1
2
3
4
5
6
7
8

SELECT * FROM shirts;
输出：
+-------------+------+
| name        | size |
+-------------+------+
| dress shirt | 0    |
| polo shirt  | 1    |
+-------------+------+

插入size为1，取出来数据为0。当然这个按照规则是正确的，但是会让人感觉比较乱，也容易误用。

修改字段带来的问题

新增ENUM成员时需要重建整个表，这里做一些测试来说明情况。

创建一张表：

1
2
3
4
5

CREATE TABLE `enum_tests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` enum('default','success','fail') COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

创建一个批量执行插入100万条数据的存储过程方法：

1
2
3
4
5
6
7
8
9
10
11
12
13

delimiter $$
create procedure proc_insert_datas()
begin
declare num int ;
SET num = 1 ;
while num <= 1000000 do
    INSERT INTO enum_tests (`status`)
VALUES
    (ELT(0.5 + RAND() * 3, 'default','success','fail')) ;
SET num = num + 1 ;
end while;
SET AUTOCOMMIT = 1;
end$$

执行存储过程：

1
2
3
4

call proc_insert_datas()$$

输出：
Query OK, 0 rows affected (2 hours 26 min 14.09 sec)

插入100万条数据居然花了2小时26分钟，我竟也等了那么久。这个方法应该优化一下，但这不是这篇文章的重点。温馨提示，请勿在生产环境做此测试，不然下次祭天的名单里就有你。

测试过程

1. 在ENUM 值列表最后添加一个成员 refunded：

   1 2 3 4 5

   ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('default','success','fail','refunded') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL; 输出： Query OK, 0 rows affected (0.32 sec) Records: 0 Duplicates: 0 Warnings: 0

   结论：在末尾追加ENUM 成员时不需要进行全表扫描。

2. 删除第一个测试添加的成员 refunded：

   1 2 3 4 5

   ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('default','success','fail') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL; 输出： Query OK, 1000000 rows affected (6.01 sec) Records: 1000000 Duplicates: 0 Warnings: 0

   结论：删除一个没有用过的ENUM成员需要进行全表扫描，成本较高。

3. 将 refunded 插入到值列表中间而非末尾：

   1 2 3 4 5

   ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('default','success','refunded','fail') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL; 输出： Query OK, 1000000 rows affected (5.14 sec) Records: 1000000 Duplicates: 0 Warnings: 0

   结论：在原ENUM值列表中间新增值需要进行全表扫描，成本较高。

4. 删除值列表中间的成员：

   1 2 3 4 5

   ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('default','success','fail') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL; 输出： Query OK, 1000000 rows affected (4.63 sec) Records: 1000000 Duplicates: 0 Warnings: 0

   结论：删除值列表中间的成员，需要进行全表扫描，成本较高。

5. 给 status 字段添加索引后再执行上述测试：

   1	ALTER TABLE `enum_tests` ADD INDEX(`status`);

   时间有所增加，是增加了更新索引导致的。

排序问题

ENUM值的排序规则是按创建表结构时指定的顺序，而非字面值的大小。

1
2
3
4
5
6
7
8
9
10

SELECT DISTINCT(status) FROM enum_tests ORDER BY status desc;

输出：
+---------+
| status  |
+---------+
| fail    |
| success |
| default |
+---------+

所以，你通过这样的SQL语句没法按字面值排序。

总结

综上，MySQL 的ENUM类型的优点：

1. 节省存储空间；

2. 可读查询和输出，数字将转换回查询结果中的相应字符串；

3. 如果启用了严格的SQL模式，错误值会导致警告或错误，可在一定程度上过滤掉脏数据。

缺点：

1. 可使用ENUM的索引值，也可以使用字符串，产生等于SQL语句，容易误用；

2. 更改枚举成员需要使用ALTER TABLE语句重建整个表，大部分情况下会进行全表扫描；

3. 迁移到其他RDBMS可能是一个问题，因为ENUM不是SQL标准的，不是所有数据库系统都支持；

4. 枚举值不能是表达式，即使是计算字符串值的表达式也是如此；

5. 无法通过字面量值进行排序。

建议：

1. 非常不建议使用ENUM存数字，如果搭配弱类型语言，那就更有可能出现问题了；

2. 尽量不用这个类型，除非你非常确定你的枚举成员不会改变，或者新增成员也只是在队尾新增，还有你没有转换数据库的需求；

3. 如果字段是字符串，并且长度固定，建议用char类型；

4. 如果是数值型，建议使用tinyint，只占1个字节，比较稳妥，即使字段也比较可控。

5.