港真，Null 貌似在哪里都是個頭疼的問題，比如 Java 里讓人頭疼的 NullPointerException，為了避免猝不及防的空指針異常，千百年來程序猿們不得不在代碼里小心翼翼的各種 if 判斷，麻煩而又臃腫，為此 java8 引入了 Optional 來避免這一問題。

下面咱們要聊的是 MySQL 里的 null，在大量的 MySQL 優(yōu)化文章和書籍里都提到了字段盡可能用NOT NULL，而不是NULL，除非特殊情況。但卻都只給結(jié)論不說明原因，猶如雞湯不給勺子一樣，讓不少初學者對這個結(jié)論半信半疑或者云里霧里。本文今天就詳細的剖析下使用 Null 的原因，并給出一些不用 Null 的理由。

1、NULL 為什么這么多人用?

NULL是創(chuàng)建數(shù)據(jù)表時默認的，初級或不知情的或怕麻煩的程序員不會注意這點。

很多人員都以為not null 需要更多空間，其實這不是重點。

重點是很多程序員覺得NULL在開發(fā)中不用去判斷插入數(shù)據(jù)，寫sql語句的時候更方便快捷。

2、是不是以訛傳訛?

MySQL 官網(wǎng)文檔：

• NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

Mysql難以優(yōu)化引用可空列查詢，它會使索引、索引統(tǒng)計和值更加復(fù)雜。可空列需要更多的存儲空間，還需要mysql內(nèi)部進行特殊處理?？煽樟斜凰饕?，每條記錄都需要一個額外的字節(jié)，還能導(dǎo)致MYisam 中固定大小的索引變成可變大小的索引。

—— 出自《高性能mysql第二版》

照此分析，還真不是以訛傳訛，這是有理論依據(jù)和出處的。

3、給我一個不用 Null 的理由?

• (1)所有使用NULL值的情況，都可以通過一個有意義的值的表示，這樣有利于代碼的可讀性和可維護性，并能從約束上增強業(yè)務(wù)數(shù)據(jù)的規(guī)范性。
• (2)NULL值到非NULL的更新無法做到原地更新，更容易發(fā)生索引分裂，從而影響性能。

注意：但把NULL列改為NOT NULL帶來的性能提示很小，除非確定它帶來了問題，否則不要把它當成優(yōu)先的優(yōu)化措施，最重要的是使用的列的類型的適當性。

• (3)NULL值在timestamp類型下容易出問題，特別是沒有啟用參數(shù)explicit_defaults_for_timestamp
• (4)NOT IN、!= 等負向條件查詢在有 NULL 值的情況下返回永遠為空結(jié)果，查詢?nèi)菀壮鲥e

create table table_2 ( 
     `id` INT (11) NOT NULL, 
    user_name varchar(20) NOT NULL 
) 
 
 
create table table_3 ( 
     `id` INT (11) NOT NULL, 
    user_name varchar(20) 
) 
 
insert into table_2 values (4,"zhaoliu_2_1"),(2,"lisi_2_1"),(3,"wangmazi_2_1"),(1,"zhangsan_2"),(2,"lisi_2_2"),(4,"zhaoliu_2_2"),(3,"wangmazi_2_2") 
 
insert into table_3 values (1,"zhaoliu_2_1"),(2, null) 
 
-- 1、NOT IN子查詢在有NULL值的情況下返回永遠為空結(jié)果，查詢?nèi)菀壮鲥e 
select user_name from table_2 where user_name not in (select user_name from table_3 where id!=1) 
 
mysql root@10.48.186.32:t_test_zz5431> select user_name from table_2 where user_name not 
                                    -> in (select user_name from table_3 where id!=1); 
+-------------+ 
| user_name   | 
|-------------| 
+-------------+ 
0 rows in set 
Time: 0.008s 
mysql root@10.48.186.32:t_test_zz5431> 
 
-- 2、單列索引不存null值，復(fù)合索引不存全為null的值，如果列允許為null，可能會得到“不符合預(yù)期”的結(jié)果集 
-- 如果name允許為null，索引不存儲null值，結(jié)果集中不會包含這些記錄。所以，請使用not null約束以及默認值。 
select * from table_3 where name != 'zhaoliu_2_1' 
 
