ENUM 和 SET：方便但有代价

个人主页 for_ever_love__ 欢迎各位大佬莅临其他栏目: 大模型开发从0到1 其他栏目: iOS项目总结大全 其他栏目: 我想学python了 其他栏目: iOS UI 文章目录ENUM 和 SET方便但有代价一、ENUM 怎么用二、ENUM 的优点2.1 省空间2.2 数据校验2.3 可读性好三、ENUM 的坑3.1 排序按内部序号不是字符串3.2 修改取值要 ALTER TABLE3.3 空字符串的坑3.4 NULL vs 空字符串3.5 不能和其他表的外键/JOIN 友好共处3.6 迁移麻烦四、SET 是什么SET 的问题五、替代方案方案 1TINYINT 应用层常量方案 2VARCHAR CHECK 约束8.0.16方案 3字典表 外键六、怎么选七、什么时候 ENUM 确实合适八、性能对比九、迁移从 ENUM 改成 TINYINT十、CHECK 约束8.0.16小结ENUM 和 SET方便但有代价状态字段用 ENUM 还是 TINYINT这篇文章讲清楚 ENUM 的优缺点和替代方案。一、ENUM 怎么用CREATETABLEorders(idINTPRIMARYKEY,statusENUM(pending,paid,shipped,done,canceled)NOTNULL);INSERTINTOordersVALUES(1,paid);INSERTINTOordersVALUES(2,unknown);-- 严格模式下报错内部存的是整数1, 2, 3…只占1~2 字节。二、ENUM 的优点2.1 省空间statusENUM(...)-- 1 字节statusVARCHAR(20)-- 实际长度 1 字节utf8mb4 下最多 81 字节statusTINYINT-- 1 字节2.2 数据校验无效值插不进去严格模式下。数据库层保证了取值合法。2.3 可读性好SELECT*FROMordersWHEREstatuspaid;-- 比 WHERE status 2 直观三、ENUM 的坑3.1 排序按内部序号不是字符串INSERTINTOtVALUES(b),(a),(c);-- ENUM 定义ENUM(a,b,c)SELECTvFROMtORDERBYv;-- 结果a, b, c ← 按序号排-- 如果定义是 ENUM(c,b,a)SELECTvFROMtORDERBYv;-- 结果c, b, a ← 序号变了这是最大的坑——排序结果依赖定义顺序改定义的顺序会改变已有查询的结果。字符串列会按字母序排SELECTvFROMtORDERBYv;-- VARCHARa, b, c固定如果业务依赖排序用 CASTORDERBYCAST(vASCHAR)但这样用不了索引。3.2 修改取值要 ALTER TABLE-- 加一个新状态ALTERTABLEordersMODIFYstatusENUM(pending,paid,shipped,done,canceled,refunding);要重建表大表很慢。虽然 appending 到末尾时 MySQL 可能不用重建取决于是否改变存储大小但在中间插入、删除、改顺序都会重建。对比 VARCHAR/TINYINT-- 加一个状态不用改表结构INSERTINTOorder_status_dictVALUES(6,refunding);-- 或者应用层加个常量业务变化时需要加新状态ENUM 的代价不小。3.3 空字符串的坑INSERTINTOorders(status)VALUES(xxx);-- 非严格模式插入空字符串 内部序号为 0SELECTstatusFROMordersWHEREstatus;-- 能查到SELECTstatus0FROMorders;-- 0严格模式下会报错但非严格模式会静默写入。3.4 NULL vs 空字符串statusENUM(a,b)NULL-- 可以有 NULLstatusENUM(a,b)NOTNULL-- 可以有序号 0空字符串两者混淆容易出 bug。3.5 不能和其他表的外键/JOIN 友好共处需要字典表时CREATETABLEstatus_dict(codeVARCHAR(20)PRIMARYKEY,nameVARCHAR(50),sort_orderINT,colorVARCHAR(20));ENUM 没法直接 JOIN 出中文名、颜色这些附加信息。3.6 迁移麻烦从 MySQL 迁到其他数据库时ENUM 的兼容性差。四、SET 是什么SET 是可以多选的 ENUMCREATETABLEusers(idINTPRIMARYKEY,permsSET(read,write,delete,admin));INSERTINTOusersVALUES(1,read,write);INSERTINTOusersVALUES(2,read,delete,admin);内部用位运算存最多 64 个成员。-- 查询有 write 权限的SELECT*FROMusersWHEREFIND_IN_SET(write,perms);SELECT*FROMusersWHEREperms2;-- write 是第 2 位-- 加权限UPDATEusersSETpermsperms|2WHEREid1;SET 的问题同样的坑改定义要 ALTER加上最多 64 个成员查询要用FIND_IN_SET或位运算可读性差FIND_IN_SET用不了索引权限这种场景通常需要更灵活的模型SET 用得更少了权限一般用关联表或多对多表。五、替代方案方案 1TINYINT 应用层常量CREATETABLEorders(idINTPRIMARYKEY,statusTINYINTNOTNULLCOMMENT1待支付 2已支付 3已发货 4完成 5取消);应用层classOrderStatus:PENDING1PAID2SHIPPED3DONE4CANCELED5NAMES{1:待支付,2:已支付,3:已发货,4:完成,5:取消}优点加状态不用改表排序按数值可控跨库兼容好缺点SQL 里是数字可读性差要依赖注释和文档方案 2VARCHAR CHECK 约束8.0.16CREATETABLEorders(idINTPRIMARYKEY,statusVARCHAR(20)NOTNULL,CONSTRAINTchk_statusCHECK(statusIN(pending,paid,shipped,done,canceled)));优点可读性好数据库层有校验加新值只改约束也要 ALTER但不重建数据实际上改 CHECK 约束也要 ALTER TABLE但通常比改 ENUM 轻。方案 3字典表 外键CREATETABLEorder_status(codeVARCHAR(20)PRIMARYKEY,nameVARCHAR(50)NOTNULL,sort_orderINT,colorVARCHAR(20));CREATETABLEorders(idINTPRIMARYKEY,statusVARCHAR(20)NOTNULL,KEYidx_status(status)-- 可以不加物理外键应用层保证);优点可以存中文名、排序、颜色等元信息加状态插入一条记录即可可以做权限、流程配置缺点要 JOIN 才能拿到名称或者应用层缓存字典这是最灵活的方案适合状态含义丰富、会变化的场景。六、怎么选场景推荐取值固定不变性别、是否、星期ENUM 可以状态可能增加TINYINT / VARCHAR 字典表需要排序且顺序有意义TINYINT序号即顺序需要存中文名等元信息字典表需要 JOIN 出附加信息字典表跨数据库兼容VARCHAR / TINYINT权限/多选关联表不用 SET一句话取值确定不变、不需要附加信息 → ENUM 挺好状态会变、要存元信息 → 用字典表或 TINYINT七、什么时候 ENUM 确实合适-- 性别genderENUM(unknown,male,female)-- 是否启用其实 TINYINT(1) 更好enabledENUM(Y,N)-- 星期weekdayENUM(Mon,Tue,Wed,Thu,Fri,Sat,Sun)-- 支付渠道如果确定就这几种channelENUM(alipay,wechat,card)这些场景取值几乎不会变ENUM 的简洁性才有价值。但说实话几乎不会变这个判断经常出错——业务一年后加了新渠道你就要改表了。八、性能对比-- 测试表CREATETABLEa(sENUM(p,paid,done,cancel)NOTNULL,KEYidx(s));CREATETABLEb(sTINYINTNOTNULL,KEYidx(s));CREATETABLEc(sVARCHAR(20)NOTNULL,KEYidx(s));各 500 万行类型表大小等值查询索引大小ENUM最小最快最小TINYINT一样小一样快一样小VARCHAR(20)大 30%略慢大 3 倍ENUM 和 TINYINT 性能完全一样都是整数存储。VARCHAR 略差但差距通常可以接受。九、迁移从 ENUM 改成 TINYINT-- 1. 加新列ALTERTABLEordersADDCOLUMNstatus_newTINYINT;-- 2. 迁移数据UPDATEordersSETstatus_newCASEstatusWHENpendingTHEN1WHENpaidTHEN2WHENshippedTHEN3WHENdoneTHEN4WHENcanceledTHEN5END;-- 3. 删旧列重命名ALTERTABLEordersDROPCOLUMNstatus;ALTERTABLEorders CHANGE status_newstatusTINYINTNOTNULL;-- 4. 加索引ALTERTABLEordersADDKEYidx_status(status);大表用pt-online-schema-change或 gh-ost 做在线变更。十、CHECK 约束8.0.16替代 ENUM 的一个好选择CREATETABLEorders(idINTPRIMARYKEY,statusVARCHAR(20)NOTNULL,amountDECIMAL(10,2),CONSTRAINTchk_statusCHECK(statusIN(pending,paid,done)),CONSTRAINTchk_amountCHECK(amount0));INSERTINTOordersVALUES(1,xxx,10);-- ERROR 3819: Check constraint chk_status is violated优点可读性好SQL 里是字符串数据库层校验修改约束不用重建表通常缺点8.0.16 之前不支持索引不如整数高效迁移到其他库时 CHECK 的兼容性查看约束SELECT*FROMinformation_schema.table_constraintsWHEREconstraint_typeCHECK;小结类型优点缺点适用ENUM省空间、可读、有校验改定义要重建表、排序依赖定义顺序取值真不变SET多选压缩存储同样的坑 查询麻烦基本不用TINYINT快、小、易扩展SQL 里可读性差状态会变VARCHAR CHECK可读、有校验空间略大8.0.16字典表最灵活、可存元信息要 JOIN 或缓存复杂状态我的建议状态字段默认不用 ENUM——业务总会变用 TINYINT或 SMALLINT 应用层常量加状态不用改表需要更多元信息用字典表8.0.16 可以考虑 VARCHAR CHECK兼顾可读性和校验ENUM 不是不能用但要清楚它带来的约束排序依赖定义顺序、加取值要改表。这两个特性在业务演进时很容易成为负担。