在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

那些年我們踩過(guò)的坑，SQL 中的空值陷阱！

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        NULL 即是空
        三值邏輯
        空值比較
        NOT IN 與空值
        函數(shù)與空值
        DISTINCT、GROUP BY、UNION 與空值
        ORDER BY 與空值
        空值處理函數(shù)
        字段約束與空值

null

SQL 是一種聲明式的語(yǔ)言，我們只需要描述想要的結(jié)果（WHAT），而不關(guān)心數(shù)據(jù)庫(kù)如何實(shí)現(xiàn)（HOW）；雖然 SQL 比較容易學(xué)習(xí)，但是仍然有一些容易混淆和出錯(cuò)的概念。

今天我們就來(lái)說(shuō)說(shuō) SQL 中的空值陷阱和避坑方法，涉及的數(shù)據(jù)庫(kù)包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。還是老規(guī)矩，結(jié)論先行：

本文使用的示例數(shù)據(jù)可以點(diǎn)擊鏈接《SQL 入門教程》示例數(shù)據(jù)庫(kù)下載。
NULL 即是空

在數(shù)據(jù)庫(kù)中，空值（NULL）是一個(gè)特殊的值，通常用于表示缺失值或者不適用的值。比如，填寫(xiě)問(wèn)卷時(shí)不愿意透露某些信息會(huì)導(dǎo)致錄入項(xiàng)的缺失，在公司的組織結(jié)構(gòu)中總會(huì)有一個(gè)人（董事長(zhǎng)/總經(jīng)理）沒(méi)有上級(jí)領(lǐng)導(dǎo)。

首先一點(diǎn)，空值與數(shù)字 0 并不相同。假如我問(wèn)你：你的錢包里有多少錢？如果你知道里面沒(méi)有錢，可以說(shuō)是零；如果你不確定，那么就是未知，但不能說(shuō)沒(méi)有。當(dāng)我們需要?jiǎng)?chuàng)建一個(gè)表來(lái)存儲(chǔ)這個(gè)信息的時(shí)候，應(yīng)該是 NULL；除非我們能夠確定錢包里面沒(méi)有錢或者有多少錢。

另外，空值與空字符串（’’）也不相同，原因和上面類似。但是 Oracle 是一個(gè)例外，我們會(huì)在下文具體討論。

在大多數(shù)編程語(yǔ)言中，訪問(wèn) null 值通常會(huì)導(dǎo)致錯(cuò)誤；但是 SQL 不會(huì)出錯(cuò)，只是會(huì)影響到運(yùn)算的結(jié)果而已。
三值邏輯

在大多數(shù)編程語(yǔ)言中，邏輯運(yùn)算的結(jié)果只有兩種情況，不是真（True）就是假（False）。但是對(duì)于 SQL 而言，邏輯運(yùn)算還可能是未知（Unknown）：

trheevalue
引入三值邏輯主要是為了支持 NULL，因?yàn)?NULL 代表的是未知數(shù)據(jù)。因此，SQL 中的邏輯運(yùn)算與（AND）、或（OR）以及非（NOT）的結(jié)果如下：

對(duì)于 AND 運(yùn)算符而言，真和未知的與運(yùn)算有可能是真，也有可能是假；因此，最終的結(jié)果是未知。

??SQL 中的 WHERE、HAVING 以及 CASE WHEN 子句只返回邏輯運(yùn)算結(jié)果為真的數(shù)據(jù)，不返回結(jié)果為假或未知的數(shù)據(jù)。
1

空值比較

當(dāng)我們使用比較運(yùn)算符（=、<>、<、> 等）與 NULL 進(jìn)行比較時(shí)，結(jié)果既不是真也不是假，而是未知；因?yàn)?NULL 表示未知，也就意味著可能是任何值。以下運(yùn)算的結(jié)果都是未知：

NULL = 0
NULL <> 0
NULL <= 0
NULL = NULL
NULL != NULL

NULL 與任何值都不相等，甚至兩個(gè) NULL 也不想等；因?yàn)槲覀儾荒苷f(shuō)兩個(gè)未知的值相同，也不能說(shuō)它們不相同。

