大家好，我是咔咔 不期速成，日拱一卒

之前ElasticSearch系列文章中提到了如何處理空值，若為Null則會(huì)直接報(bào)錯(cuò)，因?yàn)樵贓lasticSearch中當(dāng)字段值為null時(shí)、空數(shù)組、null值數(shù)組時(shí)，會(huì)將其視為該字段沒(méi)有值，最終還是需要使用exists或者null_value來(lái)處理空值

大多數(shù)ElasticSearch的數(shù)據(jù)都來(lái)自于各類數(shù)據(jù)庫(kù)，這里暫且只針對(duì)于MySQL，各個(gè)開(kāi)源軟件中都默認(rèn)兼容各種Null值，空數(shù)組等等

若從根源上截?cái)嗑涂梢允『芏嗍拢钡浆F(xiàn)在很多開(kāi)發(fā)小伙伴還是堅(jiān)韌不拔的給字段的默認(rèn)值還是Null

本期就來(lái)聊一聊為什么不建議給字段的默認(rèn)值設(shè)置為Null

本期環(huán)境為：MySQL8.0.26

b9a7b0081aa9407e8e6dd29c781d2d6f.png

一、案例數(shù)據(jù)

創(chuàng)建表user

CREATE TABLE `user` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
 `age` tinyint(4) unsigned NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

添加數(shù)據(jù)，共計(jì)10條數(shù)據(jù)，有兩條數(shù)據(jù)的name值為Null

INSERT INTO `user` (`name`, `age`) VALUES ('kaka', 26);
INSERT INTO `user` (`name`, `age`) VALUES ('niuniu', 26);
INSERT INTO `user` (`name`, `age`) VALUES ('yangyang', 26);
INSERT INTO `user` (`name`, `age`) VALUES ('dandan', 26);
INSERT INTO `user` (`name`, `age`) VALUES ('liuliu', 26);
INSERT INTO `user` (`name`, `age`) VALUES ('yanyan', 26);
INSERT INTO `user` (`name`, `age`) VALUES ('leilie', 26);
INSERT INTO `user` (`name`, `age`) VALUES ('yao', 26);
INSERT INTO `user` (`name`, `age`) VALUES (NULL, 26);
INSERT INTO `user` (`name`, `age`) VALUES (NULL, 26);

一、count數(shù)據(jù)丟失

在這期 MySQL統(tǒng)計(jì)總數(shù)就用count，別花里胡哨的《死磕MySQL系列 十》 文章中，已經(jīng)對(duì)count的使用說(shuō)的非常明白了。

那借著這個(gè)案例，來(lái)分析一下為什么數(shù)據(jù)會(huì)丟失，先看結(jié)果

select count(*) as num1 ,count(name) as num2 from user;

[圖片上傳失敗...(image-1cd61e-1651801816061)]

使用count字段名時(shí)出現(xiàn)了數(shù)據(jù)丟失，很明顯是因?yàn)橹麈IID9、10這兩條記錄的name值為空造成的。

為什么會(huì)出現(xiàn)這種情況？

當(dāng)count除了主鍵字段外，會(huì)有兩種情況：

一種是字段為null，執(zhí)行時(shí)，判斷到有可能是null，但還要把值取出來(lái)再判斷下，不是null的進(jìn)行累加

另一種是字段為not null，執(zhí)行時(shí)，逐行從記錄里邊讀出這個(gè)字段，判斷不是null，才進(jìn)行累加

此時(shí)，咱們遇到的問(wèn)題是name字段的值存在了null值，所以會(huì)走第一種情況，不進(jìn)行統(tǒng)計(jì)null值

為什么建議大家都使用count()?*

MySQL對(duì)于count做了專門的優(yōu)化，跟字段不同的是并不是把所有帶了*的值取出來(lái)，而是指定了count(*)肯定不是null，只需要按行累加即可

MySQL團(tuán)隊(duì)對(duì)count()做了什么優(yōu)化？*

MySQL系列文章至今已經(jīng)更新了第十八期了，你有沒(méi)有猜到原因呢？

現(xiàn)在你應(yīng)該知道主鍵索引結(jié)構(gòu)中葉子節(jié)點(diǎn)存儲(chǔ)的是整行數(shù)據(jù)，而普通索引葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵ID

那對(duì)于普通索引來(lái)說(shuō)肯定會(huì)比主鍵索引小，因?yàn)閷?duì)于MySQL來(lái)說(shuō)，不管遍歷哪個(gè)索引結(jié)果都一樣，所以優(yōu)化器會(huì)主動(dòng)去找到那顆最小的樹(shù)進(jìn)行遍歷。

在邏輯正確的前提下，盡量減少訪問(wèn)數(shù)據(jù)量，是數(shù)據(jù)庫(kù)系統(tǒng)設(shè)計(jì)通用法則之一。

最后給大家留一個(gè)問(wèn)題，為什么Innodb存儲(chǔ)引擎不跟Myisam存儲(chǔ)一樣存儲(chǔ)一個(gè)count值呢？

