mysql 中字段究竟該不該為 NULL

為什么會(huì)有許多表的字段設(shè)置為NULL？

1. 開發(fā)中常用的建表工具創(chuàng)建表時(shí)字段默認(rèn)可以為 NULL。

2. 開發(fā)人員不能正確區(qū)分?NULL?和 NOT?NULL?的區(qū)別，以為默認(rèn)?NULL 可以節(jié)省空間。

3. 默認(rèn)為 NULL，在插入數(shù)據(jù)時(shí)可以少很多判斷

針對(duì)這些問題，下面就徹底搞清楚字段該不該為 NULL。

NULL 字段可以節(jié)省空間嗎？

NULL

MySQL官方文檔有如下的描述：

For NULL tables, NULL columns require additional space in the row to record whether their values are NULL. Each NULL column takes one bit extra, rounded up to the nearest byte.

從上面的描述中可以知道，在MyISAM中NULL字段并不能完全的節(jié)省空間。

InnoDB

InnoDB 表的行格式

平時(shí)使用 COMPACT 格式的行較多，正對(duì)該格式，文檔有如下的說明。

The variable-length part of the record header contains a bit vector for indicating NULL columns. If the number of columns in the index that can be NULL is N, the bit vector occupies CEILING(N/8) bytes. (For example, if there are anywhere from 9 to 16 columns that can be NULL, the bit vector uses two bytes.) Columns that are NULL do not occupy space other than the bit in this vector. The variable-length part of the header also contains the lengths of variable-length columns. Each length takes one or two bytes, depending on the maximum length of the column. If all columns in the index are NOT NULL and have a fixed length, the record header has no variable-length part.

從上面的內(nèi)容可以知道，NULL 列除不能節(jié)省空間，反而會(huì)增加空間。

MySQL考慮的是越小的行大小，在固定大小的內(nèi)存中就可以更多的緩存行數(shù)據(jù)，提升性能。如果字段有值就存，沒值就不存，默認(rèn)值也不是保存行數(shù)據(jù)里面的。

NULL 字段帶來的問題

1. NULL值到非NULL的更新無法做到原地更新，更容易發(fā)生頁分裂，從而影響性能。

2. NULL值在timestamp類型下容易出問題，特別是沒有啟用參數(shù)explicit_defaults_for_timestamp=true具體見文章最后的：參考資料

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

1，NOT IN子查詢?cè)谟蠳ULL值的情況下返回永遠(yuǎn)為空結(jié)果，查詢?nèi)菀壮鲥e(cuò)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

create table table_2 (
true `id` INT (11) NOT NULL,
true user_name varchar(20) NOT NULL
)


create table table_3 (
true `id` INT (11) NOT NULL,
true 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)

select user_name from table_2 where user_name not in (select user_name from table_3 where id!=1)

2，單列索引不存null值，復(fù)合索引不存全為null的值，如果列允許為null，可能會(huì)得到“不符合預(yù)期”的結(jié)果集如果name允許為null，索引不存儲(chǔ)null值，結(jié)果集中不會(huì)包含這些記錄。所以，請(qǐng)使用not null約束以及默認(rèn)值。

1

select * from table_3 where name != 'zhaoliu_2_1'

3，如果在兩個(gè)字段進(jìn)行拼接：比如題號(hào)+分?jǐn)?shù)，首先要各字段進(jìn)行非null判斷，否則只要任意一個(gè)字段為空都會(huì)造成拼接的結(jié)果為null。

1

select CONCAT("1", null) from dual; -- 執(zhí)行結(jié)果為null。

4，如果有 Null column 存在的情況下，count(Null column)需要格外注意，null 值不會(huì)參與統(tǒng)計(jì)。

1
2

// 下面的語句返回 2， 但是數(shù)據(jù)庫里面有4條記錄
select count(user_name) from table_3;

5， NULL 字段的判斷 需要使用?IS?NULL?或?IS?NOT?NULL

NULL字段和索引

索引長度 key_len

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

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

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

null字段唯一索引

在可以為NULL的字段上也是可以建立唯一索引的，但需要注意的是：唯一索引想要防止重復(fù)記錄的功能就失效了。官方文檔描述如下：

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

NULL 字段普通索引

在可以為 NULL 的字段上建立普通索引，則所有索引字段為 NULL 的索引記錄都是排列在一起的。

source: //leokongwq.github.io/2019/01/05/mysql-null-column.html

喜歡，在看