??對(duì)于比較運(yùn)算而言，NULL 和 NULL 不相同；但是某些 SQL 子句中的 NULL 值被看作相同的值，例如 GROUP BY。具體參考下文。

那么，如何判斷一個(gè)值是否是 NULL 呢？為此，SQL 引入了兩個(gè)謂詞（WHERE 子句）：IS NULL和IS NOT NULL。以下示例用于查找 manager 為空的員工：

– 使用比較運(yùn)算符判斷空值
SELECT employee_id, first_name, last_name, manager_id
FROM employees
WHERE manager_id = NULL;

– 使用 IS NULL 判斷空值
SELECT employee_id, first_name, last_name, manager_id
FROM employees
WHERE manager_id IS NULL;

    100|Steven    |King     |          |

其中，第一個(gè)查詢使用比較運(yùn)算符判斷空值，不會(huì)返回任何結(jié)果；第二個(gè)查詢使用 IS NULL 判斷空值，返回了正確的結(jié)果。

除了標(biāo)準(zhǔn)的IS [NOT] NULL之外，還有一些數(shù)據(jù)庫(kù)擴(kuò)展的運(yùn)算符可以用于空值比較：

– MySQL
SELECT employee_id, first_name, last_name, manager_id
FROM employees
WHERE manager_id <=> NULL;

    100|Steven    |King     |          |

– PostgreSQL
SELECT employee_id, first_name, last_name, manager_id
FROM employees
WHERE manager_id IS NOT DISTINCT FROM NULL;

    100|Steven    |King     |          |

MySQL 中的<=>可以用于等值比較，支持兩個(gè) NULL 值；PostgreSQL 中的IS [NOT] DISTINCT FROM可以用于等值比較，支持兩個(gè) NULL 值。

以下查詢的結(jié)果也不會(huì)返回任何結(jié)果：

SELECT employee_id, first_name, last_name, manager_id
FROM employees
WHERE (1 = NULL) OR (1 != NULL);

因?yàn)楦鶕?jù)上面的三值邏輯，兩個(gè)未知結(jié)果的 OR 運(yùn)算最終還是未知。

前文我們說(shuō)過(guò)，空字符串不是 NULL；但是 Oracle 中的空字符串被看作 NULL。例如：

– Oracle
SELECT 1
FROM dual
WHERE ‘’ IS NULL;

– 其他數(shù)據(jù)庫(kù)
SELECT 1 AS val
WHERE ‘’ IS NULL;

當(dāng)然，我們?nèi)绻褂玫戎担?）運(yùn)算符判斷空字符串與 NULL，結(jié)果仍然為空。
NOT IN 與空值

對(duì)于 WHERE 條件中的 IN 和 NOT IN 運(yùn)算符，使用的是等值比較。所以如果 NOT IN 碰到了 NULL 值，永遠(yuǎn)不會(huì)返回任何結(jié)果。例如：

SELECT employee_id, first_name, last_name, manager_id
FROM employees
WHERE 1 NOT IN (NULL, 2);

因?yàn)樯厦娴臈l件實(shí)際上等價(jià)于：

SELECT employee_id, first_name, last_name, manager_id
FROM employees
WHERE 1 != NULL AND 1 != 2;

1 不等于 NULL 的結(jié)果是未知，1 不等于 2 的結(jié)果是真，未知和真的 AND 運(yùn)算結(jié)果還是未知。

??如果使用 NOT IN，一定要確保括號(hào)中的值不會(huì)出現(xiàn) NULL；或者盡量使用 NOT EXISTS。
1

函數(shù)與空值

一般來(lái)說(shuō)，函數(shù)和表達(dá)式的參數(shù)中如果存在 NULL，其結(jié)果也是 NULL。當(dāng)然也有一些例外，比如聚合函數(shù)。

以下查詢返回的都是 NULL：

SELECT ABS(NULL), 1 + NULL
FROM employees
WHERE employee_id = 100;

一個(gè)未知值的絕對(duì)值仍然未知，1 加上一個(gè)未知值結(jié)果還是未知。

