第14章 对未知的恐惧

在我们的范例Bug数据库中，Accounts表有first_name和last_name两列。你可以通过使用字符串链接操作将这两个字段合并成用户的全名。

Fear-Unknown/intro/full-name.sql
SELECT first_name || ' ' || last_name AS full_name FROM Accounts;

假设老板让你修改一下数据库，加上用户的中间名的缩写。（如果两个用户的名和姓是一样的，通过中间名的缩写能够减少误解。）这是个很简单的活。你也可以手动加了一些用户的中间名缩写。

Fear-Unknown/intro/middle-name.sql
ALTER TABLE Accounts ADD COLUMN middle_initial CHAR(2);
UPDATE Accounts SET middle_initial = 'J.' WHERE account_id = 123;
UPDATE Accounts SET middle_initial = 'C.' WHERE account_id = 321;
SELECT first_name || ' ' || middle_initial || ' ' || last_name AS full_name FROM Accounts;

突然，程序不显示任何名字了。事实上，看了一下之后，你发现并不都如此。只有那些填了中间名缩写的用户的名字能正常显示，其他人的名字都是空的。

其他人的名字怎么了？你能在老板发现并开始恐慌地猜测你是不是丢了数据之前修复这个错误吗？

不可避免地，你的数据库中总会有一些字段是没有值的。不管是插入一个不完整的行，还是有些列可以合法地拥有一些无效值。SQL支持一个特殊的空值，就是我们所熟知的NULL。

有很多在SQL的表和查询中有效使用空值的途径。

本章的目的就是弄清楚如何编写那些包含NULL的查询。

很多开发人员都对SQL中NULL的行为感到茫然无措。SQL将NULL当做一个特殊的值，不同于0、false或者空字符串，这一点和大多数的编程语言都不同。大多数的数据库都遵循这种SQL标准，然而在Oracle和Sybase中，NULL的意义是长度为0的空字符串。NULL这个值也有一些特殊的行为。

让人奇怪的是，在一些值为NULL的列上进行计算所得到的结果。比如说，很多开发人员都觉得如下查询请求的结果中10应当作为Bugs的默认值返回，但是当hours列中的值为NULL是，返回的结果还是NULL，并非10。

Fear-Unknown/anti/expression.sql
SELECT hours + 10 FROM Bugs;

如下查询仅返回assigned_to为123的行，不包含别的值或者NULL：

Fear-Unknown/anti/search.sql
SELECT * FROM Bugs WHERE assigned_to = 123;

你可能会觉得如下查询会返回上面那个查询的补集，也就是所有之前查询没有返回的行：

Fear-Unknown/anti/search-not.sql
SELECT * FROM Bugs WHERE NOT (assigned_to = 123);

然而，这两个查询都不会返回assigned_to是NULL的记录。任何和NULL的比较都返回“未知”，既不是TRUE也不是FALSE。即使NULL的相反值也是NULL。

下面这个错误是在查询NULL或者非NULL值时常犯的：

Fear-Unknown/anti/equals-null.sql
SELECT * FROM Bugs WHERE assigned_to = NULL; 
SELECT * FROM Bugs WHERE assigned_to <> NULL;

WHERE子句的筛选逻辑是当其条件的返回值为TRUE时选择该记录，但和NULL比较永远得不到TRUE，相应地，返回的是“未知”。无论比较的逻辑是相等还是不等，返回的结果还是“未知”，当然“未知”不等于TRUE。之前的所有查询请求都没办法获得assigned_to为NULL的记录。

在参数化的SQL查询表达式中使用NULL进行查询时，碰到使用NULL作为普通值的列也非常地不方便。

Fear-Unknown/anti/parameter.sql
SELECT * FROM Bugs WHERE assigned_to = ?;

上述查询在传入一个普通的整形时会返回你所期望的值，但你不能使用NULL作为参数传入。

处理NULL会使查询变得更加复杂，很多软件开发人员就会选择在数据库中禁止NULL。取而代之的是，他们选择一个普通的值来标记“未知”或者“无效”。

“我们痛恨NULL！”杰克，一个软件开发人员，描述了他的客户端开发人员要求他屏蔽数据库中所有的NULL。他们的解释就是简单的“我们痛恨NULL”。NULL的存在会导致他们的程序代码出错。杰克询问我该用哪个值来代替NULL标记悬空值。

我告诉杰克，NULL的作用正是用来表示悬空值或者无效值。无论他选择别的什么值来标记一个悬空值，都要修改程序代码来对这个值进行特殊处理。

杰克的客户端开发人员对待NULL的态度是完全错误的。类似地，我可以坦率地说，我也不喜欢写代码来处理将零作为除数的问题，但这并不是不使用零的合理理由。

这样做的实际危害在哪里呢？来看如下的例子，我们将assigned_to这一列声明为NOT NULL：

