快捷導(dǎo)航

為什么mysql字段要使用NOT NULL

更新時(shí)間：2021年05月13日 10:18:01 作者：艾小仙

數(shù)據(jù)庫(kù)字段一定要設(shè)置為 not null,不然會(huì)有很大的bug，下面就一起來(lái)介紹一下，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

最近剛?cè)肼毿鹿?，發(fā)現(xiàn)數(shù)據(jù)庫(kù)設(shè)計(jì)有點(diǎn)小問(wèn)題，數(shù)據(jù)庫(kù)字段很多沒(méi)有NOT NULL，對(duì)于強(qiáng)迫癥晚期患者來(lái)說(shuō)，簡(jiǎn)直難以忍受，因此有了這篇文章。

基于目前大部分的開(kāi)發(fā)現(xiàn)狀來(lái)說(shuō)，我們都會(huì)把字段全部設(shè)置成NOT NULL并且給默認(rèn)值的形式。

通常，對(duì)于默認(rèn)值一般這樣設(shè)置：
整形，我們一般使用0作為默認(rèn)值。
字符串，默認(rèn)空字符串

時(shí)間，可以默認(rèn)1970-01-01 08:00:01，或者默認(rèn)0000-00-00 00:00:00，但是連接參數(shù)要添加zeroDateTimeBehavior=convertToNull，建議的話還是不要用這種默認(rèn)的時(shí)間格式比較好

但是，考慮下原因，為什么要設(shè)置成NOT NULL？

來(lái)自高性能Mysql中有這樣一段話：

盡量避免NULL

很多表都包含可為NULL（空值）的列，即使應(yīng)用程序并不需要保存NULL也是如此，這是因?yàn)榭蔀镹ULL是列的默認(rèn)屬性。通常情況下最好指定列為NOT NULL，除非真的需要存儲(chǔ)NULL值。

如果查詢中包含可為NULL的列，對(duì)MySql來(lái)說(shuō)更難優(yōu)化，因?yàn)榭蔀镹ULL的列使得索引、索引統(tǒng)計(jì)和值比較都更復(fù)雜?？蔀镹ULL的列會(huì)使用更多的存儲(chǔ)空間，在MySql里也需要特殊處理。當(dāng)可為NULL的列被索引時(shí)，每個(gè)索引記錄需要一個(gè)額外的字節(jié)，在MyISAM里甚至還可能導(dǎo)致固定大小的索引（例如只有一個(gè)整數(shù)列的索引）變成可變大小的索引。

通常把可為NULL的列改為NOT NULL帶來(lái)的性能提升比較小，所以（調(diào)優(yōu)時(shí)）沒(méi)有必要首先在現(xiàn)有schema中查找并修改掉這種情況，除非確定這會(huì)導(dǎo)致問(wèn)題。但是，如果計(jì)劃在列上建索引，就應(yīng)該盡量避免設(shè)計(jì)成可為NULL的列。

當(dāng)然也有例外，例如值得一提的是，InnoDB使用單獨(dú)的位（bit）存儲(chǔ)NULL值，所以對(duì)于稀疏數(shù)據(jù)有很好的空間效率。但這一點(diǎn)不適用于MyISAM。

書中的描述說(shuō)了幾個(gè)主要問(wèn)題，我這里暫且拋開(kāi)MyISAM的問(wèn)題不談，這里我針對(duì)InnoDB作為考量條件。

如果不設(shè)置NOT NULL的話，NULL是列的默認(rèn)值，如果不是本身需要的話，盡量就不要使用NULL
使用NULL帶來(lái)更多的問(wèn)題，比如索引、索引統(tǒng)計(jì)、值計(jì)算更加復(fù)雜，如果使用索引，就要避免列設(shè)置成NULL
如果是索引列，會(huì)帶來(lái)的存儲(chǔ)空間的問(wèn)題，需要額外的特殊處理，還會(huì)導(dǎo)致更多的存儲(chǔ)空間占用
對(duì)于稀疏數(shù)據(jù)又更好的空間效率，稀疏數(shù)據(jù)指的是很多值為NULL，只有少數(shù)行的列有非NULL值的情況

