一 NULL 為什么這么經(jīng)常用

(1) java的null

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

為此有很多的開(kāi)源包都有諸多處理

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

guava的Optional

甚至java8也引入了Optional來(lái)避免這一問(wèn)題（和guava的大同小異，用法稍有一點(diǎn)點(diǎn)變化)

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

(a) 創(chuàng)建不規(guī)范 null是創(chuàng)建數(shù)據(jù)表時(shí)候默認(rèn)的，一些mysql客戶端的自動(dòng)生成表語(yǔ)句里面可能也沒(méi)有not null的指定。

(b) 錯(cuò)誤認(rèn)識(shí) 會(huì)有人覺(jué)得not null需要更多的空間

(c) 圖省事 null在開(kāi)發(fā)中不用判斷插入數(shù)據(jù)，寫(xiě)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難以優(yōu)化引用可空列查詢，它會(huì)使索引、索引統(tǒng)計(jì)和值更加復(fù)雜?？煽樟行枰嗟拇鎯?chǔ)空間，還需要mysql內(nèi)部進(jìn)行特殊處理?？煽樟斜凰饕?，每條記錄都需要一個(gè)額外的字節(jié)，還能導(dǎo)致MYisam 中固定大小的索引變成可變大小的索引。 —— 出自《高性能mysql第二版》

如此看來(lái)，不指定not null并沒(méi)有性能上的優(yōu)勢(shì)。

三 mysql不用null的理由

（1）所有使用NULL值的情況，都可以通過(guò)一個(gè)有意義的值的表示，這樣有利于代碼的可讀性和可維護(hù)性，并能從約束上增強(qiáng)業(yè)務(wù)數(shù)據(jù)的規(guī)范性。

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

（3）NULL值在timestamp類型下容易出問(wèn)題，特別是沒(méi)有啟用參數(shù)explicit_defaults_for_timestamp

（4）NOT IN、!= 等負(fù)向條件查詢?cè)谟?NULL 值的情況下返回永遠(yuǎn)為空結(jié)果，查詢?nèi)菀壮鲥e(cuò)

四 null引發(fā)的bad case

數(shù)據(jù)初始化:

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子查詢?cè)谟蠳ULL值的情況下返回永遠(yuǎn)為空結(jié)果，查詢?nèi)菀壮鲥e(cuò)

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

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

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

select * from table2 where name != 'zhaoyun'

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

select * from table2 where name != 'zhaoyun1'

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

(3) 使用concat拼接時(shí)，首先要對(duì)各個(gè)字段進(jìn)行非null判斷，否則只要任何一個(gè)字段為空都會(huì)造成拼接的結(jié)果為null

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

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

(4) 當(dāng)計(jì)算count時(shí)候null column不會(huì)計(jì)入統(tǒng)計(jì)

select count(name) from table2;

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

五 索引長(zhǎng)度對(duì)比

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 的計(jì)算規(guī)則和三個(gè)因素有關(guān)：數(shù)據(jù)類型、字符編碼、是否為 NULL

key_len 82 = 20 * 4(utf8mb4 - 4字節(jié), utf8 - 3字節(jié)) + 2(存儲(chǔ)varchar變長(zhǎng)字符長(zhǎng)度為2字節(jié)，定長(zhǎng)字段無(wú)需額外的字節(jié))

key_len 83 = 20 * 4(utf8mb4 - 4字節(jié), utf8 - 3字節(jié)) + 2(存儲(chǔ)varchar變長(zhǎng)字符長(zhǎng)度為2字節(jié)，定長(zhǎng)字段無(wú)需額外的字節(jié)) + 1(是否為null的標(biāo)志)

所以說(shuō)索引字段最好不要為NULL，因?yàn)镹ULL會(huì)使索引、索引統(tǒng)計(jì)和值更加復(fù)雜，并且需要額外一個(gè)字節(jié)的存儲(chǔ)空間。

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

最新評(píng)論