Fear-Unknown/anti/special-create-table.sql
CREATE TABLE Bugs (bug_id SERIAL PRIMARY KEY,   -- other columns
   assigned_to BIGINT UNSIGNED NOT NULL,
   hours NUMERIC(9,2) NOT NULL,
   FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id) );

假设我们使用-1表示一个未知的值。

Fear-Unknown/anti/special-insert.sql
INSERT INTO Bugs (assigned_to, hours) VALUES (-1, -1);

hours这一列是数字，因此你定义某一个特殊的数字表示“未定义”。这个数字在这列中必须不具有任何含义，所以你选择了一个负数。但是-1这个值在执行SUM或者AVG的时候会导致计算结果错误。因而你必须在计算时使用另一个条件判断来去除这些列，这些额外的工作都是因为你要避免使用NULL所带来的。

Fear-Unknown/anti/special-select.sql
SELECT AVG( hours ) AS average_hours_per_bug FROM Bugs WHERE hours <> -1;

在另一列中，-1可能是一个有意义的值，因此你不得不根据每一列的定义和取值范围逐个选择一个不同的值。你还需要记住或者将这些特殊值写成文档。这都给项目增加了过多的复杂度和不必要的工作。

再来看assigned_to列。这是一个指向Accounts表的外键。当一个Bug被提交进系统但还没有指派给任何人去处理，应该用哪个非NULL的值来表示这个逻辑呢？任何一个非NULL的值都必须指向Accounts表中的一条记录，因此你需要在Accounts表中插入一条占位符似的记录，表示“没有人”或者“未指派”。为了让你能将一个Bug指派给一个人而创建这样一个没有意义的账号看上去非常地可笑。

当你将一列声明为NOT NULL时，也就是说，这列中的每一个值都必须存在且是有意义的。比如说，Bugs.reported_by这一列必须有一个值，因为每个Bug都是由某个人报告的。但一个Bug可能暂时还没有指派给任何人处理。悬空的值应该用NULL来表示。

如果你发现自己或者团队中有人描述了如下事件，可能就是没有正确地处理NULL。

NULL也是可关联的吗

关于SQL中的NULL也有一些争论。创立关系理论的计算机科学家E. F. Codd认为，使用NULL来标记悬空值是有必要的。然而，C. J. Date展示了标准SQL中定义的NULL的行为在某些特殊情况下和关系逻辑相违背。

事实是大多数编程语言都没有完美地实现计算机科学的那些理论。无论如何SQL都支持NULL。我们已经发现了一些危害，但你可以学着如何找出这些危害从而更有效地使用NULL。

要辨识出对NULL的错误使用是比较困难的事情。在测试阶段，一些错误可能不会发生，尤其是如果你漏掉了一些边界条件的测试而仅仅构造了一些简单的测试数据。然而，当程序用于实际生产时，所产生的数据可能是你不曾预料到的。如果一个NULL存在于数据中，产生错误就是一个时间问题了。

使用NULL并不是反模式，反模式是将NULL作为一个普通值处理或者使用一个普通的值来 取代NULL的作用。

有一种情况可以将NULL视为普通值，那就是导入或者导出数据的时候。在一个以逗号分割的文本文件中，所有的值都必须是可读的文本。比如，MySQL的mysqlimport工具在从文本文件中导入数据时，使用_\N_代表NULL。

类似地，用户不能直接输入一个NULL。程序的输入端可能会使用一些特殊处理来引导用户输入NULL。比如，微软.NET 2.0及以上版本，为Web界面提供了一个叫做ConvertEmptyStringToNull的方法。参数和绑定字段会自动地将空字符串转换成NULL。

最后，如果需要支持多个不同的悬空值时，NULL也不起作用。比如说，想要将一个Bug分为“从未被指派”和“被指派给一个离开项目组的人”，在这种情况下你不得为每种状态使用不同的值。

大多数使用NULL的问题都是源自于对SQL的三值逻辑的误解。对于习惯了传统的true/false逻辑程序员来说，这可是一个不小的挑战。不过只要稍微研究一下，你就能正确地在SQL中处理NULL。

假设斯坦30岁，而奥利弗的年龄未知。如果我问你到底是斯坦大还是奥利弗大，你只能回答：“我不知道。”如果我问你斯坦是不是和奥利弗一样大，你还是只能回答：“不知道。”如果我问你他们两个加起来有多大，你的答案依旧如此。

假设查理的年龄也是未知。如果我问你奥利弗和查理是不是一样大，你的答案还是“不知道”。这就是为什么NULL = NULL的结果是NULL。

下表列举了一些程序员期望得到某种结果，但事实却不如人意的情况。

当然，这些例子并不仅仅表示直接使用NULL这个关键字的情况，它们同样也适用于那些返回值为NULL的表达式。

理解NULL在布尔表达式中行为的关键点在于，要明白NULL既不是TRUE也不是FALSE。

