关于 MySQL enum 类型的一些测试

背景：

在开发项目时通常会遇到一些状态字段，例如订单的状态有 待支付、已支付、已关闭、已退款 等，我以前做的项目都是把这些状态用数字存在数据库中，然后在 php 代码中用常量来维护一份映射表，例如：

const STATUS_PENDING = 0;
const STATUS_PAID = 1;
const STATUS_CLOSED = 2;
const STATUS_REFUNDED = 3;

但是在实际使用过程中发现并不是那么好用，由于各种原因（追查 bug、临时的统计需求等）我们常常需要登录到 mysql 服务器里手动执行一些 sql 查询，由于许多表都有状态字段，写 sql 时必须对照的 php 代码里的映射关系来写，一不小心还有可能将不同表的状态数字弄混导致大问题。

于是我在新项目中准备使用 mysql 的 enum 类型来存储各种状态，在使用过程中发现如果在 Laravel 的 migration 文件中对使用了 enum 类型的表做变更（即使是变更非 enum 类型的字段）都会报错

[Doctrine\DBAL\DBALException]
  Unknown database type enum requested, Doctrine\DBAL\Platforms\MySQL57Platform may not support it.

搜索了一下，发现是 doctrine 不支持 mysql 的 enum，该文中列举了 enum 的 3 个缺点：

新增 enum 值的时候需要重建整个表，当数据量大的时候可能需要耗费数小时。
enum 值的排序规则是按创建表结构时指定的顺序，而非字面值的大小。
依赖 mysql 对 enum 值的校验并不是非常必要，在默认配置下插入非法值最终会变成空值。

根据新项目的实际情况，不太可能出现需要对状态字段做排序的需求，即使有我们可以在设计表结构的时候就定好顺序，因此缺点 2 可以忽略；而缺点 3 则可以通过代码规范、插入/更新前校验等方式来规避；至于缺点 1，我们需要做一些测试。

测试准备

首先创建一个表：

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

然后插入 100W 条数据：

$count = 1000000;
$bulk  = 1000;
$data  = [];
foreach (['pending', 'success', 'closed'] as $status) {
    $data[$status] = [];
    for ($i = 0; $i < $bulk; $i++) {
        $data[$status][] = ['status' => $status];
    }
}

for ($i = 0; $i < $count; $i += $bulk) {
    $status = array_random(['pending', 'success', 'closed']);
    EnumTest::insert($data[$status]);
}

测试过程

测试1

在 enum 值列表最后添加一个值 refunded

ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('pending','success','closed','refunded') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

输出：

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

结论：在末尾追加 enum 值时几乎没有成本。

测试 2：

删除刚刚添加的值 refunded

ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('pending','success','closed') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

输出：