如果不知道的話，可以看上文提到的count文章

二、為distinct打抱不平

在開(kāi)發(fā)工作中使用Distinct進(jìn)行去重的場(chǎng)景十分的少，大多數(shù)情況都是使用group by完成的

select distinct name from user;

可以看到此時(shí)的數(shù)據(jù)依然是正確的，對(duì)Null值做了去重的操作

[圖片上傳失敗...(image-692e6c-1651801816061)]

為什么要說(shuō)這個(gè)，因?yàn)檫沁窃谄渌钠脚_(tái)上看到過(guò)有人這么使用count(distinct name,mobile)，然后說(shuō)是統(tǒng)計(jì)出來(lái)的數(shù)據(jù)不準(zhǔn)確。

這種用法依然是count(字段)的用法，distinct本身是會(huì)對(duì)Null進(jìn)行去重，去重后依然是需要判斷name的值不為null時(shí)，才會(huì)進(jìn)行累計(jì)。

所以，不要把鍋甩給distinct

三、使用表達(dá)式數(shù)據(jù)丟失

在一些值為null時(shí)，使用表達(dá)式會(huì)造成數(shù)據(jù)的不一致，接下來(lái)一起看下

select * from user where name != 'kaka';

[圖片上傳失敗...(image-c85131-1651801816061)]

這跟我們的預(yù)期結(jié)果不大一致，預(yù)期是想返回id2~10的數(shù)據(jù)

當(dāng)然，這個(gè)問(wèn)題也不是無(wú)解，MySQL同樣也提供了方法

要解決這個(gè)問(wèn)題，只能再加一個(gè)條件就是把字段值為null的再單獨(dú)處理一下

[圖片上傳失敗...(image-cf6b5e-1651801816061)]

四、空指針問(wèn)題

如果一個(gè)列存在null值，使用MySQL的聚合函數(shù)后返回結(jié)果是null，而并非是0，就會(huì)造成程序執(zhí)行時(shí)的指針異常

CREATE TABLE user_order (
 id INT PRIMARY KEY auto_increment,
 num int
) ENGINE='innodb';

insert into user_order(num) values(3),(6),(6),(NULL);

創(chuàng)建用戶訂單數(shù)量表，并插入4條數(shù)據(jù)，接下來(lái)演示一下產(chǎn)生的問(wèn)題

select sum(num) from goods where id>4;

[圖片上傳失敗...(image-a905af-1651801816061)]

可以看到當(dāng)字段為null時(shí)，使用聚合函數(shù)返回值就是null，并非是0，那么這個(gè)問(wèn)題要怎么處理呢？

同樣MySQL也給大家提供了對(duì)應(yīng)函數(shù)，就是ifnull

select ifnull(sum(num), 0) from goods where id>4;

[圖片上傳失敗...(image-11c500-1651801816061)]

五、這是在難為誰(shuí)？

當(dāng)一個(gè)字段的值存在null值，若要進(jìn)行null值查詢時(shí)，必須要使用isnull或者ifnull進(jìn)行匹配查詢，又或者使用is null，is not null。

而常用的表達(dá)式就不能再進(jìn)行使用了，有工作經(jīng)驗(yàn)的還好的，要是新人的話會(huì)很難受。

接下來(lái)看幾個(gè)新人經(jīng)常犯的錯(cuò)誤

錯(cuò)誤一

對(duì)存在null值的字段使用表達(dá)式進(jìn)行過(guò)濾，正確用法應(yīng)該是is null 或者 is not null

select * from user where name<>null;

[圖片上傳失敗...(image-2a5427-1651801816061)]

錯(cuò)誤二

依然是使用表達(dá)式，同樣可以使用isnull

[圖片上傳失敗...(image-3286a-1651801816061)]

六、總結(jié)

說(shuō)了這么多也都感覺(jué)到了字段設(shè)置為null的麻煩之處，不過(guò)幸好的是MySQL對(duì)使用is null、isnull()等依然可以使用上索引。

咔咔目前所在的公司存在大量字段默認(rèn)值就是null，于是代碼中就大量存儲(chǔ)ifnull、is null、is not null等代碼。

一般字段數(shù)值類型的默認(rèn)值就給成0，字符串的給個(gè)空也行，千萬(wàn)不要給null了哈！

推薦閱讀

死磕MySQL系列總目錄

闖禍了，生成環(huán)境執(zhí)行了DDL操作《死磕MySQL系列 十四》

聊聊MySQL的加鎖規(guī)則《死磕MySQL系列 十五》

為什么不讓用join？《死磕MySQL系列 十六》

MySQL對(duì)JOIN做了那些不為人知的優(yōu)化《死磕MySQL系列 十七》

堅(jiān)持學(xué)習(xí)、堅(jiān)持寫(xiě)作、堅(jiān)持分享是咔咔從業(yè)以來(lái)所秉持的信念。愿文章在偌大的互聯(lián)網(wǎng)上能給你帶來(lái)一點(diǎn)幫助，我是咔咔，下期見(jiàn)。