mysql中怎么避免使用null定義字段的原因是什么

本篇文章給大家分享的是有關(guān)mysql中怎么避免使用null定義字段的原因是什么，小編覺得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

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

(1) java的null

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

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

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

guava的Optional

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

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

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

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

(c) 圖省事 null在開發(fā)中不用判斷插入數(shù)據(jù)，寫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第二版》

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

三 mysql不用null的理由

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

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

（3）NULL值在timestamp類型下容易出問題，特別是沒有啟用參數(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)字段無需額外的字節(jié))

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

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

以上就是mysql中怎么避免使用null定義字段的原因是什么，小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見到或用到的。希望你能通過這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注億速云行業(yè)資訊頻道。