Query OK, 1000000 rows affected (5.93 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

结论：删除一个没有用过的 enum 值仍需全表扫描，成本较高，但还在可接受范围内。

测试 3：

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

ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('pending','success','refunded', 'closed') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

输出：

Query OK, 1000000 rows affected (6.00 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

结论：在原 enum 值列表中间新增值需要全表扫描并更新，成本较高。

测试 4：

删除值列表中间的值

ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('pending','success','closed') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

输出：

Query OK, 1000000 rows affected (4.23 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

结论：需全表扫描，成本较高。

测试 5：

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

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

发现测试 2-4 的耗时反而有所增加，应该是同时需要更新索引导致的。

结语：

对于我的新项目来说只会出现新增 enum 值的情况，即使将来有个别状态废弃不用也不需要去调整 enum 的值列表，因此决定在项目中引入 enum 类型作为存储状态的数据类型。

本作品采用《CC 协议》，转载必须注明作者和本文链接

Laravel 教程 - 电商实战 Laravel 教程 - 电商进阶

本帖由 Summer 于 7年前加精

leo

管理员 4.7k 声望

Engineering Director of Backend @ RightCapital

不会写前端的后端不是好运维

40 人点赞

从零开发一个电商项目，功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等

你将学到如 RESTFul 设计风格、PostMan 的使用、OAuth 流程，JWT 概念及使用和 API 开发相关的进阶知识。

讨论数量: 29

nickfan

88 声望 / SA @ thebizark.com

migration的enum类型有一种workaround方法解决：
在你的Migration的类文件中初始化时将此类型注册为string

    public function __construct()
    {
        DB::getDoctrineSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');
    }

的方式解决。
虽然个人觉得还是用tinyint的类型替代会更好，数字的取值含义，我一般建议在column的comment注释中分别说明：

order_status ->comment='单据状态#0:draft/1:submit/2:approved';

其中数字后面的是这个值的代码中的字面标识量，取值的含义比如draft草稿，submit提交，approved审核通过的多语言翻译可以在i18n中定义，
而相关的使用可以在Model/Repository中定义

const INDEX_ORDER_STATUS_DRAFT = 0;
const INDEX_ORDER_STATUS_SUBMIT = 1;
const INDEX_ORDER_STATUS_APPROVED = 2;

const LABEL_ORDER_STATUS_DRAFT = 'draft';
const LABEL_ORDER_STATUS_SUBMIT = 'submit';
const LABEL_ORDER_STATUS_APPROVED = 'approved';

// 用于根据index数字查找字面标识>显示输出翻译
protected static $indexLabelMapOrderStatus = [
    self::INDEX_ORDER_STATUS_DRAFT=>self::LABEL_ORDER_STATUS_DRAFT,
    self::INDEX_ORDER_STATUS_SUBMIT=>self::LABEL_ORDER_STATUS_SUBMIT,
    self::INDEX_ORDER_STATUS_APPROVED=>self::LABEL_ORDER_STATUS_APPROVED,
];

上面的这段代码中【字段名】-【字段字面标识】-【枚举取值】的相关const定义和字典定义其实都可以通过数据库结构定义信息自动代码生成

7年前评论

leo

管理员 4.7k 声望 / Engineering Director of Backend @ RightCapital

@nickfan 你认为 tinyint 优于 enum 的点是？

7年前评论

nickfan

88 声望 / SA @ thebizark.com

@leo
个人认为
从数据可读性、索引维护难度、数据库迁移(移植)问题、索引效率、人员培训多个方面综合比较而言enum枚举字段的优势和使用tinyint相比并不占优。

另外楼主的cas client拖更已然成梗了~~:)

7年前评论

leo

管理员 4.7k 声望 / Engineering Director of Backend @ RightCapital

@nickfan

数据可读性明显是 enum 优于 tinyint。
数据库移植性是一个伪命题，真没听说过有多少项目能活到需要更换数据库的情况，即使真的需要换数据库，enum 也不会是核心问题。
人员培训……如果连 enum 都搞不定，这人趁早转行吧。
索引效率，enum 在存储时本质也是一个数字，空间占用 1 ~ 2 字节，两者相差应该不大。

7年前评论

nickfan

88 声望 / SA @ thebizark.com

@leo 这个你自己测试的索引维护的问题是故意漏掉不提么？嘿嘿~~

7年前评论

leo

管理员 4.7k 声望 / Engineering Director of Backend @ RightCapital

@nickfan 是漏了。。。我一会儿再测测看

7年前评论

johnlui

326 声望

我不用 enum 主要考虑的是跨数据库移植问题。

7年前评论

GanymedeNil

课程读者 94 声望 / 研发工程师 @ 好未来

跨数据库移植+1

7年前评论

daryl

9 声望

《高性能 MySQL》建议，小心使用 ENUM。

7年前评论

leo

管理员 4.7k 声望 / Engineering Director of Backend @ RightCapital

@daryl 小心不等于不用

7年前评论

leo

管理员 4.7k 声望 / Engineering Director of Backend @ RightCapital

@johnlui
@canyuexiang
很好奇你们为什么会在项目设计之初就考虑数据库迁移问题，我认为使用一个数据库就应该发挥它的全部优势，如果为了所谓的方便数据库迁移，那么即使迁移到了新数据库，你也只能用最基本的 sql，而不能用“非标准”的数据类型比如 json 、geo 等，因为你还需要为以后的数据库迁移考虑，这些类型迁移起来可比 enum 复杂多了。

7年前评论

leo

管理员 4.7k 声望 / Engineering Director of Backend @ RightCapital

@nickfan 索引性能测试结果：

字段为 tinyint 类型表结构：

CREATE TABLE `enum_tests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `status` (`status`)
);

字段为 enum 类型表结构：

CREATE TABLE `enum_tests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` enum('pending','success','closed') COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `enum_tests_status_index` (`status`)
)

写入 100W 条数据测试结果： tinyint 类型 27 秒，enum 类型 28秒。

tinyint 类型查询性能：

mysql> explain SELECT * FROM `enum_tests` where status=0;
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key    | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | enum_tests | NULL       | ref  | status        | status | 1       | const | 499484 |   100.00 | Using index |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------------+

enum 类型查询性能：

mysql> explain SELECT * FROM `enum_tests` where status='pending';
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key    | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | enum_tests | NULL       | ref  | status        | status | 1       | const | 499484 |   100.00 | Using index |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------------+

在我看来没有太大区别

7年前评论

大张

269 声望

@leo 认可这个观点, 很多时候这样的做法都是在过度设计. 就像Repository又套了一层接口一样, 个人认为Repository只要有实现即可, 没必要再套接口, 加重设计.

7年前评论

laravue

39 声望

问一下楼主，mysql性能测试用的什么软件？

7年前评论

nickfan

88 声望 / SA @ thebizark.com

@leo 我的观点是可维护问题，enum本质上和tinyint没啥区别，字面量更好解读数据而已，解读数据的述求通过comment注释说明也可以解决，但维护层面就像你文章头上测试的

在原 enum 值列表中间新增值需要全表扫描并更新，成本较高。

删除一个没有用过的 enum 值仍需全表扫描，成本较高，但还在可接受范围内。

删除值列表中间的值需全表扫描，成本较高

可读性既然可以通过注释曲线救国，
而且维护导致的性能成本的升高会有一个非线性的增长（修改enum值列表非追加时）虽然是少数场景
个人的观点是不如tinyint+注释简单粗暴点。

7年前评论

树袋熊

课程读者 6 声望

今天遇到了同样的报错信息，，请问怎么解决不支持enum类型字段的问题的？

6年前评论

课程读者 178 声望

能使用 tinyint 不建议使用 enum

enum 缺点

1、更改枚举成员需要使用 Alter table 语句重建整个表，这在资源和时间方面是昂贵的
2、获取完整的枚举列表很复杂，因为需要访问information_schema数据库
3、迁移到其它 DB 可能是一个问题，enum不是 SQL 标准
4、enum的值必须是一个 string ，但是如果插入的值是 int 类型且值与 enum key 值对应，则也能更新与插
     入。enum === [1=>'string',2=>'string'] 
5、enum 可扩展性差

6年前评论

leo

管理员 4.7k 声望 / Engineering Director of Backend @ RightCapital

@FreeMason 仔细看我文章，这些缺点在我看来并不是问题，完全可以从代码规范层面规避掉

6年前评论

课程读者 178 声望

@nickfan 如果插入的值是 int 类型且值与 enum key 值对应，不管 enum 指定的值是数字还是 string，则也能更新与插
入。enum === [1=>'string',2=>'string']

6年前评论

leo

管理员 4.7k 声望 / Engineering Director of Backend @ RightCapital

@FreeMason 都已经知道是 enum 类型的字段了，还往里面传数字，谁要这么干直接收拾东西走人，不遵守规范的人留不得

6年前评论

课程读者 178 声望

@leo 你文章上所说解决 enum 缺点也看到了，我也说了，是个人建议能使用 tinyint 不使用 enum，并不是说使用 enum 在代码层去做强类型验证 enum 字段值不对不好，我虽然使用 tinyint 来代替 enum，但是我在代码层也会做安全验证。

6年前评论

leo

管理员 4.7k 声望 / Engineering Director of Backend @ RightCapital

@FreeMason tinyint 的缺点我也说了，在手写 SQL 的情况下容易出问题，而出现手写 SQL 的情况往往是紧急修复线上问题，所以我认为用 enum 要优于 tinyint。

6年前评论

课程读者 178 声望

@leo 对的，这跟 char 与 varchar 有点类似。各有长各有短。

6年前评论

LFI

3 声望

enum设计考虑：

1.倾向于有限类型字段的枚举标识，如性别，订单等等。

2.不需要考虑扩展性，基本有限的类型是极少情况额外增加枚举类型的，如果考虑扩展性提供方便的列表获取的话何不直接创建一个表来管理这些类型。

3.列表的获取完全可以通过在对应的Model提供一个方法来维护这些列表

4.DB迁移的情况那是更少完全多虑, 而且对于这种完全有方案解决的。

5.在开发上具备很友好的可读性，即使新成员加入都很快理解，在代码层面都不需要特别的提供注释。

6年前评论

huijiewei

18 声望

@翁航 Repository 用接口主要是为了依赖注入，并非过度设计

6年前评论

skyLee

见习助教 75 声望 / 研发 @ XT

所谓，人的习惯真难改！！！

6年前评论

苏亦坤

课程读者 95 声望

@leo 6 年啦，现在楼主对于 enum 以及 tinyint 和 varchar 是什么态度呀，更推荐那一种呀。

我在搜索的时候发现这篇文章，我本来觉得 enum 优于 tinyint（可读性）和 varchar（限定范围），但是看了一下评论，感觉好像有点动摇了，不知道后续楼主在工作中哪种用的更多哇

11个月前评论

leo （楼主）

仍然选 enum，我司现在应该有几百个 enum 字段了，和 Laravel ORM enum 配合用爽歪歪

wanzhende

4 声望

个人认为enum还是很好的,工具是辅助我们的,enum天然具有语义,且底层使用int映射,对于性能,存储还易读性都有很好的平衡

2个月前评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容，与人为善，比聪明更重要！

帮助

关于 MySQL enum 类型的一些测试

背景：

测试准备

测试过程

测试1

测试 2：

测试 3：

测试 4：

测试 5：

结语：

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

关于 MySQL enum 类型的一些测试

背景：

测试准备

测试过程

测试1

测试 2：

测试 3：

测试 4：

测试 5：

结语：

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

请登录