MySQL中NULL值相關技巧總結

NULL具有獨特的邏輯意義，對於NULL值的處理一度是讓很多朋友頭疼的問題，今日整理了一些實用的TIPS，望對大家有所協助，疏漏之處敬請批評

1. 對含空值列進行排序
建表：  
mysql> create table t1(col1 int primary key, col2 varchar(2),col3 int);
Query OK, 0 rows affected (0.24 sec)

加入資料：

mysql> insert into t1 values (1,'A',10),(2,'B',NULL),(3,'C',NULL),(4,'D',50),(5,'E',30),(6,'F',NULL),(7,'G',20),(8,'H',90),(9,'I',NULL),(10,'J',60);   
Query OK, 10 rows affected (0.08 sec)  
Records: 10 Duplicates: 0 Warnings: 0

我們知道MySQL在排序過程中總是將NULL當作“最小值”處理。例如：

mysql> select * FROM t1 order by 3;  
+------+------+------+
| col1 | col2 | col3 |

+------+------+------+

| 6 | F | NULL |
| 2 | B | NULL | 
| 3 | C | NULL |  
| 9 | I | NULL |

| 1 | A | 10 |
| 7 | G | 20 |

| 5 | E | 30 |
| 4 | D | 50 |
| 10 | J | 60 |
| 8 | H | 90 |
+------+------+------+  
10 rows in set (0.00 sec) 
  
但是，怎樣實現如下效果呢：
+------+------+------+  
| col1 | col2 | col3 |

+------+------+------+
| 1 | A | 10 |  
| 7 | G | 20 |  
| 5 | E | 30 |
| 4 | D | 50 |
| 10 | J | 60 |  
| 8 | H | 90 |

| 2 | B | NULL |
| 3 | C | NULL |
| 6 | F | NULL |   
| 9 | I | NULL |  
+------+------+------+
10 rows in set (0.00 sec)

SOLUTION：
mysql> SELECT col1, col2, col3 FROM (

-> SELECT col1, col2, col3,CASE WHEN col3 IS NULL THEN 0 ELSE 1 END AS IS_NULL FROM t1) as n 
-> ORDER BY n.IS_NULL DESC, col3;

運用 'CASE'運算式構造一個附加列，並為空白值和非空值分別賦值，然後以該附加列作為排序條件。   

mysql> select col1, col2, col3,CASE WHEN col3 IS NULL THEN 0 ELSE 1 END AS IS_NULL FROM t1;  
+------+------+------+---------+ |

| col1 | col2 | col3 | IS_NULL |
+------+------+------+---------+  
| 1 | A | 10 | 1 |

| 2 | B | NULL | 0 |   
| 3 | C | NULL | 0 |
| 4 | D | 50 | 1 |
| 5 | E | 30 | 1 |
| 6 | F | NULL | 0 |
| 7 | G | 20 | 1 |

| 8 | H | 90 | 1 |   
| 9 | I | NULL | 0 |

| 10 | J | 60 | 1 |   
+------+------+------+---------+   
10 rows in set (0.00 sec)   

通過構造附加列對含NULL列排序，可以輕鬆決定含空值的記錄在結果集中的位置。   

2. 運用 ORDER BY NULL 禁止排序  
若查詢中包含 GROUP BY　但我們希望避免對結果集排序從而減少消耗，可以運用　ORDER BY NULL 禁止排序。
R,'` A.Kk  
mysql> EXPLAIN SELECT SUM(col3),CASE WHEN col3 IS NULL THEN 0 ELSE 1 END AS IS_NULL FROM t1 GROUP BY IS_NULL/G;

*************************** 1. row ***************************  
id: 1
select_type: SIMPLE  
table: t1   
type: ALL  
possible_keys: NULL   
key: NULL   
key_len: NULL   
ref: NULL   
rows: 10   
Extra: Using temporary; Using filesort   
1 row in set (0.00 sec)  
 
mysql> EXPLAIN SELECT SUM(col3),CASE WHEN col3 IS NULL THEN 0 ELSE 1 END AS IS_NULL FROM t1 GROUP BY IS_NULL m)  
ORDER BY NULL/G;  
*************************** 1. row ***************************   
id: 1
select_type: SIMPLE   
table: t1  
type: ALL   
possible_keys: NULL   
key: NULL   
key_len: NULL ts  
ref: NULL   
rows: 10  
Extra: Using temporary   
1 row in set (0.00 sec)   
可見運用了ORDER BY NULL後查詢便沒有進行filesort，在較大結果集中filesort操作往往相當耗時。 
  
3. 子查詢 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)  
 
為什麼會這樣呢？這要從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 形式。   
這種做法在有些情況下還可導致效能的提升。