萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> MySQL查詢NULL值處理函數詳解

MySQL查詢NULL值處理函數詳解

NULL值在mysql中不是空字符而是一個NULL了,那麼我們使用普通的NULL=\"\"是不行的,下面我來總結一下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 形式。

copyright © 萬盛學電腦網 all rights reserved