SQL 語句中關於 NULL 的那些坑

問題描述

今天在跟進公司內部測試平臺線上問題的時候，發現一個忽略已久的問題。

為了簡化問題描述，將其進行了抽象。

有一張資料表qms_branch，裡面包含了一批形式如下所示的資料：

id	name	types
1	dashboard_trunk	dashboard
2	monkey_trunk	monkey
3	dashboard_projects_10_9_9	dashboard
4	performance_trunk
5	performance_projects_10_9_8	performance

在系統的某個頁面中，需要展示出所有dashboard型別以外的分支，於是就採用如下方式進行查詢（Rails）。

branches = Qms::Branch.where("types!='dashboard'")

這個方式有問題麼？

之前我是覺得沒什麼問題。但是在程式碼上線後，實際使用時發現部分分支沒有載入出來，這就包括了performance_trunk分支。

然後就是問題定位，到 MySQL 的控制檯採用 SQL 語句進行查詢：

SELECT * FROM qms_branch WHERE types != 'dashboard'

發現在查詢結果中的確沒有包含performance_trunk分支。

這是什麼原因呢？為什麼在第 4 條資料中，types屬性的值明明就不是dashboard，但是採用types!='dashboard'就無法查詢得到結果呢？

原因追溯

檢視資料表qms_branch的結構，看到types欄位的屬性為：DEFAULT NULL。

經過查詢資料，在w3schools上找到了答案。

• NULL is used as a placeholder for unknown or inapplicable values, it is treated differently from other values.
• It is not possible to test for NULL values with comparison operators, such as =, <, or <>. We will have to use the IS NULL and IS NOT NULL operators instead.

也就是說，在 SQL 中，NULL並不能採用!=與數值進行比較，若要進行比較，我們只能採用IS NULL或IS NOT NULL。

於是，我們將 SQL 語句改為如下形式：

SELECT * FROM qms_branch WHERE types IS NULL or types != 'dashboard'

再次查詢時，結果集就包含performance_trunk分支了。

問題延伸

透過上面例子，我們知道在對 NULL 進行判斷處理時，只能採用IS NULL或IS NOT NULL，而不能採用=, <, <>, !=這些運算子。

那除此之外，還有別的可能存在的坑麼？

再看一個例子：

有一張資料表table_foo，其中有一個欄位value_field，我們想從這張表中篩選出所有value_field為'value1'，'value2'或 NULL 的記錄。

那麼，我們採用IN運算子，透過如下 SQL 語句進行查詢。

SELECT * FROM table_foo WHERE value_field IN ('value1', 'value2', NULL)

這會存在問題麼？我們並沒有採用=, <, <>, !=對 NULL 進行比較哦。

答案是同樣存在問題！

因為在 SQL 中，IN語句會被轉換為多個=語句。例如，上面例子中的 SQL 在執行時就會被轉換為如下 SQL 語句：

SELECT * FROM table_foo WHERE value_field = 'value1' OR value_field = 'value2' OR value_field = NULL

而這個時候，執行value_field = NULL時就會出現問題了。

正確的做法應該是將NULL相關的判斷獨立出來，如下 SQL 才是正確的寫法。

SELECT * FROM table_foo WHERE value_field IN ('value1', 'value2') OR value_field IS NULL

---

關於作者

筆名九毫，英文名 Leo Lee。

專注於軟體測試行業，享受在牆角安靜地 debug，也喜歡在部落格上分享文字。

個人部落格：http://debugtalk.com