MySQL數(shù)據(jù)庫(kù)NULL值的處理
我們大家都知道MySQL數(shù)據(jù)庫(kù)NULL其有非常獨(dú)特的邏輯意義，對(duì)于NULL值的處理問(wèn)題可以說(shuō)是令人一大頭疼的事情，所以今天我特意整理了一些比較實(shí)用的TIPS，希望會(huì)給你帶來(lái)一些幫助在此方面，疏漏之處敬請(qǐng)批評(píng) 。
1. 對(duì)含空值列進(jìn)行排序
建表：
- mysql> create table t1(col1 int primary key, col2 varchar(2),col3 int);
- Query OK, 0 rows affected (0.24 sec)
加入數(shù)據(jù)：
- 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在排序過(guò)程中總是將NULL當(dāng)作“最小值”處理。例如：
- 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)
但是，怎樣實(shí)現(xiàn)如下效果呢：
- +------+------+------+
- | 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;
運(yùn)用 'CASE'表達(dá)式構(gòu)造一個(gè)附加列，并為空值和非空值分別賦值，然后以該附加列作為排序條件。
- 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)
通過(guò)構(gòu)造附加列對(duì)含MySQL數(shù)據(jù)庫(kù)NULL列排序，可以輕松決定含空值的記錄在結(jié)果集中的位置。
2. 運(yùn)用 ORDER BY NULL 禁止排序
若查詢中包含 GROUP BY 但我們希望避免對(duì)結(jié)果集排序從而減少消耗，可以運(yùn)用 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)
可見(jiàn)運(yùn)用了ORDER BY NULL后查詢便沒(méi)有進(jìn)行filesort，在較大結(jié)果集中filesort操作往往相當(dāng)耗時(shí)。
3. 子查詢 NOT IN 與 NOT EXISTS 中的NULL
有些情況下 NOT IN 形式的子查詢返回空結(jié)果集，但是將其改寫(xiě)為 NOT EXISTS 形式后則恢復(fù)正常，如下所示：
建表：
- 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)
加入數(shù)據(jù)：
- 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
執(zhí)行如下查詢：
- 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)
為什么會(huì)這樣呢？這要從MySQL數(shù)據(jù)庫(kù)NULL的特殊性說(shuō)起：
在MySQL中有三種狀態(tài)：True、False、Unknown，任何NULL的比較操作都是Unknown狀態(tài)，如下所示：
- 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狀態(tài)當(dāng)做False處理。
所以***條查詢的查詢田間等同于：col2 NOT IN (2, NULL) => col2 <> 2 AND col2 <> NULL => true AND Unknow => Unknow => False
查詢條件永為False，故該查詢沒(méi)有返回結(jié)果。
而 NOT EXISTS 是循環(huán)執(zhí)行的
他首先執(zhí)行 SELECT 1 FROM t3 WHERE t3.col2 = 2
返回了結(jié)果，經(jīng) NOT EXISTS 操作后查詢條件為 False，故不做任何輸出，
接下來(lái)執(zhí)行 SELECT 1 FROM t3 WHERE t3.col2 = 3
無(wú)返回結(jié)果。經(jīng) NOT EXISTS 操作后查詢條件為 True，于是輸出本次查詢結(jié)果。
所以，如果當(dāng)一個(gè) NOT IN 子查詢沒(méi)有返回結(jié)果的時(shí)候，應(yīng)該特別注意內(nèi)層查詢的結(jié)果集是否包含空值，若包含的話，應(yīng)嘗試將查詢改寫(xiě)為 NOT EXISTS 形式。
這種做法在有些情況下還可導(dǎo)致性能的提升。以上的相關(guān)內(nèi)容就是對(duì)MySQL數(shù)據(jù)庫(kù)NULL 的介紹，望你能有所收獲。
【編輯推薦】
