第11章 每日新花样

在个人信息表中，称呼（salutation）列可以只有有限的几个候选值。基本上你只需要支持Mr.、Mrs.、Ms.、Dr.以及Rev.，这些称谓几乎覆盖了所有人。你可以在声明这个列的时候使用数据类型或者约束来指定这些候选值，因此不会有人往salutation列中加入无效值。

31-Flavors/intro/create-table.sql
CREATE TABLE PersonalContacts (   -- other columns
   salutation VARCHAR(4)
     CHECK (salutation IN ('Mr.', 'Mrs.', 'Ms.', 'Dr.', 'Rev.')), );

这一列基本上可以保持稳定，因为你不需要支持别的称呼了，是这样的吗？

遗憾的是，你的老板告诉你公司正准备在法国创建一家分公司。你需要支持M.、Mme.以及Mlle.这些称呼。你的任务就是让你的联系人表能接受这些值。这是一项很精细的任务，不中断表的读写操作可能无法完成这一任务。

你同时还得考虑，你的老板提到了下月可能会在巴西建立办事处的事情。

将一列的有效字段值约束在一个固定的集合内是非常有用处的。如果我们可以确保一列中永远不会包含无效字段，那对于使用方来说，逻辑会变得非常简单。

比如在Bugs表中，status列标记了一个给定的Bug是NEW、IN PROGRESS、FIXED等状态。这些值的意义与在项目中如何管理这些Bug有关，但我们现在所关心的是status这一列中的值必须限定在所列出来的这几个值中。

理想情况下，我们希望数据库能够拒绝无效值的输入：

31-Flavors/obj/insert-invalid.sql
INSERT INTO Bugs (status) VALUES ('NEW'); -- OK  
INSERT INTO Bugs (status) VALUES ('BANANA'); -- Error!

很多数据库设计人员趋向于在定义列的时候指定所有可选的有效数据。列的定义是元数据的一部分，也就是表结构定义的一部分。

比如说，你可以对某一列定义一个检查约束项。这个约束不允许往列中插入或者更新任何会导致约束失败的值。

31-Flavors/anti/create-table-check.sql
CREATE TABLE Bugs (   -- other columns
   status VARCHAR(20) CHECK (status IN ('NEW', 'IN PROGRESS', 'FIXED')) );

MySQL支持使用ENUM关键字来约束一列的取值范围，但这并不是一个标准类型。

31-Flavors/anti/create-table-enum.sql
CREATE TABLE Bugs (   -- other columns
   status ENUM('NEW', 'IN PROGRESS', 'FIXED'), );

在MySQL的实现中，即使你使用字符串表示ENUM里的值，但实际存储在列中的数据是这些值在定义时的序数。因此，这列的数据是字节对齐的，当你进行一次排序查询时，结果是按照实际存储的序号进行排序的，而非对应字符串的字母序。这可能并不是你所希望的。

其他的解决方案包含了域以及用户自定义类型（UDT）。你可以使用这些方法来约束某一列只能接受一个特定集合中的数据，并且能很方便地将这个约束应用到整个域上。但这些特性并没有得到大多数关系数据库系统的支持。

最后一个办法，你还可以编写一个触发器，当修改指定列的内容时触发，将被修改的值和允许输入的值进行匹配，如果不符合则产生一个错误中断操作。

所有的这些解决方案都有一定的缺陷。下面就来一一描述这些问题。

假设你正在为Bug跟踪服务开发一个用户界面程序，它允许用户编辑Bug报告。为了让界面能够引导用户选择一个合适的status值，你选择使用一个包含所有可选值的下拉菜单。你要如何查询数据库来获取当前可以输入到status列中值的枚举列表呢？

你的第一反应可能是查询当前列中所有正在被使用的值，使用如下这个简单的查询：

31-Flavors/anti/distinct.sql
SELECT DISTINCT status FROM Bugs;

然而，如果所有的Bug都是新建的，上面这个查询只会返回NEW。如果你使用这样的结果集来填充用户界面中的控件，这就变成了一个先有鸡还是先有蛋的问题：你无法将一个Bug的状态改变为当前使用状态以外的值。

