程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL數據庫NULL值的處理

MySQL數據庫NULL值的處理

編輯:MySQL綜合教程

我們大家都知道MySQL數據庫NULL其有非常獨特的邏輯意義,對於NULL值的處理問題可以說是令人一大頭疼的事情,所以今天我特意整理了一些比較實用的TIPS,希望會給你帶來一些幫助在此方面,疏漏之處敬請批評 。

1. 對含空值列進行排序

建表:

  1. mysql> create table t1(col1 int primary key, col2 varchar(2),col3 int);   
  2. Query OK, 0 rows affected (0.24 sec)  

加入數據:

  1. 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);   
  2. Query OK, 10 rows affected (0.08 sec)   
  3. Records: 10 Duplicates: 0 Warnings: 0  

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

  1. mysql> select * FROM t1 order by 3;   
  2. +------+------+------+   
  3. | col1 | col2 | col3 |   
  4. +------+------+------+   
  5. | 6 | F | NULL |   
  6. | 2 | B | NULL |   
  7. | 3 | C | NULL |   
  8. | 9 | I | NULL |   
  9. | 1 | A | 10 |   
  10. | 7 | G | 20 |   
  11. | 5 | E | 30 |   
  12. | 4 | D | 50 |   
  13. | 10 | J | 60 |   
  14. | 8 | H | 90 |   
  15. +------+------+------+   
  16. 10 rows in set (0.00 sec)   

但是,怎樣實現如下效果呢:

  1. +------+------+------+   
  2. | col1 | col2 | col3 |   
  3. +------+------+------+   
  4. | 1 | A | 10 |   
  5. | 7 | G | 20 |   
  6. | 5 | E | 30 |   
  7. | 4 | D | 50 |   
  8. | 10 | J | 60 |   
  9. | 8 | H | 90 |   
  10. | 2 | B | NULL |   
  11. | 3 | C | NULL |   
  12. | 6 | F | NULL |   
  13. | 9 | I | NULL |   
  14. +------+------+------+  
  15. 10 rows in set (0.00 sec)   
  16. SOLUTION:   
  17. mysql> SELECT col1, col2, col3 FROM (   
  18. -> SELECT col1, col2, col3,CASE WHEN col3 IS NULL THEN 0 ELSE 1 END AS IS_NULL FROM t1) as n   
  19. -> ORDER BY n.IS_NULL DESC, col3;  

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

  1. mysql> select col1, col2, col3,CASE WHEN col3 IS NULL THEN 0 ELSE 1 END AS IS_NULL FROM t1;   
  2. +------+------+------+---------+ |  
  3. | col1 | col2 | col3 | IS_NULL |   
  4. +------+------+------+---------+   
  5. | 1 | A | 10 | 1 |   
  6. | 2 | B | NULL | 0 |   
  7. | 3 | C | NULL | 0 |   
  8. | 4 | D | 50 | 1 |   
  9. | 5 | E | 30 | 1 |   
  10. | 6 | F | NULL | 0 |  
  11. | 7 | G | 20 | 1 |   
  12. | 8 | H | 90 | 1 |   
  13. | 9 | I | NULL | 0 |   
  14. | 10 | J | 60 | 1 |   
  15. +------+------+------+---------+   
  16. 10 rows in set (0.00 sec)   

通過構造附加列對含MySQL數據庫NULL列排序,可以輕松決定含空值的記錄在結果集中的位置。

2. 運用 ORDER BY NULL 禁止排序

若查詢中包含 GROUP BY 但我們希望避免對結果集排序從而減少消耗,可以運用 ORDER BY NULL 禁止排序。

  1. R,'` A.Kk   
  2. 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

  1. id: 1   
  2. select_type: SIMPLE   
  3. table: t1   
  4. type: ALL    
  5. possible_keys: NULL   
  6. key: NULL   
  7. key_len: NULL   
  8. ref: NULL   
  9. rows: 10   
  10. Extra: Using temporary; Using filesort   
  11. 1 row in set (0.00 sec)   
  12. 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

  1. id: 1   
  2. select_type: SIMPLE   
  3. table: t1   
  4. type: ALL   
  5. possible_keys: NULL   
  6. key: NULL   
  7. key_len: NULL ts   
  8. ref: NULL   
  9. rows: 10   
  10. Extra: Using temporary   
  11. 1 row in set (0.00 sec)  

可見運用了ORDER BY NULL後查詢便沒有進行filesort,在較大結果集中filesort操作往往相當耗時。

3. 子查詢 NOT IN 與 NOT EXISTS 中的NULL 

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

  1. mysql> CREATE TABLE t2 (col1 int default NULL, col2 int default NULL);   
  2. Query OK, 0 rows affected (0.01 sec)   
  3. mysql> CREATE TABLE t3 (col1 int default NULL, col2 int default NULL);   
  4. Query OK, 0 rows affected (0.01 sec)  

加入數據:

  1. mysql> INSERT INTO t2 VALUES (1,2),(1,3);   
  2. Query OK, 2 rows affected (0.00 sec)   
  3. Records: 2 Duplicates: 0 Warnings: 0   
  4. mysql> INSERT INTO t3 VALUES (1,2),(1,NULL);   
  5. Query OK, 2 rows affected (0.00 sec)   
  6. Records: 2 Duplicates: 0 Warnings: 0  

執行如下查詢:

  1. mysql> SELECT * FROM t2 WHERE col2 NOT IN (SELECT col2 FROM t3);   
  2. Empty set (0.00 sec)   
  3. mysql> SELECT * FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3 WHERE t3.col2 = t2.col2);   
  4. +------+------+   
  5. | col1 | col2 |   
  6. +------+------+   
  7. | 1 | 3 |   
  8. +------+------+   
  9. 1 row in set (0.00 sec)  

為什麼會這樣呢?這要從MySQL數據庫NULL的特殊性說起:

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

  1. mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;   
  2. +----------+-----------+----------+----------+   
  3. | 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |   
  4. +----------+-----------+----------+----------+   
  5. | NULL | NULL | NULL | NULL |   
  6. +----------+-----------+----------+----------+   
  7. 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 形式。

這種做法在有些情況下還可導致性能的提升。以上的相關內容就是對MySQL數據庫NULL 的介紹,望你能有所收獲。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved