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

作者：youzhibing2904

?

NULL

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

兩種NULL

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

以“不知道戴墨鏡的人眼睛是什么顏色”這種情況為例，這個(gè)人的眼睛肯定是有顏色的，但是如果他不摘掉眼鏡，別人就不知道他的眼睛是什么顏色。這就叫作未知。

而“不知道冰箱的眼睛是什么顏色”則屬于“不適用”。因?yàn)楸涓揪蜎]有眼睛，所以“眼睛的顏色”這一屬性并不適用于冰箱。“冰箱的眼睛的顏色”這種說法和“圓的體積”“男性的分娩次數(shù)”一樣，都是沒有意義的。

平時(shí)，我們習(xí)慣了說“不知道”，但是“不知道”也分很多種。“不適用”這種情況下的 NULL ，在語義上更接近于“無意義”，而不是“不確定”。

這里總結(jié)一下：“未知”指的是“雖然現(xiàn)在不知道，但加上某些條件后就可以知道”;而“不適用”指的是“無論怎么努力都無法知道”。

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

“IS NULL”而非“= NULL”?

為什么必須寫成“IS NULL”，而不是“= NULL”?我相信不少人有這樣的困惑吧，尤其是相信剛學(xué) SQL 的小伙伴。我們來看個(gè)具體的案例，假設(shè)我們有如下表以及數(shù)據(jù)：

DROP TABLE IF EXISTS t_sample_null; CREATE TABLE t_sample_null ( id INT(11) unsigned NOT 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 這種叫法本身是不對的，只是我們?nèi)粘Ｖ幸呀?jīng)叫習(xí)慣了，具體往下看)，怎么查，很多新手會(huì)寫出這樣的 SQL：

- SQL 不報(bào)錯(cuò)，但查不出結(jié)果 SELECT * FROM t_sample_null WHERE remark = NULL;

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

三值邏輯

這個(gè)三值邏輯不是三目運(yùn)算，指的是三個(gè)邏輯值，有人可能有疑問了，邏輯值不是只有真(true)和假(false)嗎，哪來的第三個(gè)?

說這話時(shí)我們需要注意所處的環(huán)境，在主流的編程語言中(C、JAVA、Python、JS等)中，邏輯值確實(shí)只有 2 個(gè)，但在 SQL 中卻存在第三個(gè)邏輯值：unknown。這有點(diǎn)類似于我們平時(shí)所說的：對、錯(cuò)、不知道。

邏輯值 unknown 和作為 NULL 的一種的 UNKNOWN (未知)是不同的東西。前者是明確的布爾型的邏輯值，后者既不是值也不是變量。

為了便于區(qū)分，前者采用小寫字母 unknown ，后者用大寫字母 UNKNOWN 來表示。為了讓大家理解兩者的不同，我們來看一個(gè) x=x 這樣的簡單等式。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)算，這在二值邏輯中是沒有的。其余的 SQL 謂詞全部都能由這三個(gè)邏輯運(yùn)算組合而來。從這個(gè)意義上講，這個(gè)幾個(gè)邏輯表可以說是 SQL 的母體(matrix)。

NOT 的話，因?yàn)檫壿嬛当肀容^簡單，所以很好記;但是對于 AND 和 OR，因?yàn)榻M合出來的邏輯值較多，所以全部記住非常困難。為了便于記憶，請注意這三個(gè)邏輯值之間有下面這樣的優(yōu)先級順序：

• AND 的情況：false > unknown > true;
• OR 的情況：true > unknown > false。

優(yōu)先級高的邏輯值會(huì)決定計(jì)算結(jié)果。例如 true AND unknown ，因?yàn)?unknown 的優(yōu)先級更高，所以結(jié)果是 unknown 。而 true OR unknown 的話，因?yàn)?true 優(yōu)先級更高，所以結(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”

我們再回到問題：為什么必須寫成“IS NULL”，而不是“= NULL”?

對 NULL 使用比較謂詞后得到的結(jié)果總是 unknown 。而查詢結(jié)果只會(huì)包含 WHERE 子句里的判斷結(jié)果為 true 的行，不會(huì)包含判斷結(jié)果為 false 和 unknown 的行。不只是等號(hào)，對 NULL 使用其他比較謂詞，結(jié)果也都是一樣的。

所以無論 remark 是不是 NULL ，比較結(jié)果都是 unknown ，那么永遠(yuǎn)沒有結(jié)果返回。以下的式子都會(huì)被判為 unknown：

-- 以下的式子都會(huì)被判為 unknown

-- 以下的式子都會(huì)被判為 unknown = NULL > NULL < NULL <> NULL NULL = NULL

那么，為什么對 NULL 使用比較謂詞后得到的結(jié)果永遠(yuǎn)不可能為真呢?

這是因?yàn)?#xff0c;NULL 既不是值也不是變量。NULL 只是一個(gè)表示“沒有值”的標(biāo)記，而比較謂詞只適用于值。

