Java知己

做一個積極的人
編碼精续、改 bug、提升自己
我有一個樂園粹懒，面向編程重付，春暖花開！

image

01 小木的故事

作為后臺開發(fā)凫乖，在日常工作中如果要接觸 Mysql 數(shù)據(jù)庫确垫，那么不可避免會遇到 Mysql 中的 NULL 和空值弓颈。那你知道它們有什么區(qū)別嗎？

學不動了删掀，也不想知道它們有什么區(qū)別翔冀。大兄弟，不行啊爬迟，要面試橘蜜！

image

前些天我的好朋友小木去應聘工作，他面試完回來和我聊天回味了一道他的面試題付呕。

面試官：你有用過 MySQL 嗎计福？

小木：有！

面試官：那你能大概說一下 Mysql 中 NULL 值和空值的區(qū)別嗎徽职？

小木：（思考…）NULL 和空值都用過象颖，你要我說它兩有啥區(qū)別，這個我還真沒仔細想過姆钉，反正實際開發(fā)中會用说订！

聽了小木的這個回答。

我說：你這樣回答肯定是不妥的潮瓶，這個問題你是必掛了陶冷。

小木說： NULL 翻譯過來不就是空嗎？我是真的沒有仔細想過毯辅，這個還是挺迷惑人的埂伦。

為了其他的伙伴在遇到這個問題的時候不要像我的好友小木一樣在此處跌倒，錯過心儀的公司思恐，下面簡單整理聊聊這兩者的一些區(qū)別和使用沾谜。

02 NULL 和空值

NULL 也就是在字段中存儲 NULL 值，空值也就是字段中存儲空字符 (’’)胀莹。

1基跑、占用空間區(qū)別

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逆日。

通俗的講：空值就像是一個真空轉態(tài)杯子，什么都沒有萄凤，而 NULL 值就是一個裝滿空氣的杯子，雖然看起來都是一樣的搪哪，但是有著本質(zhì)的區(qū)別靡努。

2、插入 / 查詢方式區(qū)別

創(chuàng)建一個表，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

模擬數(shù)據(jù)：

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去查，單純?nèi)ゲ榭罩?(’’) 列梁肿，則使用 =''蜓陌。

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

3烛芬、COUNT 和 IFNULL 函數(shù)

使用COUNT函數(shù)：

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函數(shù)：

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(字段) 統(tǒng)計會過濾掉 NULL 值隧期，但是不會過濾掉空值。

說明：IFNULL 有兩個參數(shù)赘娄。 如果第一個參數(shù)字段不是NULL仆潮，則返回第一個字段的值。 否則遣臼，IFNULL函數(shù)返回第二個參數(shù)的值（默認值）性置。

4、索引字段說明

看到網(wǎng)上有一些人說： MySql 中如果某一列中含有 NULL暑诸，那么包含該列的索引就無效了蚌讼。

給one 和two 字段分別加上普通索引。之前有寫過个榕，在復習添加索引：Mysql 索引整理總結

1
2
3
-- 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 表名; 篡石，查看這個表的所有索引信息。

一個普通索引西采，一個復合索引凰萨。

復合索引遵守 “最左前綴” 原則，即在查詢條件中使用了復合索引的第一個字段械馆，索引才會被使用胖眷。因此，在復合索引中索引列的順序至關重要霹崎。

image

可以看到珊搀，創(chuàng)建了兩個索引，并且index_tow NULL 那一列是 YES尾菇。

使用EXPLAIN 來進行演示說明境析，EXPLAIN 的使用說明：Mysql 中 explain 用法和結果字段的含義介紹

復合索引

image

普通索引

image

發(fā)現(xiàn)查詢two字段 是可以正常使用索引的囚枪。我使用的 MYSQL 5.7 ,InnoDB 引擎。也看了一些網(wǎng)上的資料劳淆，MySQL 中 NULL 對索引的影響 這個文章中用例子驗證链沼，MySQL 可以在含有 null 的列上使用索引。

備注：可能是其他條件下不行沛鸵，看網(wǎng)上資料說使用空間索引會失效括勺，具體我沒有去驗證，空間索引沒有用到過曲掰。查詢官網(wǎng) create-index-spatial疾捍，感興趣的伙伴可以自行驗證。

image

這里我想到一點蜈缤，很多問題的答案都是在指定的條件和環(huán)境下才成立拾氓，多質(zhì)疑，多驗證底哥。

小總結 ：在有 NULL 值得字段上使用常用的索引咙鞍，如普通索引、復合索引趾徽、全文索引等不會使索引失效续滋。在官網(wǎng)查看在空間索引的情況下，說明了 索引列必須為 NOT NULL孵奶。

03 總結提升

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

1、空值不占空間载绿，NULL 值占空間粥诫。當字段不為 NULL 時，也可以插入空值崭庸。

2怀浆、當使用 IS NOT NULL 或者 IS NULL 時，只能查出字段中沒有不為 NULL 的或者為 NULL 的怕享，不能查出空值执赡。

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

4、在進行 count() 統(tǒng)計某列的記錄數(shù)的時候跌帐，如果采用的 NULL 值首懈，會別系統(tǒng)自動忽略掉芳来，但是空值是會進行統(tǒng)計到其中的。

5猜拾、MySql 中如果某一列中含有 NULL，那么包含該列的索引就無效了佣盒。這一句不是很準確挎袜。

6：實際到底是使用 NULL 值還是空值 (’’)，根據(jù)實際業(yè)務來進行區(qū)分肥惭。個人建議在實際開發(fā)中如果沒有特殊的業(yè)務場景盯仪，可以直接使用空值。

以上就是我的對此問題的整理和思考蜜葱，希望可以在面試中幫助到你全景。如果你對此話題有自己的思考和理解，也歡迎留言一起探討牵囤！

04 參考資料

https://www.cnblogs.com/wzmenjoy/p/4244590.html

https://dev.mysql.com/doc/refman/5.7/en/working-with-null.html

謝謝你的閱讀爸黄，如果您覺得這篇文章對你有幫助，請點贊或者喜歡揭鳞，讓更多的人看到炕贵！祝你每天開心愉快！

不管做什么野崇，只要堅持下去就會看到不一樣称开！在路上，不卑不亢!

關注公眾號：「Java知己」乓梨，發(fā)送「1024」鳖轰，免費領取 30 本經(jīng)典編程書籍。?與 10 萬程序員一起進步扶镀。每天更新Java知識哦蕴侣，期待你的到來！

Java知己

每日福利