下表列举了一些程序员所期望得到某种结果，但事实却不如人意的情况。

一个NULL值当然不是TRUE，同样也不是FALSE。如果是FALSE，对一个NULL使用NOT操作符，则应该返回TRUE，但事实是NOT(NULL)依旧返回一个NULL。这样的行为让那些想要在布尔表达式中使用NULL的人感到很困惑。

由于等于或者不等于操作在对NULL进行比较时都不会返回TRUE，你就需要使用别的操作来检索NULL。老的SQL标准定义了一个IS NULL的断言，在一个给定的操作值为NULL时，它将返回TRUE。与之对应的，IS NOT NULL在操作值是NULL时，返回FALSE。

Fear-Unknown/soln/search.sql
SELECT * FROM Bugs WHERE assigned_to IS NULL;
SELECT * FROM Bugs WHERE assigned_to IS NOT NULL;

在SQL-99标准中，额外定义了一个比较断言IS DISTINCT FROM。这个断言的行为模式类似于原来的<>操作符。不同的是，在处理操作数为NULL时它依旧会返回TRUE或FALSE。

错误的方法正确的结果

在如下的情况中，一个允许为空的列的行为基于巧合而得到了正确的结果。

SELECT * FROM Bugs WHERE assigned_to <> 'NULL';

这里允许为空的assigned_to列正用来和'NULL'这个字符串进行比较（注意引号），而不是和NULL这个关键字比较。

当assigned_to为NULL时，和字符串'NULL'的比较结果不为TRUE。这一条记录就从查询结果中排除了，而这正是程序员所期望的。

其余的情况是，一个整型的列和字符串'NULL'进行比较。像'NULL'这样的字符串在大多数数据库中都被视为0，而assigned_to的大多数值都大于0。这样的值就不等于一个字符串，因此在结果集中就有了这条记录。

因此，由于犯了另一个常见的错误——在NULL关键字上使用了引号——一些程序员可能无意间就得到了他们想要的结果。不幸的是，这样的巧合并不是总发生，比如WHERE assigned_to =

'NULL'。

这个断言能让你在进行比较操作之前不用再编写冗长的表达式判断IS NULL。如下两个查询是等价的：

Fear-Unknown/soln/is-distinct-from.sql
SELECT * FROM Bugs WHERE assigned_to IS NULL OR assigned_to <> 1;
SELECT * FROM Bugs WHERE assigned_to IS DISTINCT FROM 1;

你可以和查询参数一起使用这个断言，即使传入值就是一个NULL：

Fear-Unknown/soln/is-distinct-from-parameter.sql
SELECT * FROM Bugs WHERE assigned_to IS DISTINCT FROM ?;

每个数据库对IS DISTINCT FROM的支持是不同的。PostgreSQL、IBM DB2和Firebird直接支持它，Oracle和Microsoft SQL Server暂时还不支持。MySQL提供了一个专有的表达式，它的工作逻辑和IS NOT DISTINCT FROM一致。

如果NULL会破坏程序逻辑或者NULL本身就是毫无意义的，那我推荐你在定义对应的列时加上NOT NULL约束。让数据库来帮你确保约束的实行比自己写代码可靠得多。

比如说，在Bugs表中任意记录的date_reported、reported_by和status这几列的值都应该是非NULL的。类似地，在Comments这张子表中也必须有一个非NULL的bug_id列，指向一个切实存在的Bug。你应该在声明这些列的时候都加上NOT NULL选项。

有些人推荐你为每一列都定义一个DEFAULT值，这样一来当在执行插入操作时即使省略了某一列，也能获得一个非NULL的值。这样的建议也并不是通用的。比如说，Bugs.reported_by应该总是非NULL的。如果要定义默认值，应该用哪个值？对于一个在逻辑上没有默认值的列来说，声明一个NOT NULL约束是很合理、很常见的。

在一些查询请求中，你需要强制让某一列或者某个表达式返回非NULL的值，从而让查询逻辑变得更简单，但又不想将这个值存下来。你所需要的仅仅是在特定的请求时对一个给定的列临时设置一个默认值的方法。为此可以使用COALESCE()函数。这个函数接受一系列的值作为入参，并且返回第一个非NULL的参数。

在本章最开始所描述的拼接用户名字的案例中，可以使用COALESCE()函数来写一个表达式，使用一个空格代替中名缩写，这样即使中名缩写是NULL，返回的结果也始终是一个非NULL的中间名缩写，从而最终的结果不会变成NULL。

Fear-Unknown/soln/coalesce.sql
SELECT first_name || COALESCE(' ' || middle_initial || ' ', ' ') || last_name AS full_name FROM Accounts;

COALESCE()是一个SQL标准函数。一些数据库使用了别的函数来实现这个功能，诸如 NVL()或ISNULL()。