神奇的 SQL 之溫柔的陷阱 → 三值邏輯 與 NULL ！

前言

　　開(kāi)心一刻　
?　　　　一個(gè)中國(guó)小孩參加國(guó)外的脫口秀節(jié)目，因?yàn)檎Z(yǔ)言不通，于是找了一個(gè)翻譯。
　　　　主持人問(wèn)：“Who?is?your?favorite?singer ？”
　　　　翻譯：”你最喜歡哪個(gè)歌手啊 ？”
　　　　小孩興奮地回答：”Michael Jackson”
　　　　翻譯轉(zhuǎn)身對(duì)主持人說(shuō)：”邁克爾-杰克遜”
　　　　主持人看著翻譯："你說(shuō)什么 ？" 　　　　電視機(jī)前的觀眾："我怎么有點(diǎn)蒙？"?
NULL

　　NULL 用于表示缺失的值或遺漏的未知數(shù)據(jù)，不是某種具體類型的值。數(shù)據(jù)表中的 NULL 值表示該值所處的字段為空，值為 NULL
的字段沒(méi)有值，尤其要明白的是：NULL 值與 0 或者空字符串是不同的。

　　兩種 NULL

　　　　這種說(shuō)法大家可能會(huì)覺(jué)得很奇怪，因?yàn)?SQL 里只存在一種 NULL 。然而在討論 NULL
時(shí)，我們一般都會(huì)將它分成兩種類型來(lái)思考：“未知”（unknown）和“不適用”（not applicable,inapplicable）。


　　　　以“不知道戴墨鏡的人眼睛是什么顏色”這種情況為例，這個(gè)人的眼睛肯定是有顏色的，但是如果他不摘掉眼鏡，別人就不知道他的眼睛是什么顏色。這就叫作未知。而“不知道冰箱的眼睛是什么顏色”則屬于“不適用”。因?yàn)楸涓揪蜎](méi)有眼睛，所以“眼睛的顏色”這一屬性并不適用于冰箱?！氨涞难劬Φ念伾边@種說(shuō)法和“圓的體積”“男性的分娩次數(shù)”一樣，都是沒(méi)有意義的。平時(shí)，我們習(xí)慣了說(shuō)“不知道”，但是“不知道”也分很多種。“不適用”這種情況下的
NULL
，在語(yǔ)義上更接近于“無(wú)意義”，而不是“不確定”。這里總結(jié)一下：“未知”指的是“雖然現(xiàn)在不知道，但加上某些條件后就可以知道”；而“不適用”指的是“無(wú)論怎么努力都無(wú)法知道”。

　　　　關(guān)系模型的發(fā)明者 E.F. Codd 最先給出了這種分類。下圖是他對(duì)“丟失的信息”的分類



　　為什么必須寫(xiě)成“IS NULL”，而不是“＝ NULL”

　　　　我相信不少人有這樣的困惑吧，尤其是相信剛學(xué) SQL 的小伙伴。我們來(lái)看個(gè)具體的案例，假設(shè)我們有如下表以及數(shù)據(jù)
DROP TABLE IF EXISTS t_sample_null; CREATE TABLE t_sample_null ( id INT(11)
unsignedNOT NULL AUTO_INCREMENT COMMENT '自增主鍵', name VARCHAR(50) NOT NULL
COMMENT'名稱', remark VARCHAR(500) COMMENT '備注', primary key(id) ) COMMENT 'NULL樣例
'; INSERT INTO t_sample_null(name, remark) VALUES('zhangsan', '張三'),('李四', NULL
);
　　　　我們要查詢備注為 NULL 的記錄（為 NULL 這種叫法本身是不對(duì)的，只是我們?nèi)粘Ｖ幸呀?jīng)叫習(xí)慣了，具體往下看），怎么查，很多新手會(huì)寫(xiě)出這樣的 SQL
-- SQL 不報(bào)錯(cuò)，但查不出結(jié)果 SELECT * FROM t_sample_null WHERE remark = NULL;


　　　　執(zhí)行時(shí)不報(bào)錯(cuò)，但是查不出我們想要的結(jié)果， 這是為什么了 ？ 這個(gè)問(wèn)題我們先放著，我們往下看

三值邏輯

