2011 年 3 月 2 日 周三
MySQL 的 枚举 (ENUM) 类型 是程序员群体中的一个讨论热点乍一看, 我们可以通过枚举类型, 很好地将记录值限制在允许范围内一个典型的例子是, 一个具有字段名称为大陆板块的数据表: 每一个国家位于一个大陆板块, 而这些大陆板块不太可能经常变化当然, 或许一天北美板块会与亚洲板块碰撞形成北美亚, 但即便你的数据库能够延续使用到那个时候, 起码你也不需要研讨怎么去重构你的数据表, 那将是当时的开发者要做的工作
言归正传如果, 使用 ENUM 是唯一 一个, 能够代表某一个国家属于哪个大陆板块的选择, 那我们大可进行下一步, 去争辩诸如 NoSQL 的优劣 Git 和 SVN 孰强孰弱你喜欢的框架有哪些缺点这些其他的问题但这里有一个普遍适用于实现枚举的最佳实践:
维基百科 是这样描述关系表的:
这是一种将可知的枚举数据分离出来的表例如, 一个关系型数据库的仓库数据, 仓库里面的物件有可能会有一个状态的字段记录已经声明的值, 例如: 已售, 预定, 售罄在极简的数据库设计当中, 这些值都会在独立的关系表状态中存储, 以此满足范式(database normalization)
所以, 关系表也可以满足枚举的实现下面就来看看, ENUM 的八宗罪到底是什么:
1. 数据被错误对待
男女; 先生夫人小姐; 非洲亚洲, 等等这些人们使用作为 ENUM 类型字段的短词称为数据当你使用一个 ENUM 类型字段, 技术上看, 是你将数据抽离出来 (对应到实际数据表时), 放到一个独立的地位(一种数据库的元数据, 具有精确定义字段) 这不同与约束数据类型, 如我们通常的做法: 数值型字段只能存储整型数据, 或者日期型字段不能为空这些都没有问题, 而且还十分重要使用 ENUM 类型字段时, 我们实际上是保存部分数据 去作为 这个数据模型的一个特征信息简而言之, ENUM 类型字段破坏了范式要求这也许看起来十分学院派或迂腐陈旧, 但这正是以下各种罪行的源头
2. 更改 ENUM 类型字段, 代价很昂贵
永恒不变的是, 每次你创建 ENUM 类型字段的时候都说: 这个字段不可能变的人类普遍欠缺顾全大局的能力, 预测上更是糟糕, 其如研发部的新产品线贵司新的航运方案北美板块碰撞亚洲板块
使用 ALTER TABLE 去修改整个数据表的 ENUM 类型字段, 是十分耗费资源的如果将
ENUM('red', 'blue', 'black')
改为
ENUM('red', 'blue', 'white')
, MySQL 需要重构整个数据表, 并且检索 所有数据去检查'black'这个无效值 MySQL 是真的蠢, 它确实会在你每次增加一个新的 ENUM 值时都这么做的!(传言未来会处理 ENUM 类型字段的效率问题, 但我对其受重视程度深表怀疑)
全表重构在小型数据表中可能没有那么痛苦, 但在海量数据的情况下可能会导致资源被锁死很长很长一段时间如果你使用关系表去替代 ENUM 类型字段, 改变枚举集合只不过是使用 INSERTUPDATE 和 DELETE, 对比来看真是滑稽
很重要的一点, 当更改 ENUM 类型字段的枚举集合时, MySQL 会转换任意已有但不存在于新的枚举集合中的记录值为''(空的字符串)使用关系表, 在更改和删除枚举集合时会灵活很多(下面会提到)
3. 几乎无法给关联数据添加额外的属性
至今都没有一个可以更加明智地改变 ENUM 类型字段的方法, 这也是我们的常态在我们的国家大陆板块例子中, 更改国土面积会出现什么情况? 我们没有预料到这个属性, 但也要既来之则安之使用关系表设计, 我们可以轻易地拓展大陆板块这个数据表, 各种方式为其增加我们想要的数据和字段 ENUM? 快别说了
另一种极妙的灵活性体现在关系表的拓展便捷性上一个简单的标记位字段即可表示这个枚举值是否可用所以, 当你的公司不打算销售黑色的装饰品了, 你只需在黑色所对应的 is_discontinued 字段中做个标记即可而且你依然可以查询到已售的颜色(译者: 指的是, ENUM 的修改会导致原有, 而现在已经没有的值变为空字符串, 数据失去了部分特征), 同时你那些黑色装饰品的订单依然可统可计哦! ENUM, 你要不要试试?
4. 获取 ENUM 全部可能值, 很麻烦
一个很常见的需求是, 将数据库中存在的数据显示在可拖拽列表中, 例如:
选择颜色:
红 蓝 黑
如果这些数值存储在一个名为 colors 的数据表里, 你所要做的仅仅是:
SELECT * FROM colors
, 这样即可动态地令数据地显示在可拖拽列表中你可以添加或者改变 color 关系表中的颜色, 并且, 你那酷炫订单的颜色可选项会自动更新, 真了不起 (译: 此处所举例子, 应等同于: 通过后台管理, 可以限定前端用户某类型数据的可选项这样的功能)
回到 ENUM 上: 你要如何获取全部的枚举值? 你当然可以使用 ENUM 值搭配 DISTINCT 去查询(译: 即是查询 ENUM 值互相不相同的数据, 等于利用 DISTINCT 的唯一性去查询 ENUM), 但这样也只会返回确实使用过, 并存在于数据表 ENUM 字段可选值中的 ENUM 值, 而不是所有可能的值你也可以查询 INFORMATION_SCHEMA 然后通过代码解析返回的数据, 去找到你想要的 ENUM 的所有值, 但这完全是多此一举事实上, 我依然没有发现, 有任何兼顾了优雅与原生的 SQL 方式, 可以获取 ENUM 类型字段的所有值
5. ENUM 类型字段所提供的优化有限
通常使用 ENUM 的正当理由, 不外乎优化二字, 譬如, 性能提升, 简化模型与高可读性
那我们从性能上看你可以在未优化的数据库中做很多匪夷所思而夸张的事, 但是大多情况是, 在数据达到一定规模前, 都不会出现影响性能的情况, 并且通常我们的产品远未达到那个尺度规模有一点需要注意的是, 因为数据库开发者们都热衷于令自己的设计可以达到完备的范式, 并且只会在遇到性能问题时才会考虑反范式如果你担心使用关系表会导致变慢, 可以在同一基准下测试不同方式下的表现, 再进行考虑切勿先入为主地认为关联查询会成为瓶颈, 可能有时并非如此(可参照 evidence to support that ENUM isn't always appreciably faster than alternatives.)
另一个关于 ENUM 优化方式的说法是, ENUM 可以有效减少数据库中的数据表外键不可置否, 使用外键相当于是将很多不同的盒子以线相连, 而且在大型系统中, 范式设计已可降低对人类的理解能力界限复杂型查询的要求但是, 我们为什么会设计模型, 为什么要将模型抽象化以便我们能够理解它去试试做一个新数据模型图或者 ER 图, 并且忽略一些小细节和关系表有时候使用 ENUM 确实如看上去那般简单, 但事实上你在心里需要想着一个隐式的关系表, 所以并没有看上去那般简单
6. ENUM 值在其他数据表中不可直接复用
当你 (在数据表中) 创建了一个带值的 ENUM 字段, 在其他数据表中无法直接复用这个 ENUM 而当有了关系表, 相同应用形式下, 可以在其他多个数据表中复用当改变关系表中的一个数据, 其他多个数据表也会得到响应
ENUM 类型字段的分离, 将使你能在多个数据表中复用相同的 ENUM 值(需要保持一致性)
7. ENUM 类型字段有显然陷阱
假设你设置了一个字段 color
ENUM('blue', 'black', 'red')
, 这时你想 INSERT 一行数据, 但 color 字段是'purple', MySQL 会将不合法的值变为 '' (空字符串) 处理上没问题, 但如果我们使用的是带外键的关系表, 那么我们的数据能因健壮性而更加可靠
同样, MySQL 会为 ENUM 值关联枚举索引, 并且在使用中会错误地调用到索引而不是 ENUM 值, 反之亦然
想象一下:
- CREATE TABLE test (foobar ENUM('0', '1', '2'));
- mysql> INSERT INTO test VALUES ('1'), (1);
- Query OK, 2 rows affected (0.00 sec)
- Records: 2 Duplicates: 0 Warnings: 0
- mysql> SELECT * FROM test;
- +--------+
- | foobar |
- +--------+
- | 1 |
- | 0 |
- +--------+
- 2 rows in set (0.00 sec)
我们插入了 '1' (字符串), 并且不小心插入了 1 (没有引号, 数值型) MySQL 会将我们地数值型数据当作是枚举索引去处理(并没有错, 但会令人混淆), 根据索引可知, ENUM 字段的第一个值为 0 (译: 枚举索引由 1 开始)
8. ENUM 的移植性不佳
ENUM 类型不是 SQL 标准, 属于 MySQL, 而其他 DBMS 不一定有原生的支持 PostgreSQL, MariaDB, 与 Drizzle (后面那两个就 MySQL 的分支), 我只知道这三个是支持的 ENUM 的如果某个人打算将数据库迁移, 那么他就要花费更多的步骤去处理你那些精妙的 ENUM 字段了, 相信他会更爱你如果 (那个人) 是你, 你可会发现自己当时真是聪明够了通常来说, 数据库迁移不会经常发生, 并且, 由于所有人都会假设迁移数据库的过程中, 必然要出乱子, 因此成为第八宗罪
几时适合使用 ENUM:
1. 当你需要存储的是准确不变的值时
大陆板块就是最好的例子, 定义十分准确另一个常用例子是称谓: 先生夫人小姐, 或者是扑克的花色: 方块梅花红心黑桃但是, 即便是这些例子, 有时也需要去拓展值的范围(例如有人需要你称呼陈医生而不是陈先生的时候, 或者你的扑克游戏里面需要用到小丑牌)
2. 你永远不需要存储额外的关联信息
用回扑克牌的例子扑克游戏老少咸宜, 依赖的规则是梅花和黑桃为黑色, 方块和红心为红色 (例如, 尤克牌) 如果我们需要为花色关联额外的信息, 例如颜色, 那将如何? 如果我们使用关系表, 那我们只需要在关系表中新增字段即可, 小事一桩如果我们使用 ENUM 去表示花色, 那我们就很难去准确的表示花色于颜色的关联了, 如此我们只能在应用层上去达成这种关联
3. ENUM 值的数量大于 2 个并少于 20 个
如果你的 ENUM 值只有两个, 你完全可以将 ENUM 换成更加高效的 TINYINT(1)或者更更高效的 BIT(1)(MySQL5.0.3 及以上)例如:
gender ENUM('male', 'female')
可以变换为: is_male BIT(1). 当你只有两个选项时, 完全能以布尔值 true/false, 结合字段名字中的 is 关键词来区分至于 20 个的上限设定, 没错, ENUM 事实上可以保存多达 65535 个值, 但求你千万别试超过二十个值会变得很累赘, 超过 50 个必然难于管理与使用
如果你无论如何都要用 ENUM:
1. ENUM 值千万不要使用数值型
ENUM 定义为字符型数据是有原因的并不是说你使用数值型字段类型去存储数字是错误的, 但有充足的证据显示, MySQL 内部机制使用数字去引用索引 (参考 上面的第七条) 反正不要在 ENUM 中存储数字, OK?
2. 考虑使用严格模式
启用严格模式, 至少在你插入一个不存在的 ENUM 值时会报告错误否则, 只会简单地出现一个警告, 继而该值被设置为一个空字符串 ""(枚举索引为 0)抄笔记: 如果你设置了 IGNORE, 错误依然会被忽略
结论
从开发维护的角度去做有意义的事, 性能问题出现时再考虑优化普遍而言, 使用关系表抑或是使用 ENUM 类型, 争议不断
性能瓶颈 (这个概念) 被滥用已是不争事实 开发者们浪费了大量的时间去思考它担心它,(例如)非关键代码上的运行速度这些对效率的苛求, 给调试与维护造成了很大的负面影响我们理应忽略那小部分的效率, 就拿 (达到)97%(效率) 而言, 过早的优化是万恶之源
虽说我们不应放弃优化那 3%(的效率), 但一个优秀的开发者不应为此而盲目自满(译: 指在开发意识上, 对于效率的高度追求), 而应意识到要理智对待关键代码但这也应在理解代码的前提下进行 - Donald Knuth
komlenic.com is the weblog/playground of Chris Komlenic, a full stack developer and generalist living in central Pennsylvania.
来源: https://juejin.im/entry/5aa231505188257bf550a663