再看看 SQL 中的 null

发表于

NULL 与任何值的运算结果都是 NULL

NULL 有一个很重要的特性：NULL 与任何值的运算结果都是 NULL

1
2
3
4
5
SELECT 1 = 1; -- true
SELECT 1 = NULL; -- null
SELECT NULL = NULL; -- null
SELECT 1 != NULL; -- null
SELECT NULL != NULL; -- null

这也就意味着，对于存在列 col BOOLEAN NULLABLE 的表，如果一行中 col 列的值为 NULL，则下面的四条查询都不会包含这行

1
2
3
4
5
6
7
-- 等于自不必说
SELECT * FROM "table" WHERE col = true;
SELECT * FROM "table" WHERE col = false;

-- 但不等于也不会包括值为 null 的行 
SELECT * FROM "table" WHERE col != true;
SELECT * FROM "table" WHERE col != false;

IS NULL

我们其实基本都知道这点，所以我们在进行不等运算时通常会用 IS NULL / IS NOT NULL 特殊处理 NULL 列

1
2
SELECT * FROM "table" WHERE col is null OR col != true; -- 筛选 col = null 或 false
SELECT * FROM "table" WHERE col is null OR col != false; -- 筛选 col = null 或 true

IS TRUE / IS FALSE

但其实，除了 IS NULL， IS 操作符后面还可以跟随 BOOL 值，那么，用 IS NOT 其实是一个更加精准的不等于操作

1
2
SELECT * FROM "table" WHERE col IS NOT true; -- 筛选 col = null 或 false
SELECT * FROM "table" WHERE col IS NOT false; -- 筛选 col = null 或 true

而对于不是 bool 类型的 nullable 列，我们则可以搭配使用 = != 与 IS

1
2
3
-- 筛选 col = null 或任何不为 1 的值
SELECT * FROM "table" WHERE col is null OR col != 1;
SELECT * FROM "table" WHERE col = 1 IS NOT TRUE;

concat

NULL 与任何值的运算结果都是 NULL，但在函数中则不一定（取决于具体实现）

以连接字符串为例，如果中间存在 NULL，用 || 连接属于运算符，中间任何一项为 NULL 则结果为 NULL，而用 concat 连接则是函数，中间出现的 NULL 会被忽略

1
2
SELECT 'hello-' || NULL || 'world'; -- null
SELECT concat('hello-', NULL, 'world'); -- hello-world

布尔逻辑

NULL 与任何值的运算结果都是 NULL，但在布尔逻辑中则不一样…… 试试看能不能说出下面的返回值

1
2
3
4
5
6
7
8
9
10
11
12
13
select not NULL;
select null or null;
select null and null;

select null OR true;
select true OR null;
select null OR false;
select false OR null;

select null AND true;
select true AND null;
select null AND false;
select false AND null;
答案 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
select not NULL;          -- NULL
select null or null;      -- NULL
select null and null;     -- NULL

select null OR true;      -- TRUE
select true OR null;      -- TRUE
select null OR false;     -- NULL
select false OR null;     -- NULL

select null AND true;     -- NULL
select true AND null;     -- NULL
select null AND false;    -- FALSE
select false AND null;    -- FALSE

其实逻辑在于理解 NULL 代表着「未知」，与「真」「假」一起构成了一个三值集合

那么自运算，无论「未知的反面」还是「未知和未知」「未知或未知」都是未知，也因此都是 NULL

OR 的逻辑则是「有任何一个值为 TRUE 就是 TRUE」，因此，在 NULL 参与的运算中，存在 TRUE 则为 TRUE，不存在 TRUE 则为 NULL（继续未知）

AND 的逻辑则是「有任何一个值为 FALSE 就是 FALSE」，因此，在 NULL 参与的运算中，存在 FALSE 则为 FALSE，不存在 FALSE 则为 NULL（继续未知）

在聚合函数中

在聚合时，如果对 NULL 值进行聚合，它的数值是被完全忽略的 —— 一个例子是 avg，如果对 1, 2, 3, 4, NULL 做 avg，结果是 (1+2+3+4)/4

如果期望给 NULL 在聚合时一个默认值，可以用 coalesce 函数为它赋一个「默认值」

COALESCE

上面已经提过 coalesce 函数了，再说一句就是，coalesce 函数可以接受多个参数（而不只是两个），会返回第一个非 NULL 值

例如用 coalesce(first_name, nickname, email) 取用户「昵称」

NULLIF

pg 中还存在一个 nullif 函数，接收两个值，如果两个值相等则返回 NULL 否则返回第一个值，等价于 CASE WHEN a = b THEN NULL ELSE a END

这个函数主要用来「将零值转换为空值」；举个例子就是

1
2
3
4
SELECT 
  NULLIF(TRIM(name), '') AS name,
  NULLIF(status, 'N/A')  AS status
FROM t;

还有就是搭配 NULL 的「可转换为其他类型」的特性

1
2
3
4
SELECT
  -- 将 '' 直接转换成 date 是会报错的，但是先转换为 NULL 再转换为 date 就可以了
  NULLIF(col_date, '')::date AS date
FROM t;

NULL 值在 PostgresQL 协议中的表示

虽然大概率不会引起混淆，但还是说一下，虽然我们用 psql 规则看到 NULL 值就像是文本一样，但它的底层传输是二进制，PG 规定用 len = -1 代表 NULL 值，所以不存在它与空字符串、null 字符串等混淆的情况

另外，特殊的，在 COPY 中，如果用的 text format（PG 的消息协议中，对于一个值存在 text 和 binary 两种 format），那么会用 \N 代表 NULL 值

参考：https://www.postgresql.org/docs/current/protocol-message-formats.html 中的 Query, Parse, Bind, RowDescription, DataRow

NULL 值在文件系统中的存储

当行中有任一列值为 NULL 时，「行头」HeapTupleHeaderData 中的 t_infomask 字段内的 HEAP_HASNULL flag 会被标记为 1，此时在行头后面、其余数据前面会增加一个 null bitmap，用位图的形式存储所有列的 NULL 情况，且如果某一列的值为 null，后面的 data 中将不会出现这一列的信息

另外，null bitmap 除了用来处理 NULL 值，还会用来处理 drop column —— 当一列被删除时其实际上依然存在着，只是后面所有的行都会带有 null bitmap 将这一列标记为 NULL（更事实上，删除列是懒删除，行内的数据都还在，只有下次更新行时才会清理这些数据 —— 当然，清理的方式也是将它标记为 NULL）；

参考：https://www.postgresql.org/docs/current/storage-page-layout.html