要获得所有允许输入的status候选值，你需要查询这一列的元数据。大多数SQL数据库支持使用系统视图来完成这种查询需求，但是使用起来是很复杂的。比如，如果你使用MySQL的ENUM类型，可以使用如下的查询语句来查询INFORMATION_SCHEMA系统视图：

31-Flavors/anti/information-schema.sql
SELECT column_type FROM
information_schema.columns WHERE table_schema = 'bugtracker_schema'
   AND table_name = 'bugs'
   AND column_name = 'status';

你无法简单地从INFORMATION_SCHEMA的结果集中获取单独的枚举值，而是得到了一个包含Check约束或者ENUM类型声明的字符串。比如，在MySQL中，上述查询就会返回一个类型为LONGTEXT、内容为ENUM('NEW',

'IN PROGRESS', 'FIXED')的结果，结果中包含了括号、逗号及单引号。你必须额外编写一段程序代码来解析这个字符串，将每个引号对中的数据独立抽取出来，才能在控件中使用。

对于获取Check约束、域或者UDT信息的查询来说，过程会更加复杂。大多数开发人员非常勇敢地在程序中手动维护这样一个列表。当程序数据和数据库的元数据不同步时，程序很容易就崩溃了。

最常见的改变就是添加或删除一个候选值。没有什么语法支持从ENUM或者Check约束中添加或删除一个值，你只能使用一个新的集合重新定义这一列。如下是一个更新MySQLENUM的例子，目的是将DUPLICATE添加到status的候选值列表中：

31-Flavors/anti/add-enum-value.sql
ALTER TABLE Bugs MODIFY COLUMN status
   ENUM('NEW', 'IN PROGRESS', 'FIXED', 'DUPLICATE');

你先要知道之前的定义允许NEW、IN PROGRESS和FIXED，这样问题又绕回到了之前如何获取这些值上了。

一些数据库要求只有表为空表时才能改变某一列的定义。你可能需要先转存这张表中的数据，清空这张表，重新定义它，然后再将数据重新导入，这个过程会使得这张表处于无法访问的状态。这种工作非常常见，以致它已经有了个名字：ETL，表示“抽取、转换和加载”。有一些数据库支持使用ALTER TABLE指令重新构建一张使用中的表，但这个操作依旧是非常复杂的，并且其开销也很巨大。

作为一个策略问题，修改元数据——意味着修改表或列的定义——应该是极少的，并且需要大量的测试以保证质量。如果你需要修改元数据来对一个ENUM定义进行添加或删除操作，就不得不投入大量的测试时间或者让很多工程师关注这个修改所带来的影响。否则，这样的修改就会导致额外的风险，让你的程序变得不稳定。

如果你打算废弃一个选项，你可能会为老数据而烦恼。比如，将质量控制流程中的FIXED状态拆分成CODE COMPLETE和VERIFIED两个状态：

31-Flavors/anti/remove-enum-value.sql
ALTER TABLE Bugs MODIFY COLUMN status
   ENUM('NEW', 'IN PROGRESS', 'CODE COMPLETE', 'VERIFIED');

如果移除FIXED，你要如何处理已经是FIXED状态的数据？将所有的FIXED状态修改为VERIFIED？还是将其设为空或者默认值？

你可能不得不保留这个老数据已使用的废弃选项。但又要如何在用户界面上区分废弃的和可用的Bug状态候选值呢？

Check约束、域和UDT在各种数据库中的支持形式并不统一。ENUM是MySQL独有的特性。每一个数据库对于列的候选值列表长度的定义都不尽相同。触发器的语法也区别很大。这些差异使得你在需要支持多种数据库时很难选择一个合适的解决方案。

使用ENUM或者Check约束时遇到的问题可能是候选值集合并不固定。如果你正考虑使用ENUM，首先问一下自己，候选值的集合是否需要改变或者是否可能改变。如果答案为“是”，那使用ENUM就不见得是好主意。

