MySQL ENUM 枚举类型的使用及局限

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

ENUM 枚举型介绍

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

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

  • 节省存储空间,MySQL ENUM使用数字索引(1,2,3,…)来表示字符串值。
  • 可读查询和输出,数字将转换回查询结果中的相应字符串。

创建和使用ENUM列

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

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

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

插入数据:

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

更新数据:

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

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

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

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:

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

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

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类型的一个坑。

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

INSERT INTO shirts_index (name, size) VALUES ('dress shirt',1);
INSERT INTO shirts_index (name, size) VALUES ('polo shirt','1');
SELECT * FROM shirts;
输出:
+-------------+------+
| name        | size |
+-------------+------+
| dress shirt | 0    |
| polo shirt  | 1    |
+-------------+------+

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

修改字段带来的问题

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

创建一张表:

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

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$$

执行存储过程:

call proc_insert_datas()$$

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

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

测试过程

  1. ENUM 值列表最后添加一个成员 refunded
   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 成员时不需要进行全表扫描。

  1. 删除第一个测试添加的成员 refunded
   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成员需要进行全表扫描,成本较高。

  1. refunded 插入到值列表中间而非末尾:
   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值列表中间新增值需要进行全表扫描,成本较高。

  1. 删除值列表中间的成员:
   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

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

  1. status 字段添加索引后再执行上述测试:
   ALTER TABLE `enum_tests` ADD INDEX(`status`);

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

排序问题

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

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个字节,比较稳妥,即使字段也比较可控。