　　這個(gè)三值邏輯不是三目運(yùn)算，指的是三個(gè)邏輯值，有人可能有疑問(wèn)了，邏輯值不是只有真(true)和假(false)嗎，哪來(lái)的第三個(gè)？
說(shuō)這話時(shí)我們需要注意所處的環(huán)境，在主流的編程語(yǔ)言中（C、JAVA、Python、JS等）中，邏輯值確實(shí)只有 2 個(gè)，但在 SQL
中卻存在第三個(gè)邏輯值：unknown。這有點(diǎn)類似于我們平時(shí)所說(shuō)的：對(duì)、錯(cuò)、不知道。

　　邏輯值 unknown 和作為 NULL 的一種的 UNKNOWN
（未知）是不同的東西。前者是明確的布爾型的邏輯值，后者既不是值也不是變量。為了便于區(qū)分，前者采用小寫(xiě)字母 unknown ，后者用大寫(xiě)字母 UNKNOWN
來(lái)表示。為了讓大家理解兩者的不同，我們來(lái)看一個(gè) x=x 這樣的簡(jiǎn)單等式。x 是邏輯值 unknown 時(shí)，x=x 被判斷為 true ，而 x 是
UNKNOWN 時(shí)被判斷為 unknown?
-- 這個(gè)是明確的邏輯值的比較 unknown = unknown → true -- 這個(gè)相當(dāng)于NULL = NULL UNKNOWN = UNKNOWN
→ unknown
?　　三值邏輯的邏輯值表

　　　　NOT



　　　　AND



　　　　OR



　　　　圖中藍(lán)色部分是三值邏輯中獨(dú)有的運(yùn)算，這在二值邏輯中是沒(méi)有的。其余的 SQL
謂詞全部都能由這三個(gè)邏輯運(yùn)算組合而來(lái)。從這個(gè)意義上講，這個(gè)幾個(gè)邏輯表可以說(shuō)是 SQL 的母體（matrix）。
　　　　NOT 的話，因?yàn)檫壿嬛当肀容^簡(jiǎn)單，所以很好記；但是對(duì)于 AND 和
OR，因?yàn)榻M合出來(lái)的邏輯值較多，所以全部記住非常困難。為了便于記憶，請(qǐng)注意這三個(gè)邏輯值之間有下面這樣的優(yōu)先級(jí)順序。
　　　　　　AND 的情況： false ＞ unknown ＞ true
　　　　　　OR 的情況： true ＞ unknown ＞ false

　　　　優(yōu)先級(jí)高的邏輯值會(huì)決定計(jì)算結(jié)果。例如 true AND unknown ，因?yàn)?unknown 的優(yōu)先級(jí)更高，所以結(jié)果是 unknown 。而
true OR unknown 的話，因?yàn)?true 優(yōu)先級(jí)更高，所以結(jié)果是 true 。記住這個(gè)順序后就能更方便地進(jìn)行三值邏輯運(yùn)算了。特別需要記住的是，當(dāng)
AND 運(yùn)算中包含 unknown 時(shí)，結(jié)果肯定不會(huì)是 true （反之，如果AND 運(yùn)算結(jié)果為 true ，則參與運(yùn)算的雙方必須都為 true ）。
-- 假設(shè) a = 2, b = 5, c = NULL，下列表達(dá)式的邏輯值如下 a < b AND b > c → unknown a > b OR b
< c → unknown a < b OR b < c → true NOT (b <> c) → unknown
　　“IS NULL” 而非 “= NULL”

　　　　我們?cè)倩氐絾?wèn)題：為什么必須寫(xiě)成“IS NULL”，而不是“＝ NULL”

　　　　對(duì) NULL 使用比較謂詞后得到的結(jié)果總是 unknown 。而查詢結(jié)果只會(huì)包含 WHERE 子句里的判斷結(jié)果為 true 的行，不會(huì)包含判斷結(jié)果為
false 和 unknown 的行。不只是等號(hào)，對(duì) NULL 使用其他比較謂詞，結(jié)果也都是一樣的。所以無(wú)論?remark 是不是 NULL ，比較結(jié)果都是
unknown ，那么永遠(yuǎn)沒(méi)有結(jié)果返回。以下的式子都會(huì)被判為 unknown
-- 以下的式子都會(huì)被判為 unknown = NULL > NULL < NULL <> NULL NULL = NULL
　　　　那么，為什么對(duì) NULL 使用比較謂詞后得到的結(jié)果永遠(yuǎn)不可能為真呢？這是因?yàn)?，NULL 既不是值也不是變量。NULL
只是一個(gè)表示“沒(méi)有值”的標(biāo)記，而比較謂詞只適用于值。因此，對(duì)并非值的 NULL 使用比較謂詞本來(lái)就是沒(méi)有意義的。“列的值為 NULL ”、“NULL 值”
這樣的說(shuō)法本身就是錯(cuò)誤的。因?yàn)?NULL不是值，所以不在定義域（domain）中。相反，如果有人認(rèn)為 NULL
是值，那么我們可以倒過(guò)來(lái)想一下：它是什么類型的值？關(guān)系數(shù)據(jù)庫(kù)中存在的值必然屬于某種類型，比如字符型或數(shù)值型等。所以，假如 NULL
是值，那么它就必須屬于某種類型。