但是一個(gè)常見(jiàn)的例外是字符串與 NULL 的連接：

– Oracle、SQL Server、PostgreSQL
SELECT CONCAT(‘Hello’, NULL)
FROM employees
WHERE employee_id = 100;

– MySQL
SELECT CONCAT(‘Hello’, NULL)
FROM employees
WHERE employee_id = 100;

           [NULL]|

Oracle 將 NULL 看作空字符串，所以查詢結(jié)果為“Hello”；SQL Server 和 PostgreSQL 雖然區(qū)分了 NULL 和空字符串，但是 CONCAT 函數(shù)中這兩者等價(jià)；MySQL 中 NULL 參數(shù)導(dǎo)致 CONCAT 函數(shù)結(jié)果為 NULL；SQLite 沒(méi)有提供 CONCAT 函數(shù)。

另外，Oracle 中的 || 也將 NULL 看作空字符串；其他數(shù)據(jù)庫(kù) || 中的 NULL 將參數(shù)會(huì)產(chǎn)生 NULL 結(jié)果；SQL Server 中使用 + 連接字符串，NULL 參數(shù)將會(huì)產(chǎn)生 NULL 結(jié)果。

聚合函數(shù)（SUM、COUNT、AVG 等）通常會(huì)在進(jìn)行計(jì)算之前刪除 NULL 數(shù)據(jù)：

SELECT SUM(salary + commission_pct) sum1,
SUM(salary) + SUM(commission_pct) sum2,
COUNT(salary),
COUNT(commission_pct)
FROM employees;

第一個(gè) SUM 函數(shù)返回的是 salary 和 commission_pct 都不為空的數(shù)據(jù)總和；第而個(gè) SUM 函數(shù)返回的是 salary 不為空的數(shù)據(jù)總和加上 commission_pct 不為空的數(shù)據(jù)總和，所以比第一個(gè)數(shù)據(jù)大；COUNT 函數(shù)結(jié)果顯示 salary 有 107 條記錄不為空，commission_pct 只有 35 條記錄不為空。

如果輸入數(shù)據(jù)都是 NULL 值，除了 COUNT 函數(shù)之外的其他聚合函數(shù)返回 NULL：

SELECT COUNT(*), COUNT(commission_pct), AVG(commission_pct), SUM(commission_pct)
FROM employees
WHERE commission_pct IS NULL;

  72|                    0|             [NULL]|             [NULL]|

COUNT(*) 總是返回?cái)?shù)據(jù)的行數(shù)，不受空值的影響；COUNT(commission_pct) 返回了零；AVG 和 SUM 返回了 NULL。
DISTINCT、GROUP BY、UNION 與空值

SQL 中的分組操作將所有的 NULL 值分到同一個(gè)組，包括 DISTINCT、GROUP BY 以及窗口函數(shù)中的 PARTITION BY。例如：

SELECT DISTINCT commission_pct
FROM employees;

    [NULL]|
      0.40|
      0.30|
      0.20|
      0.25|
      0.15|
      0.35|
      0.10|

SELECT commission_pct
FROM employees
GROUP BY commission_pct;

    [NULL]|
      0.40|
      0.30|
      0.20|
      0.25|
      0.15|
      0.35|
      0.10|

從上面的示例可以看出，commission_pct 為空的數(shù)據(jù)有 72 條，但是分組之后只有一個(gè) NULL 組。

除此之外，UNION 操作符也將所有的 NULL 看作相同值：

SELECT manager_id
FROM employees
WHERE manager_id IS NULL
UNION
SELECT manager_id
FROM employees
WHERE manager_id IS NULL;

[NULL]|

如果將 UNION 換成 UNION ALL，查詢結(jié)果將會(huì)保留 2 個(gè) NULL 值。
ORDER BY 與空值

SQL 標(biāo)準(zhǔn)沒(méi)有定義 NULL 值的排序順序，但是為 ORDER BY 定義了 NULLS FIRST 和 NULLS LAST 選項(xiàng)，用于明確指定空值排在其他數(shù)據(jù)之前或者之后。