-- 3、如果在兩個字段進行拼接：比如題號+分數(shù)，首先要各字段進行非null判斷，否則只要任意一個字段為空都會造成拼接的結(jié)果為null。 
select CONCAT("1",null) from dual; -- 執(zhí)行結(jié)果為null。 
 
-- 4、如果有 Null column 存在的情況下，count(Null column)需要格外注意，null 值不會參與統(tǒng)計。 
mysql root@10.48.186.32:t_test_zz5431> select * from table_3; 
+------+-------------+ 
|   id | user_name   | 
|------+-------------| 
|    1 | zhaoliu_2_1 | 
|    2 | <null>      | 
|   21 | zhaoliu_2_1 | 
|   22 | <null>      | 
+------+-------------+ 
4 rows in set 
Time: 0.007s 
mysql root@10.48.186.32:t_test_zz5431> select count(user_name) from table_3; 
+--------------------+ 
|   count(user_name) | 
|--------------------| 
|                  2 | 
+--------------------+ 
1 row in set 
Time: 0.007s 
 
-- 5、注意 Null 字段的判斷方式， = null 將會得到錯誤的結(jié)果。 
mysql root@localhost:cygwin> create index IDX_test on table_3 (user_name); 
Query OK, 0 rows affected 
Time: 0.040s 
mysql root@localhost:cygwin>  select * from table_3 where user_name is null\G 
***************************[ 1. row ]*************************** 
id        | 2 
user_name | None 
 
1 row in set 
Time: 0.002s 
mysql root@localhost:cygwin> select * from table_3 where user_name = null\G 
 
0 rows in set 
Time: 0.002s 
mysql root@localhost:cygwin> desc select * from table_3 where user_name = 'zhaoliu_2_1'\G 
***************************[ 1. row ]*************************** 
id            | 1 
select_type   | SIMPLE 
table         | table_3 
type          | ref 
possible_keys | IDX_test 
key           | IDX_test 
key_len       | 23 
ref           | const 
rows          | 1 
Extra         | Using where 
 
1 row in set 
Time: 0.006s 
mysql root@localhost:cygwin> desc select * from table_3 where user_name = null\G 
***************************[ 1. row ]*************************** 
id            | 1 
select_type   | SIMPLE 
table         | None 
type          | None 
possible_keys | None 
key           | None 
key_len       | None 
ref           | None 
rows          | None 
Extra         | Impossible WHERE noticed after reading const tables 
 
1 row in set 
Time: 0.002s 
mysql root@localhost:cygwin> desc select * from table_3 where user_name is null\G 
***************************[ 1. row ]*************************** 
id            | 1 
select_type   | SIMPLE 
table         | table_3 
type          | ref 
possible_keys | IDX_test 
key           | IDX_test 
key_len       | 23 
ref           | const 
rows          | 1 
Extra         | Using where 
 
1 row in set 
Time: 0.002s 
mysql root@localhost:cygwin> 

(5)Null 列需要更多的存儲空間：需要一個額外字節(jié)作為判斷是否為 NULL 的標志位

alter table table_3 add index idx_user_name (user_name); 
alter table table_2 add index idx_user_name (user_name); 
explain select * from table_2 where user_name='zhaoliu_2_1'; 
explain select * from table_3 where user_name='zhaoliu_2_1'; 

可以看到同樣的 varchar(20) 長度，table_2 要比 table_3 索引長度大，這是因為：

• 兩張表的字符集不一樣，且字段一個為 NULL 一個非 NULL。

key_len 的計算規(guī)則和三個因素有關(guān)：數(shù)據(jù)類型、字符編碼、是否為 NULL

key_len 62 == 20*3(utf8 3字節(jié)) + 2 (存儲 varchar 變長字符長度 2字節(jié)，定長字段無需額外的字節(jié))

key_len 83 == 20*4(utf8mb4 4字節(jié)) + 1 (是否為 Null 的標識) + 2 (存儲 varchar 變長字符長度 2字節(jié)，定長字段無需額外的字節(jié))

所以說索引字段***不要為NULL，因為NULL會使索引、索引統(tǒng)計和值更加復(fù)雜，并且需要額外一個字節(jié)的存儲空間?；谝陨线@些理由和原因，我想咱們不用 Null 的理由應(yīng)該是夠了 🙂