　　　　NULL 容易被認(rèn)為是值的原因有兩個(gè)。第一個(gè)是高級(jí)編程語(yǔ)言里面，NULL
被定義為了一個(gè)常量（很多語(yǔ)言將其定義為了整數(shù)0），這導(dǎo)致了我們的混淆。但是，SQL 里的 NULL 和其他編程語(yǔ)言里的 NULL
是完全不同的東西。第二個(gè)原因是，IS NULL 這樣的謂詞是由兩個(gè)單詞構(gòu)成的，所以我們?nèi)菀装?IS 當(dāng)作謂詞，而把 NULL 當(dāng)作值。特別是 SQL 里還有
IS TRUE 、IS FALSE 這樣的謂詞，我們由此類推，從而這樣認(rèn)為也不是沒(méi)有道理。但是正如講解標(biāo)準(zhǔn) SQL 的書(shū)里提醒人們注意的那樣，我們應(yīng)該把IS
NULL 看作是一個(gè)謂詞。因此，寫(xiě)成 IS_NULL 這樣也許更合適。

溫柔的陷阱

　　比較謂詞和 NULL

　　　　排中律不成立

　　　　　　排中律指同一個(gè)思維過(guò)程中，兩個(gè)相互矛盾的思想不能同假，必有一真，即“要么A要么非A”

　　　　　　假設(shè)我們有學(xué)生表：t_student
DROP TABLE IF EXISTS t_student; CREATE TABLE t_student ( id INT(11) unsigned
NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', name VARCHAR(50) NOT NULL COMMENT '名稱',
ageINT(3) COMMENT '年齡', remark VARCHAR(500) NOT NULL DEFAULT '' COMMENT '備注',
primary key(id) ) COMMENT '學(xué)生信息'; INSERT INTO t_student(name, age) VALUE('
zhangsan', 25),('wangwu', 60),('bruce', 32),('yzb', NULL),('boss', 18); SELECT *
FROM t_student; View Code
　　　　　　表中數(shù)據(jù) yzb 的 age 是 NULL，也就是說(shuō) yzb 的年齡未知。在現(xiàn)實(shí)世界里，yzb 是 20 歲，或者不是 20
歲，二者必居其一，這毫無(wú)疑問(wèn)是一個(gè)真命題。那么在 SQL 的世界里了，排中律還適用嗎? 我們來(lái)看一個(gè) SQL?
SELECT * FROM t_student WHERE age = 20 OR age <> 20;
　　　　　　咋一看，這不就是查詢表中全部記錄嗎？ 我們來(lái)看下實(shí)際結(jié)果

　　　　　　yzb 沒(méi)查出來(lái)，這是為什么了？我們來(lái)分析下，yzb 的 age 是 NULL，那么這條記錄的判斷步驟如下
-- 1. 約翰年齡是 NULL (未知的 NULL !) SELECT * FROM t_student WHERE age = NULL OR age
<> NULL; -- 2. 對(duì) NULL 使用比較謂詞后，結(jié)果為unknown SELECT * FROM t_student WHERE unknown
OR unknown; -- 3．unknown OR unknown 的結(jié)果是unknown (參考三值邏輯的邏輯值表) SELECT * FROM
t_studentWHERE unknown;
　　　　　　SQL 語(yǔ)句的查詢結(jié)果里只有判斷結(jié)果為 true 的行。要想讓 yzb 出現(xiàn)在結(jié)果里，需要添加下面這樣的 “第 3 個(gè)條件”
-- 添加 3 個(gè)條件：年齡是20 歲，或者不是20 歲，或者年齡未知 SELECT * FROM t_student WHERE age = 20 OR
age<> 20 OR age IS NULL;
　　　　CASE 表達(dá)式和 NULL

　　　　　　簡(jiǎn)單 CASE 表達(dá)式如下
CASE col_1 WHEN = 1 THEN 'o' WHEN NULL THEN 'x' END
　　　　　　這個(gè) CASE 表達(dá)式一定不會(huì)返回 ×。這是因?yàn)椋诙€(gè) WHEN 子句是 col_1 = NULL
的縮寫(xiě)形式。正如我們所知，這個(gè)式子的邏輯值永遠(yuǎn)是 unknown ，而且 CASE 表達(dá)式的判斷方法與 WHERE 子句一樣，只認(rèn)可邏輯值為 true
的條件。正確的寫(xiě)法是像下面這樣使用搜索 CASE 表達(dá)式
CASE WHEN col_1 = 1 THEN 'o' WHEN col_1 IS NULL THEN 'x' END
　　NOT IN 和 NOT EXISTS 不是等價(jià)的

