SQL - 空字符串、空白字符串和 NULL

发表于 2022/04/02 更新于 2022/04/04 分类于 SQL

本文谈谈 SQL 中的空字符串，空白字符串和 NULL.

遇到这样一个需求，从数据库中获取中文名，无中文名时以英文名代替。而无中文名实际上可能有空白字符串，空字符串，还可能是 NULL, 在处理这些值的时候遇到了一些有趣的结果。

为了解释以下查询语句的结果，假设我们有一个表：

c_name	name
NULL	name with c_name null
	name with c_name empty

你可以在 db<>fiddle 中测试本文提到的 SQL 语句。

原始 SQL 语句为：

1	select case c_name when '' then name else c_name end as name from employee

空白字符串与空字符串

我首先考虑的是直接与空字符串比较是否会在 c_name 为多个空白字符的时候因被判不等而返回空白字符串，C# 中字符串的判空方法就有 String.IsNullOrEmpty(String) 与 String.IsNullOrWhiteSpace(String) 的区别，因此将查询语句改成了以下版本。

-- SQL Server 2017 (14.x) and later
select case trim(c_name) when '' then name else c_name end as name from employee
-- SQL Server 2017 (14.x) before
select case ltrim(rtrim(c_name)) when '' then name else c_name end as name from employee

根据 MS Docs , TRIM 与 LTRIM(RTRIM(@string)) 的行为一致，但 TRIM 是 SQL Server 2017(14.x) 才引进的。

但我当我使用上面的脚本和之前的脚本去测试一个包含多个空白字符的 c_name 时，发现它们都能得到正确的结果 name ，而不会返回空白字符串 c_name 。实验结果证明空白字符串无论含有多少个空白字符，实际上在 SQL Server 中都是相等的。

几经搜索后发现这是 SQL - 92 中规定的标准。标准的第208页写道：

If the length in characters of X is not equal to the length in characters of Y, then the shorter string is effectively replaced, for the purposes of comparison, with a copy of itself that has been extended to the length of the longer string by concatenation on the right of one or more pad characters, where the pad character is chosen based on CS. If CS has the NO PAD attribute, then the pad character is an implementation-dependent character different from any character in the character set of X and Y that collates less than any string under CS. Otherwise, the pad character is a .

也就是说当一个空字符串与一个有多个空白字符的空白字符串进行比较时，空字符串会以添加空格的形式被扩展，从而最后两者的值被判定为相等。

所以我们实际上仍然可以使用原来的查询语句。

NULL

前面提到，除了空白字符串，空字符串，实际上我们还有可能遇到 NULL 作为 c_name 的取值，这时候上面的查询语句是否还能正常工作呢？

很不幸，在以 NULL 作为 c_name 的测试中，NULL 依然被作为了一个合法的中文名被返回。

先来看看上面语句的另一种写法：

1	select case when c_name != '' then c_name else name end as name from employee

这条查询语句与上面原始的查询语句是否一样呢？从语义上来看似乎是一样的，只不过是把 case when 条件换了个顺序。但是令人惊讶的是，换做这种写法时，我们得到了正确的结果，它不再会返回 NULL 。

这是怎么回事呢？按照我们上面的测试，c_name 为 NULL 时与空字符串不等，所以仍然返回了 c_name , 但在这里，按照这样的理解，它似乎应该还是返回 c_name 。我一度以为是 case when 的用法有什么奇妙之处导致了这个问题，但似乎没有找到类似的问题，于是在 Stack Overflow 上发问，才发现原来并不是 case when 的问题，而是 NULL 的问题。网友指出：

Remember: null does not mean "empty". Instead, it means "I don't know".

LeetCode 中有道类似 SQL 的题目，第一反应肯定是 referee_id != 2 这样最直观的想法，但是你会发现如果直接使用这个 where 条件，在 NULL 确实与2不相等的情况下却不会被返回，这是怎么回事呢？

来看看 SQL - 92 关于等于操作符有怎样的规定：

General Rules

Let X and Y be any two corresponding <row value constructor element>s. Let XV and YV be the values represented by X and Y, respectively.

Case:

If XV or YV is the null value, then "X <comp op> Y" is unknown.

在这里我们可以看到，如果被比较的两个值至少有一个为 NULL , 比较的结果实际上并非我们想象中的 True | False , 而是 Unkown ，所以我们的 WHERE 语句对于值为 NULL 的记录得到的并不是一个 True 的结果。

在前面的查询语句中，我们的相等条件不满足，返回了 c_name , 在这一条查询语句中，我们的不等条件实际上得到了一个Unkown 也不满足 True ，所以它不会返回第一个表达式 c_name ，误打误撞返回了正确的 name 。

