MySQL 中NULL和空值的差別

NULL和空值

NULL也就是在字段中存儲NULL值，空值也就是字段中存儲空字元('')。

1、占用空間差別

mysql>  select length(NULL), length(''), length('1');
+--------------+------------+-------------+
| length(NULL) | length('') | length('1') |
+--------------+------------+-------------+
| NULL         |          0 |           1 |
+--------------+------------+-------------+
1 row in set
複制代碼      

小總結：從上面看出空值('')的長度是0，是不占用空間的；而的NULL長度是NULL，其實它是占用空間的，看下面說明。

NULL columns require additional space in the row to record whether their values are NULL.

NULL列需要行中的額外空間來記錄它們的值是否為NULL。

通俗的講：空值就像是一個真空轉态杯子，什麼都沒有，而NULL值就是一個裝滿空氣的杯子，雖然看起來都是一樣的，但是有着本質的差別。

2、插入/查詢方式差別

建立一個表，​

​tb_test​

​

CREATE TABLE `tb_test` (
  `one` varchar(10) NOT NULL,
  `two` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
複制代碼      

插入進行驗證：

-- 全部插入 NULL，失敗
mysql> INSERT tb_test VALUES (NULL,NULL);
1048 - Column 'one' cannot be null
複制代碼      

-- 全部插入 空值，成功
mysql> INSERT tb_test VALUES ('','');
Query OK, 1 row affected
複制代碼      

模拟資料：

INSERT tb_test VALUES (1,NULL);
INSERT tb_test VALUES ('',2);
INSERT tb_test VALUES (3,3);
複制代碼      

空值字段：

-- 使用 is null/is not null
mysql> SELECT * FROM tb_test where one is NULL;
Empty set

mysql> SELECT * FROM tb_test where one is not NULL;
+-----+------+
| one | two  |
+-----+------+
| 1   | NULL |
|     | 2    |
| 3   | 3    |
+-----+------+
3 rows in set
-- 使用 = 、!=
mysql> SELECT * FROM tb_test where one = '';
+-----+-----+
| one | two |
+-----+-----+
|     | 2   |
+-----+-----+
1 row in set

mysql> SELECT * FROM tb_test where one != '';
+-----+------+
| one | two  |
+-----+------+
| 1   | NULL |
| 3   | 3    |
+-----+------+
2 rows in set

複制代碼      

NULL值字段：

-- 使用 is null/is not null
mysql> SELECT * FROM tb_test where two is not NULL;
+-----+-----+
| one | two |
+-----+-----+
|     | 2   |
| 3   | 3   |
+-----+-----+
2 rows in set

mysql> SELECT * FROM tb_test where two is NULL;
+-----+------+
| one | two  |
+-----+------+
| 1   | NULL |
+-----+------+
1 row in set

-- 使用 = 、!=
mysql> SELECT * FROM tb_test where two = '';
Empty set

mysql> SELECT * FROM tb_test where two != '';
+-----+-----+
| one | two |
+-----+-----+
|     | 2   |
| 3   | 3   |
+-----+-----+
2 rows in set
複制代碼      

小總結：如果要單純查NULL值列，則使用 ​

​is NULL​

​​去查，單純去查空值('')列，則使用 ​

​=''​

​。

建議查詢方式：NULL值查詢使用is null/is not null查詢，而空值('')可以使用=或者!=、<、>等算術運算符。

3、COUNT 和 IFNULL函數

使用​

​COUNT​

​函數：

mysql> SELECT count(one) FROM tb_test;
+------------+
| count(one) |
+------------+
|          3 |
+------------+
1 row in set

mysql> SELECT count(two) FROM tb_test;
+------------+
| count(two) |
+------------+
|          2 |
+------------+
1 row in set

mysql> SELECT count(*) FROM tb_test;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set
複制代碼      

使用​

​IFNULL​

​函數：

mysql> SELECT IFNULL(one,111111111) from tb_test WHERE one = '';
+-----------------------+
| IFNULL(one,111111111) |
+-----------------------+
|                       |
+-----------------------+
1 row in set

mysql> SELECT IFNULL(two,11111111) from tb_test where two is NULL;
+----------------------+
| IFNULL(two,11111111) |
+----------------------+
| 11111111             |
+----------------------+
1 row in set
複制代碼      

小總結：使用 COUNT(字段) 統計會過濾掉 NULL 值，但是不會過濾掉空值。

說明：IFNULL有兩個參數。 如果第一個參數字段不是​

​NULL​

​​，則傳回第一個字段的值。 否則，​

​IFNULL​

​函數傳回第二個參數的值（預設值）。

4、索引字段說明

看到網上有一些人說： MySql中如果某一列中含有NULL，那麼包含該列的索引就無效了。

給​

​one​

​​ 和​

​two​

​​ 字段分别加上普通索引。之前有寫過，在複習添加索引：​​Mysql索引整理總結​​

-- ALTER TABLE table_name ADD INDEX index_name(col_name);
ALTER TABLE tb_test ADD INDEX index_oat (one, two);
ALTER TABLE tb_test add INDEX index_two(two);
複制代碼      

使用 ​

​show keys from 表名;​

​​或​

​show indexes from 表名;​

​ ，檢視這個表的所有索引資訊。

一個普通索引，一個複合索引。

複合索引遵守“最左字首”原則，即在查詢條件中使用了複合索引的第一個字段，索引才會被使用。是以，在複合索引中索引列的順序至關重要。

可以看到，建立了兩個索引，并且​

​index_tow​

​ NULL 那一列是 YES。

使用​

​EXPLAIN​

​​ 來進行示範說明，​

​EXPLAIN​

​​ 的使用說明：​​Mysql中explain用法和結果字段的含義介紹​​

​

​複合索引​

​

​

​普通索引​

​

發現查詢​

​two​

​字段 是可以正常使用索引的。我使用的MYSQL 5.7 ,InnoDB 引擎。也看了一些網上的資料，​​MySQL中NULL對索引的影響​​ 這個文章中用例子驗證，MySQL可以在含有null的列上使用索引。

備注：可能是其他條件下不行，看網上資料說使用​

​空間索引​

​​會失效，具體我沒有去驗證，空間索引沒有用到過。查詢官網​​create-index-spatial​​，感興趣的夥伴可以自行驗證。

這裡我想到一點，很多問題的答案都是在指定的條件和環境下才成立，多質疑，多驗證。

小總結 ：在有NULL值得字段上使用常用的索引，如普通索引、複合索引、全文索引等不會使索引失效。在官網檢視在空間索引的情況下，說明了 索引列必須為NOT NULL。

03 總結提升

如果你可以從上面的幾個方面和面試官進行一個溝通，即使回答的不是那麼的完美，但總比 “這兩個都用過，具體有啥差別就不知道了” 這樣的回答能好那麼一點點。

1、空值不占空間，NULL值占空間。當字段不為NULL時，也可以插入空值。

2、當使用 IS NOT NULL 或者 IS NULL 時，隻能查出字段中沒有不為NULL的或者為 NULL 的，不能查出空值。

3、判斷NULL 用IS NULL 或者 is not null,SQL 語句函數中可以使用IFNULL()函數來進行處理，判斷空字元用 =''或者<>''來進行處理。