实例测试MySQL的enum类型
在开发项目时通常会遇到一些状态字段,例如订单的状态有待支付、已支付、已关闭、已退款等,我以前做的项目都是把这些状态用数字存在数据库中,然后在php代码中用常量来维护一份映射表,例如:
constSTATUS_PENDING=0; constSTATUS_PAID=1; constSTATUS_CLOSED=2; constSTATUS_REFUNDED=3;
但是在实际使用过程中发现并不是那么好用,由于各种原因(追查bug、临时的统计需求等)我们常常需要登录到mysql服务器里手动执行一些sql查询,由于许多表都有状态字段,写sql时必须对照的php代码里的映射关系来写,一不小心还有可能将不同表的状态数字弄混导致大问题。
于是我在新项目中准备使用mysql的enum类型来存储各种状态,在使用过程中发现如果在Laravel的migration文件中对使用了enum类型的表做变更(即使是变更非enum类型的字段)都会报错
[Doctrine\DBAL\DBALException] Unknowndatabasetypeenumrequested,Doctrine\DBAL\Platforms\MySQL57Platformmaynotsupportit.
搜索了一下,发现是doctrine不支持mysql的enum,该文中列举了enum的3个缺点:
新增enum值的时候需要重建整个表,当数据量大的时候可能需要耗费数小时。
enum值的排序规则是按创建表结构时指定的顺序,而非字面值的大小。
依赖mysql对enum值的校验并不是非常必要,在默认配置下插入非法值最终会变成空值。
根据新项目的实际情况,不太可能出现需要对状态字段做排序的需求,即使有我们可以在设计表结构的时候就定好顺序,因此缺点2可以忽略;而缺点3则可以通过代码规范、插入/更新前校验等方式来规避;至于缺点1,我们需要做一些测试。
测试准备#
首先创建一个表:
CREATETABLE`enum_tests`( `id`int(10)unsignedNOTNULLAUTO_INCREMENT, `status`enum('pending','success','closed')COLLATEutf8mb4_unicode_ciNOTNULL, PRIMARYKEY(`id`) )ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=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
ALTERTABLE`enum_tests`CHANGE`status``status`ENUM('pending','success','closed','refunded')CHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ciNOTNULL;
输出:
QueryOK,0rowsaffected(0.04sec) Records:0Duplicates:0Warnings:0
结论:在末尾追加enum值时几乎没有成本。
测试2:#
删除刚刚添加的值refunded
ALTERTABLE`enum_tests`CHANGE`status``status`ENUM('pending','success','closed')CHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ciNOTNULL;
输出:
QueryOK,1000000rowsaffected(5.93sec) Records:1000000Duplicates:0Warnings:0
结论:删除一个没有用过的enum值仍需全表扫描,成本较高,但还在可接受范围内。
测试3:#
将refunded插入到值列表中间而非末尾
ALTERTABLE`enum_tests`CHANGE`status``status`ENUM('pending','success','refunded','closed')CHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ciNOTNULL;
输出:
QueryOK,1000000rowsaffected(6.00sec) Records:1000000Duplicates:0Warnings:0
结论:在原enum值列表中间新增值需要全表扫描并更新,成本较高。
测试4:#
删除值列表中间的值
ALTERTABLE`enum_tests`CHANGE`status``status`ENUM('pending','success','closed')CHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ciNOTNULL;
输出:
QueryOK,1000000rowsaffected(4.23sec) Records:1000000Duplicates:0Warnings:0
结论:需全表扫描,成本较高。
测试5:#
给status字段添加索引后再执行上述测试
ALTERTABLE`enum_tests`ADDINDEX(`status`);
发现测试2-4的耗时反而有所增加,应该是同时需要更新索引导致的。
结语:#
对于我的新项目来说只会出现新增enum值的情况,即使将来有个别状态废弃不用也不需要去调整enum的值列表,因此决定在项目中引入enum类型作为存储状态的数据类型。