不同數(shù)據(jù)庫(kù)對(duì)此提供了不同的實(shí)現(xiàn)：

SELECT employee_id, manager_id
FROM employees
WHERE employee_id IN (100, 101, 102)
ORDER BY manager_id;

– Oracle、PostgreSQL

    101|       100|
    102|       100|
    100|    [NULL]|

– MySQL、SQL Server、SQLite

    100|    [NULL]|
    101|       100|
    102|       100|

其中，Oracle 和 PostgreSQL 默認(rèn)將 NULL 作為最大值，升序時(shí)排在最后；MySQL、SQL Server 和 SQLite 默認(rèn)將 NULL 作為最小值，升序時(shí)排在最前。

另外，Oracle、PostgreSQL 和 SQLite 提供了擴(kuò)展的 NULLS FIRST 和 NULLS LAST 選項(xiàng)：

– Oracle、PostgreSQL 和 SQLite
SELECT employee_id, manager_id
FROM employees
WHERE employee_id IN (100, 101, 102)
ORDER BY manager_id NULLS FIRST;

    100|    [NULL]|
    101|       100|
    102|       100|

我們也可以使用 CASE 表達(dá)式實(shí)現(xiàn)類似的效果。以下示例與 NULLS LAST 作用相同，而且所有數(shù)據(jù)庫(kù)都可以使用：

SELECT employee_id, manager_id
FROM employees
WHERE employee_id IN (100, 101, 102)
ORDER BY CASE WHEN manager_id IS NULL THEN 1
ELSE 0
END,
manager_id;

    101|       100|
    102|       100|
    100|    [NULL]|

首先，CASE 表達(dá)式將 manager_id 為空的數(shù)據(jù)轉(zhuǎn)換為 1，非空的數(shù)據(jù)轉(zhuǎn)換為 0，所以空值排在其他數(shù)據(jù)之后；第二個(gè)排序字段 manager_id 確保了非空的數(shù)據(jù)從小到大排序。
空值處理函數(shù)

由于空值的特殊性，我們?cè)诜治鰯?shù)據(jù)時(shí)經(jīng)常需要進(jìn)行空值和其他值的轉(zhuǎn)換。為此，SQL 提供了兩個(gè)標(biāo)準(zhǔn)的空值函數(shù)：COALESCE 和 NULLIF。

COALESCE(exp1, exp2, …) 函數(shù)用于將 NULL 轉(zhuǎn)換為其他值。當(dāng) exp1 不為空時(shí)返回 exp1，否則檢查 exp2；如果 exp2 不為空時(shí)返回 exp2，依次類推。例如：

SELECT COALESCE(NULL, NULL, 3)
FROM employees
WHERE employee_id = 100;

                  3|

由于前面兩個(gè)參數(shù)都是 NULL，COALESCE 最終返回了 3。

COALESCE 函數(shù)也可以使用 CASE 表達(dá)式改寫(xiě)如下：

CASE WHEN exp1 IS NOT NULL THEN exp1
WHEN exp2 IS NOT NULL THEN exp2
…
ELSE expN
END

NULLIF(exp1, exp2) 函數(shù)用于將指定值轉(zhuǎn)換為 NULL。當(dāng) exp1 等于 exp2 時(shí)，返回 NULL；否則，返回 exp1 。NULLIF 最常見(jiàn)的用途是防止除零錯(cuò)誤，例如：

SELECT 1 / NULLIF(0, 0) – 1 / 0
FROM employees
WHERE employee_id = 100;

示例中的 NULLIF 將第一個(gè)零轉(zhuǎn)換為 NULL，因此查詢結(jié)果返回 NULL；如果直接使用 1 / 0，查詢將會(huì)返回除零錯(cuò)誤。MySQL 中的除零錯(cuò)誤由 sql_mode 變量控制。

NULLIF 函數(shù)同樣可以使用 CASE 表達(dá)式改寫(xiě)如下：

CASE WHEN exp1 = exp2 THEN NULL
ELSE exp1
END

利用 CASE 表達(dá)式，我們還可以輕松實(shí)現(xiàn)多個(gè)值到 NULL 的轉(zhuǎn)換：