就像我们讨论的那样，ENUM在候选值几乎不变的情况下所造成的问题很少。通过查询获取元数据依旧是很麻烦的，但是你可以在程序代码中维护一份列表而不用担心不同步的问题。

ENUM在存储没有业务逻辑且不需要改变的候选值时是非常方便的。比如存储一对二选一且相互对立的值：LEFT/RIGHT、ACTIVE/INACTIVE、ON/OFF、INTERNAL/EXTERNAL等。

Check约束可以在更多的场景下使用，不仅仅是实现一个类ENUM的机制，比如用来检查一个时间区间中start永远小于end。

有一个更好的解决方案来约束一列中的可选值：创建一张检查表，每一行包含一个允许在Bugs.status列中出现的候选值；然后定义一个外键约束，让Bugs.status引用这个新表。

31-Flavors/soln/create-lookup-table.sql
CREATE TABLE BugStatus (status VARCHAR(20) PRIMARY KEY );
INSERT INTO BugStatus (status) VALUES ('NEW'), ('IN PROGRESS'), ('FIXED');
CREATE TABLE Bugs (   -- other columns
 status VARCHAR(20),
 FOREIGN KEY (status) REFERENCES BugStatus(status) ON UPDATE CASCADE );

当你插入或更新Bugs表中的一条记录时，必须使用存在于BugStatus表中的一个status值。这样做类似于ENUM和Check约束一样，能够确保status值的有效性；同时，这样的方案还在其他地方体现出了它的灵活性。

候选值集合现在是存储在数据表中，而不是像ENUM那样存储在元数据中。你可以使用SELECT对这张检查表进行查询来获取相关数据，和查询别的表没什么区别。这使得获取候选值集合并作为数据集在用户界面中展示变得非常简单。你甚至可以对用户可选值进行排序操作。

31-Flavors/soln/query-canonical-values.sql
SELECT status FROM BugStatus ORDER by status;

当你使用检查表时，可以使用原始的INSERT语句向其中加入一个值。你可以不中断对表的访问就完成这样的改变。你也不需要重新定义任何列，不需要安排下线时间，或者执行一次ETL操作。同样，你也不需要在执行添加或删除操作前知道当前检查表中有哪些值。

31-Flavors/soln/insert-value.sql
INSERT INTO BugStatus (status) VALUES ('DUPLICATE');

如果定义外键时使用了ON UPDATE CASCADE选项，重命名一个值也会变得非常方便。

31-Flavors/soln/update-value.sql
UPDATE BugStatus SET status = 'INVALID' WHERE status = 'BOGUS';

如果检查表中的一个值被Bugs表中的数据引用了，那就不能删除它了。status列上的外键确保了引用完整性，因此，status列引用的值必须存在于检查表中。

然而，你可以在检查表中增加另一个属性列来标记一些废弃数据。这样做允许你保留Bugs.status列中的历史数据，同时又能够区分哪些值是能够出现在用户界面上的。

31-Flavors/soln/inactive.sql
ALTER TABLE BugStatus ADD COLUMN active   ENUM('INACTIVE', 'ACTIVE') NOT NULL DEFAULT 'ACTIVE';

使用UPDATE代替DELETE来废弃一个值：

31-Flavors/soln/update-inactive.sql
UPDATE BugStatus SET active = 'INACTIVE' WHERE status = 'DUPLICATE';

当要获取在界面上展示的候选值列表时，在查询条件中增加一个status为ACTIVE的约束：

31-Flavors/soln/select-active.sql
SELECT status FROM BugStatus WHERE active = 'ACTIVE';

这样的解决方案相比于ENUM或者Check约束来说更加灵活，因为前两者无法为每个值提供额外属性。

不同于ENUM类型、Check约束，或者域及UDT，检查表的解决方案只依赖于最基本的SQL特性：使用外键确保引用完整性。这使得该解决方案的兼容性得到了保证。

由于在每一行中存储一个候选值，就使得检查表在理论上可以支持无限多个候选值。