这里涉及到了一个三元问题，我们发现一个原本应该得到的二元布尔值集合变成了一个三元集合。在我们所接触过的其他语言中，布尔值明确地就是 True | False ，而在 SQL 中，这个概念相当于是被扩充了，在逻辑运算的计算法则上也有很大的不同，这里不再展开。

ISNULL|NULLIF

回到上面的最初查询名字的问题，网友给出了一种方案：

1	select isnull(nullif(c_name, ''), name) as name from employee

我尝试使用该查询语句，它确实可以返回不为空和 NULL 的结果，但是结果被截断了。实际上当我的 c_name 定义长度小于 name 的定义长度时，就有可能返回一个截断的结果。

先来看看这两个表达式，首先是 NULLIF :

1
NULLIF ( expression , expression )  
NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.

两个表达式不等时返回第一个表达式，两个表达式相等时，返回 NULL 。

根据 SQL - 92 ，实际上 NULLIF 相当于 CASE ... WHEN ：

NULLIF (V1, V2) is equivalent to the following <case specification>:
1
CASE WHEN V1=V2 THEN NULL ELSE V1 END

当我们的 c_name 为空字符串或空白字符串时，两个表达式根据前面的解释应该相等，即返回 NULL ，当 c_name 为 NULL 时，由于 NULL 不等于空白字符串，返回自身，即这里将空白字符串，空字符串，NULL 都返回为了 NULL ，注意这里的返回类型与 c_name 一致。

再来看看 IFNULL :

1
ISNULL ( check_expression , replacement_value )  
Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.

现在我们已经知道 check_expression 是一个 NULL ，只需要 replacement_value 用我们需要的 name 代替似乎就大功告成了。但事实上我们丢失了部分字符，例如我如果定义 c_name 为 char(4)，返回结果将变为只保留了前4个字符而非我们完整的 name 字符串。这又是怎么回事呢？

The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different. replacement_value can be truncated if replacement_value is longer than check_expression.

官方定义文档中提到了这点，如果 check_expression 与 replacement_value 的类型定义不一致，且 replacement_value 长度大于 check_expression ，这时返回值会被截断。值得注意的是，SQL - 92 中对 ISNULL 是没有定义的。

COALESCE

在搜索 ISNULL 的截断相关资料时，又看到了类似的 COALESCE 的讨论。

于是我们上面使用 ISNULL 得到截断结果的查询语句又可以改写成：

1	select coalesce(nullif(c_name, ''), name) from employee

这一次我们发现我们不再会得到截断的结果，它和我们上面使用 CASE 误打误撞得到的正确结果一致。

实际上根据 SQL - 92 的定义，COALESCE 可以改写成 CASE ：

COALESCE (V1, V2) is equivalent to the following
:

1
CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END

这时候理解 COALESCE 就非常直观了，我们先用 NULLIF 将不相等的空白字符串与 NULL 都转成一样的 NULL ，再使用 COALESCE 代替前面有截断缺陷的 ISNULL，它实际上是与 ISNULL 的语义一致。

Stored Procedure 与NULL

上面的问题我们已经得到了合适的方案，这里顺便一提正好有点相关的 NULL 在 Stroed Procedure 中的使用。

常常会遇到这样的需求，某个查询页面，用户需要根据不同的条件查询不同的信息，往往这个不同的信息还需要“全部”这个选项。我们知道，如果是一个单一的查询条件，传递起来是比较方便的，我们直接获取用户通过控件选择的输入值作为 WHERE 语句的判断条件即可得到正确结果。但是对于全部呢？这时候如果这个选项非常多，将其串联再传递给 Stored Procedure 就显得非常没有必要了，因为这时候我们实际上不需要对这些记录进行过滤。

通常我们可能会使用 IF ... ELSE ... 去判断参数值然后走不同的 SELECT 语句，但是如果可供用户选择的条件非常多，这样又显得有些繁琐了。还有一种办法是通过 IF ... ELSE ... 判断后生成 WHERE 条件，然后生成我们需要的查询语句。那么有没有什么办法，让我们直接去判断这个参数呢？通过我们上面提到的 COALESCE 就可以做到：

1	WHERE COL = COALESCE(@INPUT, COL)

这时候当后台调用传入 @INTPUT = NULL 时，就相当于忽视了这个 WHERE 条件，而当我们传入一个不为 NULL 的筛选值时，这个 @INPUT 筛选值就会作用于这个 WHERE 语句，这时候我们就很容易处理后台的用户输入值，也很容易用单一 Stored Procedure 去完成这个查询任务了。

空白字符串与空字符串

NULL

ISNULL|NULLIF

COALESCE

Stored Procedure 与NULL

参考资料