因此，對并非值的 NULL 使用比較謂詞本來就是沒有意義的。“列的值為 “NULL ”、“NULL 值” 這樣的說法本身就是錯(cuò)誤的。因?yàn)?NULL不是值，所以不在定義域(domain)中。

相反，如果有人認(rèn)為 NULL 是值，那么我們可以倒過來想一下：它是什么類型的值?關(guān)系數(shù)據(jù)庫中存在的值必然屬于某種類型，比如字符型或數(shù)值型等。所以，假如 NULL 是值，那么它就必須屬于某種類型。

NULL 容易被認(rèn)為是值的原因有兩個(gè)。

第一個(gè)是高級編程語言里面，NULL 被定義為了一個(gè)常量(很多語言將其定義為了整數(shù)0)，這導(dǎo)致了我們的混淆。但是，SQL 里的 NULL 和其他編程語言里的 NULL 是完全不同的東西。

第二個(gè)原因是，IS NULL 這樣的謂詞是由兩個(gè)單詞構(gòu)成的，所以我們?nèi)菀装?IS 當(dāng)作謂詞，而把 NULL 當(dāng)作值。特別是 SQL 里還有 IS TRUE 、IS FALSE 這樣的謂詞，我們由此類推，從而這樣認(rèn)為也不是沒有道理。但是正如講解標(biāo)準(zhǔn) SQL 的書里提醒人們注意的那樣，我們應(yīng)該把 IS NULL 看作是一個(gè)謂詞。因此，寫成 IS_NULL 這樣也許更合適。

溫柔的陷阱

比較謂詞和 NULL

