MySQL查詢NULL值處理函數詳解

我們已經看到使用WHERE子句的SQL SELECT命令來從MySQL表擷取資料。但是，當我們試圖給的條件比較欄位或列的值為NULL，它不能正常工作。

為了處理這種情況，MySQL提供了三大運算子

IS NULL: 此運算子返回true，當列的值是NULL。

IS NOT NULL: 運算子返回true，當列的值不是NULL。

<=> 操作符比較值（不同於=運算子）為ture，即使兩個NULL值

涉及NULL條件是特殊的。不能使用 =NULL 或 !=NULL 尋找NULL值的列。這種比較總是告訴他們是否是真正的失敗，因為這是不可能的。即使是NULL=NULL失敗。

如果要尋找是或不是NULL的列，請使用IS NULL或IS NOT NULL。

如果你想要尋找值是NULL的列，你不能使用=NULL測試。下列語句不返回任何行，因為對任何錶達式，

expr = NULL是假的：

代碼如下	複製代碼
mysql> SELECT * FROM my_table WHERE phone = NULL;

要想尋找NULL值，你必須使用IS NULL測試。下例顯示如何找出NULL電話號碼和空的電話號碼：

代碼如下	複製代碼
mysql> SELECT * FROM my_table WHERE phone IS NULL; mysql> SELECT * FROM my_table WHERE phone = "";

為了有助於NULL的處理，你能使用IS NULL和IS NOT NULL運算子和IFNULL()函數。

例子:
試試下面的例子：

代碼如下

複製代碼

root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> create table tcount_tbl     -> (     -> tutorial_author varchar(40) NOT NULL,     -> tutorial_count  INT     -> ); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO tcount_tbl     -> (tutorial_author, tutorial_count) values ('mahran', 20); mysql> INSERT INTO tcount_tbl     -> (tutorial_author, tutorial_count) values ('mahnaz', NULL); mysql> INSERT INTO tcount_tbl     -> (tutorial_author, tutorial_count) values ('Jen', NULL); mysql> INSERT INTO tcount_tbl     -> (tutorial_author, tutorial_count) values ('Gill', 20); mysql> SELECT * from tcount_tbl; +-----------------+----------------+ | tutorial_author | tutorial_count | +-----------------+----------------+ | mahran          |             20 | | mahnaz          |           NULL | | Jen             |           NULL | | Gill            |             20 | +-----------------+----------------+ 4 rows in set (0.00 sec) mysql>

可以看到=和!=不使用NULL值，如下所示：

代碼如下	複製代碼
mysql> SELECT * FROM tcount_tbl WHERE tutorial_count = NULL; Empty set (0.00 sec) mysql> SELECT * FROM tcount_tbl WHERE tutorial_count != NULL; Empty set (0.01 sec)

要找到，其中tutorial_count列是或不是NULL的記錄，查詢應該這樣寫：

代碼如下

複製代碼

mysql> SELECT * FROM tcount_tbl     -> WHERE tutorial_count IS NULL; +-----------------+----------------+ | tutorial_author | tutorial_count | +-----------------+----------------+ | mahnaz          |           NULL | | Jen             |           NULL | +-----------------+----------------+ 2 rows in set (0.00 sec) mysql> SELECT * from tcount_tbl     -> WHERE tutorial_count IS NOT NULL; +-----------------+----------------+ | tutorial_author | tutorial_count | +-----------------+----------------+ | mahran          |             20 | | Gill            |             20 | +-----------------+----------------+ 2 rows in set (0.00 sec)

子查詢 NOT IN 與 NOT EXISTS　中的NULL

有些情況下　NOT IN　形式的子查詢返回空結果集，但是將其改寫為 NOT EXISTS　形式後則恢複正常，如下所示：
建表：

  

代碼如下	複製代碼
mysql> CREATE TABLE t2 (col1 int default NULL, col2 int default NULL);       Query OK, 0 rows affected (0.01 sec)       mysql> CREATE TABLE t3 (col1 int default NULL, col2 int default NULL);       Query OK, 0 rows affected (0.01 sec)

加入資料：

代碼如下	複製代碼
mysql> INSERT INTO t2 VALUES (1,2),(1,3);       Query OK, 2 rows affected (0.00 sec)       Records: 2 Duplicates: 0 Warnings: 0       mysql> INSERT INTO t3 VALUES (1,2),(1,NULL);       Query OK, 2 rows affected (0.00 sec)       Records: 2 Duplicates: 0 Warnings: 0

執行如下查詢：

 

代碼如下	複製代碼
mysql> SELECT * FROM t2 WHERE col2 NOT IN (SELECT col2 FROM t3);       Empty set (0.00 sec)       mysql> SELECT * FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3 WHERE t3.col2 = t2.col2);       +------+------+       | col1 | col2 |       +------+------+       | 1 | 3 |       +------+------+       1 row in set (0.00 sec)

為什麼會這樣呢？這要從MySQL資料庫NULL的特殊性說起：

在MySQL中有三種狀態：True、False、Unknown，任何NULL的比較操作都是Unknown狀態，如下所示：

   

代碼如下	複製代碼
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;       +----------+-----------+----------+----------+       | 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |       +----------+-----------+----------+----------+       | NULL | NULL | NULL | NULL |       +----------+-----------+----------+----------+       1 row in set (0.00 sec)

而且所有的查詢條件（ON, WHERE, HAVING）都是將Unknown狀態當做False處理。

所以第一條查詢的查詢田間等同於：col2 NOT IN (2, NULL) => col2 <> 2 AND col2 <> NULL => true AND Unknow => Unknow => False
查詢條件永為False，故該查詢沒有返回結果。

而 NOT EXISTS 是迴圈執行的

他首先執行 SELECT 1 FROM t3 WHERE t3.col2 = 2

返回了結果，經 NOT EXISTS 操作後查詢條件為 False，故不做任何輸出，

接下來執行 SELECT 1 FROM t3 WHERE t3.col2 = 3

無返回結果。經 NOT EXISTS 操作後查詢條件為 True，於是輸出本次查詢結果。

所以，如果當一個 NOT IN 子查詢沒有返回結果的時候，應該特別注意內層查詢的結果集是否包含空值，若包含的話，應嘗試將查詢改寫為 NOT EXISTS 形式。