CASE WHEN expr IN (value1, value2, …)
THEN NULL
ELSE expr
END

??COALESCE 和 NULLIF 實(shí)際上是 CASE 表達(dá)式的兩種縮寫(xiě)形式。

除了標(biāo)準(zhǔn) SQL 函數(shù)之外，數(shù)據(jù)庫(kù)還提供了一些專用的函數(shù)：

Oracle 中的 NVL(expr1, expr2) 相當(dāng)于 2 個(gè)參數(shù)的 COALESCE。另外，NVL2(expr1, expr2, expr3) 如果第一個(gè)參數(shù)不為空，返回第二個(gè)參數(shù)的值；否則，返回第三個(gè)參數(shù)的值；
MySQL 中的 IFNULL(expr1, expr2) 相當(dāng)于 2 個(gè)參數(shù)的 COALESCE。另外，IF(expr1, expr2, expr3) 如果第一個(gè)參數(shù)為真（expr1 <> 0 并且 expr1 不為空），返回第二個(gè)參數(shù)的值；否則，返回第三個(gè)參數(shù)的值；
SQL Server 中的 ISNULL(expr1, expr2) 相當(dāng)于 2 個(gè)參數(shù)的 COALESCE。

字段約束與空值

如果不允許字段中存在未知或者缺失的數(shù)據(jù)，可以使用字段的 NOT NULL 約束。

對(duì)于唯一約束（UNIQUE），多個(gè) NULL 被看作是不同的值；因此，唯一約束字段中可以存在多個(gè)空值。不過(guò) SQL Server 是個(gè)例外：

CREATE TABLE t_unique(id INT UNIQUE);
INSERT INTO t_unique VALUES(1);
INSERT INTO t_unique VALUES(NULL);
INSERT INTO t_unique VALUES(NULL); – SQL Server 產(chǎn)生唯一鍵沖突錯(cuò)誤

– SQL Server 除外
SELECT * FROM t_unique;
id|
------|
[NULL]|
[NULL]|
1|

– SQL Server 除外
SELECT * FROM t_unique;
id|
------|
[NULL]|
1|

對(duì)于 SQL Server 而言，唯一約束中只允許存在一個(gè) NULL 數(shù)據(jù)；所以第 3 個(gè) INSERT 語(yǔ)句執(zhí)行出錯(cuò)，最終只有兩條記錄。

如果是復(fù)合索引，情況略有不同：

CREATE TABLE t_unique2(c1 INT, c2 INT, UNIQUE(c1,c2));

INSERT INTO t_unique2 VALUES(1, 1);
INSERT INTO t_unique2 VALUES(NULL, NULL);
INSERT INTO t_unique2 VALUES(NULL, NULL); – SQL Server 產(chǎn)生唯一鍵沖突錯(cuò)誤
INSERT INTO t_unique2 VALUES(1, NULL);
INSERT INTO t_unique2 VALUES(1, NULL); – Oracle 和 SQL Server 產(chǎn)生唯一鍵沖突錯(cuò)誤

其中，SQL Server 只允許有一個(gè)記錄的全部索引字段為空；如果某個(gè)字段不為空，Oracle 和 SQL Server 只允許有一個(gè)記錄的其他索引字段為空。

另外，檢查約束（CHECK）對(duì)于 NULL 的處理與 WHERE 條件正好相反：只要數(shù)據(jù)的檢查結(jié)果不是假都可以插入成功。例如：

CREATE TABLE t_check (
c1 INT CHECK (c1 >= 0),
c2 INT CHECK (c2 >= 0),
CHECK (c1 + c2 <= 100)
);

INSERT INTO t_check VALUES (5, 5);
INSERT INTO t_check VALUES (NULL, NULL);
INSERT INTO t_check VALUES (200, NULL);

SELECT * FROM t_check;

 5|     5|

[NULL]|[NULL]|
200|[NULL]|

如果 c1 和 c2 都有值的話，都必須大于等于零并且和值小于等于 100；c1 和 c2 都可以為空；如果其中之一為空，另一個(gè)字段的值可以大于 100。