排中律不成立。排中律指同一個(gè)思維過程中，兩個(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 '名稱', age INT(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;

表中數(shù)據(jù) yzb 的 age 是 NULL，也就是說 yzb 的年齡未知。在現(xiàn)實(shí)世界里，yzb 是 20 歲，或者不是 20 歲，二者必居其一，這毫無疑問是一個(gè)真命題。那么在 SQL 的世界里了，排中律還適用嗎? 我們來看一個(gè) SQL ：

SELECT * FROM t_student WHERE age = 20 OR age <> 20;

咋一看，這不就是查詢表中全部記錄嗎?我們來看下實(shí)際結(jié)果：

yzb 沒查出來，這是為什么?我們來分析下，yzb 的 age 是 NULL，那么這條記錄的判斷步驟如下：

-- 1. 約翰年齡是 NULL (未知的 NULL !) SELECT * FROM t_student WHERE age = NULL OR age <> NULL; -- 2. 對 NULL 使用比較謂詞后，結(jié)果為unknown SELECT * FROM t_student WHERE unknown OR unknown; -- 3．unknown OR unknown 的結(jié)果是unknown (參考三值邏輯的邏輯值表) SELECT * FROM t_student WHERE unknown;

SQL 語句的查詢結(jié)果里只有判斷結(jié)果為 true 的行。要想讓 yzb 出現(xiàn)在結(jié)果里，需要添加下面這樣的 “第 3 個(gè)條件”：

-- 添加 3 個(gè)條件：年齡是20 歲，或者不是20 歲，或者年齡未知。

-- 添加 3 個(gè)條件：年齡是20 歲，或者不是20 歲，或者年齡未知 SELECT * FROM t_student WHERE age = 20 OR age <> 20 OR age IS NULL; CASE 表達(dá)式和 NULL。簡單 CASE 表達(dá)式如下：CASE col_1 WHEN = 1 THEN 'o' WHEN NULL THEN 'x' END

這個(gè) CASE 表達(dá)式一定不會(huì)返回 ×。這是因?yàn)?#xff0c;第二個(gè) WHEN 子句是 col_1 = NULL 的縮寫形式。正如我們所知，這個(gè)式子的邏輯值永遠(yuǎn)是 unknown ，而且 CASE 表達(dá)式的判斷方法與 WHERE 子句一樣，只認(rèn)可邏輯值為 true 的條件。正確的寫法是像下面這樣使用搜索 CASE 表達(dá)式：

CASE WHEN col_1 = 1 THEN 'o' WHEN col_1 IS NULL THEN 'x' END

NOT IN 和 NOT EXISTS 并非等價(jià)

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

我們來看個(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) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', name VARCHAR(50) NOT NULL COMMENT '名稱', age INT(3) COMMENT '年齡', city VARCHAR(50) NOT NULL COMMENT '城市', remark VARCHAR(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) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', name VARCHAR(50) NOT NULL COMMENT '名稱', age INT(3) COMMENT '年齡', city VARCHAR(50) NOT NULL COMMENT '城市', remark VARCHAR(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;

需求：查詢與 A 班住在深圳的學(xué)生年齡不同的 B 班學(xué)生，也就說查詢出 ：馬化騰 和 李彥宏，這個(gè) SQL 該如何寫，像這樣?

-- 查詢與 A 班住在深圳的學(xué)生年齡不同的 B 班學(xué)生 ? SELECT * FROM t_student_B WHERE age NOT IN ( SELECT age FROM t_student_A WHERE city = '深圳市' );

我們來看下執(zhí)行結(jié)果：

我們發(fā)現(xiàn)結(jié)果是空，查詢不到任何數(shù)據(jù)，這是為什么 ?這里 NULL 又開始作怪了，我們一步一步來看看究竟發(fā)生了什么。

-- 1. 執(zhí)行子查詢，獲取年齡列表 SELECT * FROM t_student WHERE age NOT IN(43, NULL, 25); -- 2. 用NOT 和IN 等價(jià)改寫NOT IN SELECT * FROM t_student WHERE NOT age IN (43, NULL, 25); -- 3. 用OR 等價(jià)改寫謂詞IN SELECT * FROM t_student WHERE NOT ( (age = 43) OR (age = NULL) OR (age = 25) ); -- 4. 使用德· 摩根定律等價(jià)改寫 SELECT * FROM t_student WHERE NOT (age = 43) AND NOT(age = NULL) AND NOT (age = 25); -- 5. 用<> 等價(jià)改寫 NOT 和 = SELECT * FROM t_student WHERE (age <> 43) AND (age <> NULL) AND (age <> 25); -- 6. 對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;

可以看出，在進(jìn)行了一系列的轉(zhuǎn)換后，沒有一條記錄在 WHERE 子句里被判斷為 true 。也就是說，如果 NOT IN 子查詢中用到的表里被選擇的列中存在 NULL ，則 SQL 語句整體的查詢結(jié)果永遠(yuǎn)是空。這是很可怕的現(xiàn)象!

為了得到正確的結(jié)果，我們需要使用 EXISTS 謂詞。

-- 正確的SQL 語句：馬化騰和李彥宏將被查詢到 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é)果如下：

同樣地，我們再來一步一步地看看這段 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. 對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. 子查詢沒有返回結(jié)果，因此相反地，NOT EXISTS 為 true SELECT * FROM t_student_B B WHERE true;

也就是說，yzb 被作為 “與任何人的年齡都不同的人” 來處理了。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ù)，不是某種具體類型的值，不能對其使用謂詞。

2、對 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 帶來的各種問題，最佳方法應(yīng)該是往表里添加 NOT NULL 約束來盡力排除 NULL。

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

閱讀目錄（置頂)(長期更新計(jì)算機(jī)領(lǐng)域知識(shí)）

閱讀目錄（置頂)(長期更新計(jì)算機(jī)領(lǐng)域知識(shí)）

閱讀目錄（置頂)(長期科技領(lǐng)域知識(shí)）

歌謠帶你看java面試題

總結(jié)

以上是生活随笔為你收集整理的第一百零四期:搞清这些陷阱，NULL和三值逻辑再也不作妖的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。