一 NULL 為什么這么經常用

(1) java的null

null是一個讓人頭疼的問題，比如java中的NullPointerException。為了避免猝不及防的空指針，需要小心翼翼地各種if判斷，麻煩又臃腫.

為此有很多的開源包都有諸多處理

common lang3的StringUtils.isBlank(); CollectionUtils.isEmpty();

guava的Optional

甚至java8也引入了Optional來避免這一問題（和guava的大同小異，用法稍有一點點變化)

(2) mysql的null為什么橫行濫用

(a) 創建不規范 null是創建數據表時候默認的，一些mysql客戶端的自動生成表語句里面可能也沒有not null的指定。

(b) 錯誤認識 會有人覺得not null需要更多的空間

(c) 圖省事 null在開發中不用判斷插入數據，寫sql更方便

二 官方文檔

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難以優化引用可空列查詢，它會使索引、索引統計和值更加復雜。可空列需要更多的存儲空間，還需要mysql內部進行特殊處理。可空列被索引后，每條記錄都需要一個額外的字節，還能導致MYisam 中固定大小的索引變成可變大小的索引。 —— 出自《高性能mysql第二版》

如此看來，不指定not null并沒有性能上的優勢。

三 mysql不用null的理由

（1）所有使用NULL值的情況，都可以通過一個有意義的值的表示，這樣有利于代碼的可讀性和可維護性，并能從約束上增強業務數據的規范性。

（2）NULL值到非NULL的更新無法做到原地更新，更容易發生索引分裂，從而影響性能。(null -> not null性能提升很小，除非確定它帶來了問題，否則不要當成優先的優化措施)

（3）NULL值在timestamp類型下容易出問題，特別是沒有啟用參數explicit_defaults_for_timestamp

（4）NOT IN、!= 等負向條件查詢在有 NULL 值的情況下返回永遠為空結果，查詢容易出錯

四 null引發的bad case

數據初始化:

create table table1 (
    `id` INT (11) NOT NULL,
    `name` varchar(20) NOT NULL
)


create table table2 (
    `id` INT (11) NOT NULL,
    `name`  varchar(20)
)

insert into table1 values (4,"zhaoyun"),(2,"zhangfei"),(3,"liubei")
insert into table2 values (1,"zhaoyun"),(2, null)

(1)NOT IN子查詢在有NULL值的情況下返回永遠為空結果，查詢容易出錯

select name from table1 where name not in (select name from table2 where id!=1)

+-------------+
|      name   |
|-------------|
+-------------+

(2) 列值允許為空，索引不存儲null值，結果集中不會包含這些記錄。

select * from table2 where name != 'zhaoyun'

+------+-------------+
|   id |      name   |
|------+-------------|
|      |             |
+------+-------------+

select * from table2 where name != 'zhaoyun1'

+------+-------------+
|   id |      name   |
|------+-------------|
|   1  |  zhaoyun    |
+------+-------------+

(3) 使用concat拼接時，首先要對各個字段進行非null判斷，否則只要任何一個字段為空都會造成拼接的結果為null

select concat("1", null) from dual;

+--------------------+
|   concat("1", null)|
|--------------------|
|               NULL |
+--------------------+

(4) 當計算count時候null column不會計入統計

select count(name) from table2;

+--------------------+
|   count(user_name) |
|--------------------|
|                  1 |
+--------------------+

五 索引長度對比

alter table table1 add index idx_name (name);
alter table table2 add index idx_name (name);
explain select * from table1 where name='zhaoyun';
explain select * from table2 where name='zhaoyun';

table1的key_len = 82

table2的key_len = 83

key_len 的計算規則和三個因素有關：數據類型、字符編碼、是否為 NULL

key_len 82 = 20 * 4(utf8mb4 - 4字節, utf8 - 3字節) + 2(存儲varchar變長字符長度為2字節，定長字段無需額外的字節)

key_len 83 = 20 * 4(utf8mb4 - 4字節, utf8 - 3字節) + 2(存儲varchar變長字符長度為2字節，定長字段無需額外的字節) + 1(是否為null的標志)

所以說索引字段最好不要為NULL，因為NULL會使索引、索引統計和值更加復雜，并且需要額外一個字節的存儲空間。

到此這篇關于mysql 字段定義不要用null的分析的文章就介紹到這了,更多相關mysql 字段定義null內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！