　　　　我們?cè)趯?duì) SQL 語(yǔ)句進(jìn)行性能優(yōu)化時(shí)，經(jīng)常用到的一個(gè)技巧是將 IN 改寫(xiě)成 EXISTS ，這是等價(jià)改寫(xiě)，并沒(méi)有什么問(wèn)題。但是，將 NOT IN
改寫(xiě)成 NOT EXISTS 時(shí)，結(jié)果未必一樣。

　　　　我們來(lái)看個(gè)例子，我們有如下兩張表：t_student_A 和 t_student_B，分別表示 A 班學(xué)生與 B 班學(xué)生?
DROP TABLE IF EXISTS t_student_A; CREATE TABLE t_student_A ( id INT(11)
unsignedNOT NULL AUTO_INCREMENT COMMENT '自增主鍵', name VARCHAR(50) NOT NULL
COMMENT'名稱', age INT(3) COMMENT '年齡', city VARCHAR(50) NOT NULL COMMENT '城市',
remarkVARCHAR(500) NOT NULL DEFAULT '' COMMENT '備注', primary key(id) ) COMMENT '
學(xué)生信息'; INSERT INTO t_student_A(name, age, city) VALUE ('zhangsan', 25,'深圳市'),('
wangwu', 60, '廣州市'), ('bruce', 32, '北京市'),('yzb', NULL, '深圳市'), ('boss', 43, '
深圳市'); DROP TABLE IF EXISTS t_student_B; CREATE TABLE t_student_B ( id INT(11)
unsignedNOT NULL AUTO_INCREMENT COMMENT '自增主鍵', name VARCHAR(50) NOT NULL
COMMENT'名稱', age INT(3) COMMENT '年齡', city VARCHAR(50) NOT NULL COMMENT '城市',
remarkVARCHAR(500) NOT NULL DEFAULT '' COMMENT '備注', primary key(id) ) COMMENT '
學(xué)生信息'; INSERT INTO t_student_B(name, age, city) VALUE ('馬化騰', 45, '深圳市'),('馬三',
25, '深圳市'), ('馬云', 43, '杭州市'),('李彥宏', 41, '深圳市'), ('年輕人', 25, '深圳市'); SELECT *
FROM t_student_A; SELECT * FROM t_student_B; View Code
　　　　需求：查詢與?A? 班住在深圳的學(xué)生年齡不同的 B 班學(xué)生，也就說(shuō)查詢出 ：馬化騰 和 李彥宏，這個(gè) SQL 該如何寫(xiě)，像這樣？
-- 查詢與 A 班住在深圳的學(xué)生年齡不同的 B 班學(xué)生 ? SELECT * FROM t_student_B WHERE age NOT IN (
SELECT age FROM t_student_A WHERE city = '深圳市' );
　　　　我們來(lái)看下執(zhí)行結(jié)果