默認(rèn)值

對(duì)于MySql而言，如果不主動(dòng)設(shè)置為NOT NULL的話，那么插入數(shù)據(jù)的時(shí)候默認(rèn)值就是NULL。

NULL和NOT NULL使用的空值代表的含義是不一樣，NULL可以認(rèn)為這一列的值是未知的，空值則可以認(rèn)為我們知道這個(gè)值，只不過(guò)他是空的而已。

舉個(gè)例子，一張表中的某一條name字段是NULL，我們可以認(rèn)為不知道名字是什么，反之如果是空字符串則可以認(rèn)為我們知道沒(méi)有名字，他就是一個(gè)空值。

而對(duì)于大多數(shù)程序的情況而言，沒(méi)有什么特殊需要非要字段要NULL的吧，NULL值反而會(huì)對(duì)程序造成比如空指針的問(wèn)題。

對(duì)于現(xiàn)狀大部分使用MyBatis的情況來(lái)說(shuō)，我建議使用默認(rèn)生成的insertSelective方法或者純手動(dòng)寫插入方法，可以避免新增NOT NULL字段導(dǎo)致的默認(rèn)值不生效或者插入報(bào)錯(cuò)的問(wèn)題。

值計(jì)算

聚合函數(shù)不準(zhǔn)確

對(duì)于NULL值的列，使用聚合函數(shù)的時(shí)候會(huì)忽略NULL值。

現(xiàn)在我們有一張表，name字段默認(rèn)是NULL，此時(shí)對(duì)name進(jìn)行count得出的結(jié)果是1，這個(gè)是錯(cuò)誤的。

count(*)是對(duì)表中的行數(shù)進(jìn)行統(tǒng)計(jì)，count(name)則是對(duì)表中非NULL的列進(jìn)行統(tǒng)計(jì)。

=失效

對(duì)于NULL值的列，是不能使用=表達(dá)式進(jìn)行判斷的，下面對(duì)name的查詢是不成立的，必須使用is NULL。

與其他值運(yùn)算

NULL和其他任何值進(jìn)行運(yùn)算都是NULL，包括表達(dá)式的值也是NULL。

user表第二條記錄age是NULL，所以+1之后還是NULL，name是NULL，進(jìn)行concat運(yùn)算之后結(jié)果還是NULL。

可以再看下下面的例子，任何和NULL進(jìn)行運(yùn)算的話得出的結(jié)果都會(huì)是NULL，想象下你設(shè)計(jì)的某個(gè)字段如果是NULL還不小心進(jìn)行各種運(yùn)算，最后得出的結(jié)果。。。

distinct、group by、order by

對(duì)于distinct和group by來(lái)說(shuō)，所有的NULL值都會(huì)被視為相等，對(duì)于order by來(lái)說(shuō)升序NULL會(huì)排在最前

其他問(wèn)題

表中只有一條有名字的記錄，此時(shí)查詢名字!=a預(yù)期的結(jié)果應(yīng)該是想查出來(lái)剩余的兩條記錄，會(huì)發(fā)現(xiàn)與預(yù)期結(jié)果不匹配。

索引問(wèn)題

為了驗(yàn)證NULL字段對(duì)索引的影響，分別對(duì)name 和age添加索引。

關(guān)于網(wǎng)上很多說(shuō)如果NULL那么不能使用索引的說(shuō)法，這個(gè)描述其實(shí)并不準(zhǔn)確，根據(jù)引用官方文檔[3]里描述，使用is NULL和范圍查詢都是可以和正常一樣使用索引的，實(shí)際驗(yàn)證的結(jié)果好像也是這樣，看以下例子。