mysql+add+enum_mysql_enum_枚举类型的使用及局限

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

ENUM 枚举型介绍

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

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

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

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

创建和使用ENUM列

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

1234

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

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

插入数据：

12

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

更新数据：

12345

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

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

12345678

INSERT INTO shirts (name, size) VALUES ('dress','big');输出：Data truncated for column 'size' at row 1UPDATE 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：

123

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

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

12345678

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')对应的索引表如下：

值

索引

NULL

NULL

‘’

0

x-small

1

‘small’

2

‘medium’

3

‘large’

4

‘x-large’

5

ENUM类型的缺点

等义带来的一个坑

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

12

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')，这时候插入数据：

12

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

12345678

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

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

修改字段带来的问题

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

创建一张表：

12345

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万条数据的存储过程方法：

12345678910111213

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

执行存储过程：

1234

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

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

测试过程

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

12345

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 成员时不需要进行全表扫描。

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

12345

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成员需要进行全表扫描，成本较高。

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

12345

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值列表中间新增值需要进行全表扫描，成本较高。

删除值列表中间的成员：

12345

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

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

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

1

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

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

排序问题

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

12345678910

SELECT DISTINCT(status) FROM enum_tests ORDER BY status desc;输出：+---------+| status |+---------+| fail || success || default |+---------+

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

总结

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

节省存储空间；

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

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

缺点：

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

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

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

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

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

建议：

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

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

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

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