　　　　我們發(fā)現(xiàn)結(jié)果是空，查詢不到任何數(shù)據(jù)，這是為什么了 ？這里 NULL 又開(kāi)始作怪了，我們一步一步來(lái)看看究竟發(fā)生了什么
-- 1. 執(zhí)行子查詢，獲取年齡列表 SELECT * FROM t_student WHERE age NOT IN(43, NULL, 25); --
2. 用NOT 和IN 等價(jià)改寫(xiě)NOT IN SELECT * FROM t_student WHERE NOT age IN (43, NULL, 25);
-- 3. 用OR 等價(jià)改寫(xiě)謂詞IN SELECT * FROM t_student WHERE NOT ( (age = 43) OR (age = NULL
)OR (age = 25) ); -- 4. 使用德· 摩根定律等價(jià)改寫(xiě) SELECT * FROM t_student WHERE NOT (age =
43) AND NOT(age = NULL) AND NOT (age = 25); -- 5. 用<> 等價(jià)改寫(xiě) NOT 和 = SELECT * FROM
t_studentWHERE (age <> 43) AND (age <> NULL) AND (age <> 25); -- 6. 對(duì)NULL 使用<>
后，結(jié)果為 unknown SELECT * FROM t_student WHERE (age <> 43) AND unknown AND (age <>
25); -- 7．如果 AND 運(yùn)算里包含 unknown，則結(jié)果不為true（參考三值邏輯的邏輯值表） SELECT * FROM t_student
WHERE false 或 unknown; View Code
　　　　可以看出，在進(jìn)行了一系列的轉(zhuǎn)換后，沒(méi)有一條記錄在 WHERE 子句里被判斷為 true 。也就是說(shuō)，如果 NOT IN
子查詢中用到的表里被選擇的列中存在 NULL ，則 SQL 語(yǔ)句整體的查詢結(jié)果永遠(yuǎn)是空。這是很可怕的現(xiàn)象！

　　　　為了得到正確的結(jié)果，我們需要使用 EXISTS 謂詞
-- 正確的SQL 語(yǔ)句：馬化騰和李彥宏將被查詢到 SELECT * FROM t_student_B B WHERE NOT EXISTS ( SELECT
* FROM t_student_A A WHERE B.age = A.age AND A.city = '深圳市' );
　　　　執(zhí)行結(jié)果如下



　　　　同樣地，我們?cè)賮?lái)一步一步地看看這段 SQL 是如何處理年齡為 NULL 的行的
-- 1. 在子查詢里和 NULL 進(jìn)行比較運(yùn)算，此時(shí) A.age 是 NULL SELECT * FROM t_student_B B WHERE NOT
EXISTS ( SELECT * FROM t_student_A A WHERE B.age = NULL AND A.city = '深圳市' ); --
2. 對(duì)NULL 使用“=”后，結(jié)果為 unknown SELECT * FROM t_student_B B WHERE NOT EXISTS (
SELECT * FROM t_student_A A WHERE unknown AND A.city = '深圳市' ); -- 3. 如果AND
運(yùn)算里包含 unknown，結(jié)果不會(huì)是true SELECT * FROM t_student_B B WHERE NOT EXISTS ( SELECT *
FROM t_student_A A WHERE false 或 unknown ); -- 4. 子查詢沒(méi)有返回結(jié)果，因此相反地，NOT EXISTS 為
true SELECT * FROM t_student_B B WHERE true; View Code
　　　　也就是說(shuō)，yzb 被作為 “與任何人的年齡都不同的人” 來(lái)處理了。EXISTS 只會(huì)返回 true 或者false，永遠(yuǎn)不會(huì)返回
unknown。因此就有了 IN 和 EXISTS 可以互相替換使用，而 NOT IN和 NOT EXISTS 卻不可以互相替換的混亂現(xiàn)象。

　　還有一些其他的陷阱，比如：限定謂詞和 NULL、限定謂詞和極值函數(shù)不是等價(jià)的、聚合函數(shù)和 NULL 等等。

總結(jié)

　　1、NULL 用于表示缺失的值或遺漏的未知數(shù)據(jù)，不是某種具體類型的值，不能對(duì)其使用謂詞

　　2、對(duì) NULL 使用謂詞后的結(jié)果是 unknown，unknown 參與到邏輯運(yùn)算時(shí)，SQL 的運(yùn)行會(huì)和預(yù)想的不一樣

　　3、 IS NULL 整個(gè)是一個(gè)謂詞，而不是：IS 是謂詞，NULL 是值；類似的還有 IS?TRUE、IS FALSE

　　4、要想解決 NULL 帶來(lái)的各種問(wèn)題，最佳方法應(yīng)該是往表里添加 NOT NULL 約束來(lái)盡力排除 NULL

　　　　我的項(xiàng)目中有個(gè)硬性規(guī)定：所有字段必須是 NOT NULL，建表的時(shí)候就加上此約束

參考

　　《SQL進(jìn)階教程》

• ? 上一篇：破解點(diǎn)評(píng)網(wǎng)字體反爬，深入挖掘系統(tǒng)背后的原理
• ? 下一篇：Vue中拆分視圖層代碼的5點(diǎn)建議