正式開(kāi)始之前，我們先來(lái)看下 MySQL 服務(wù)器的配置和版本號(hào)信息，如下圖所示：

“兵馬未動(dòng)糧草先行”，看完了相關(guān)的配置之后，我們先來(lái)創(chuàng)建一張測(cè)試表和一些測(cè)試數(shù)據(jù)。

-- 如果存在 person 表先刪除
DROP TABLE IF EXISTS person; 
-- 創(chuàng)建 person 表，其中 username 字段可為空，并為其設(shè)置普通索引
CREATE TABLE person (
 id INT PRIMARY KEY auto_increment,
 name VARCHAR(20),
 mobile VARCHAR(13),
 index(name)
) ENGINE='innodb';
-- person 表添加測(cè)試數(shù)據(jù)
insert into person(name,mobile) values('Java','13333333330'),
 ('MySQL','13333333331'),
 ('Redis','13333333332'),
 ('Kafka','13333333333'),
 ('Spring','13333333334'),
 ('MyBatis','13333333335'),
 ('RabbitMQ','13333333336'),
 ('Golang','13333333337'),
 (NULL,'13333333338'),
 (NULL,'13333333339');
select * from person;

構(gòu)建的測(cè)試數(shù)據(jù)，如下圖所示：

有了數(shù)據(jù)之后，我們就來(lái)看當(dāng)列中存在 NULL 值時(shí)，究竟會(huì)導(dǎo)致哪些問(wèn)題？

1.count 數(shù)據(jù)丟失

當(dāng)某列存在 NULL 值時(shí)，再使用 count 查詢?cè)摿?，就?huì)出現(xiàn)數(shù)據(jù)“丟失”問(wèn)題，如下 SQL 所示：

select count(*),count(name) from person;

查詢執(zhí)行結(jié)果如下：

從上述結(jié)果可以看出，當(dāng)使用的是 count(name) 查詢時(shí)，就丟失了兩條值為 NULL 的數(shù)據(jù)丟失。

解決方案

如果某列存在 NULL 值時(shí)，就是用 count(*) 進(jìn)行數(shù)據(jù)統(tǒng)計(jì)。

擴(kuò)展知識(shí)：不要使用 count(常量)

阿里巴巴《Java開(kāi)發(fā)手冊(cè)》強(qiáng)制規(guī)定：不要使用 count(列名) 或 count(常量) 來(lái)替代 count()，count() 是 SQL92 定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語(yǔ)法，跟數(shù)據(jù)庫(kù)無(wú)關(guān)，跟 NULL 和非 NULL 無(wú)關(guān)。

說(shuō)明：count(*) 會(huì)統(tǒng)計(jì)值為 NULL 的行，而 count(列名) 不會(huì)統(tǒng)計(jì)此列為 NULL 值的行。

2.distinct 數(shù)據(jù)丟失

當(dāng)使用 count(distinct col1, col2) 查詢時(shí)，如果其中一列為 NULL，那么即使另一列有不同的值，那么查詢的結(jié)果也會(huì)將數(shù)據(jù)丟失，如下 SQL 所示：

select count(distinct name,mobile) from person;

查詢執(zhí)行結(jié)果如下：

數(shù)據(jù)庫(kù)的原始數(shù)據(jù)如下：

從上述結(jié)果可以看出手機(jī)號(hào)一列的 10 條數(shù)據(jù)都是不同的，但查詢的結(jié)果卻為 8。

3.select 數(shù)據(jù)丟失

如果某列存在 NULL 值時(shí)，如果執(zhí)行非等于查詢（<>/!=）會(huì)導(dǎo)致為 NULL 值的結(jié)果丟失。比如以下這個(gè)數(shù)據(jù)：

我需要查詢除 name 等于“Java”以外的所有數(shù)據(jù)，預(yù)期返回的結(jié)果是 id 從 2 到 10 的數(shù)據(jù)，但當(dāng)執(zhí)行以下查詢時(shí)：

select * from person where name<>'Java' order by id;
-- 或
select * from person where name!='Java' order by id;

查詢結(jié)果均為以下內(nèi)容：

可以看出為 NULL 的兩條數(shù)據(jù)憑空消失了，這個(gè)結(jié)果并不符合我們的正常預(yù)期。

解決方案

要解決以上的問(wèn)題，只需要在查詢結(jié)果中拼加上為 NULL 值的結(jié)果即可，執(zhí)行 SQL 如下：

select * from person where name<>'Java' or isnull(name) order by id;

最終的執(zhí)行結(jié)果如下：

4.導(dǎo)致空指針異常

如果某列存在 NULL 值時(shí)，可能會(huì)導(dǎo)致 sum(column) 的返回結(jié)果為 NULL 而非 0，如果 sum 查詢的結(jié)果為 NULL 就可以能會(huì)導(dǎo)致程序執(zhí)行時(shí)空指針異常（NPE），我們來(lái)演示一下這個(gè)問(wèn)題。

首先，我們先構(gòu)建一張表和一些測(cè)試數(shù)據(jù)：

-- 如果存在 goods 表先刪除
DROP TABLE IF EXISTS goods; 
-- 創(chuàng)建 goods 表
CREATE TABLE goods (
 id INT PRIMARY KEY auto_increment,
 num int
) ENGINE='innodb';
-- goods 表添加測(cè)試數(shù)據(jù)
insert into goods(num) values(3),(6),(6),(NULL);
select * from goods;

表中原始數(shù)據(jù)如下：

接下來(lái)我們使用 sum 查詢，執(zhí)行以下 SQL：

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

查詢執(zhí)行結(jié)果如下：

當(dāng)查詢的結(jié)果為 NULL 而非 0 時(shí)，就可以能導(dǎo)致空指針異常。

解決空指針異常

可以使用以下方式來(lái)避免空指針異常：

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

查詢執(zhí)行結(jié)果如下：

5.增加了查詢難度

當(dāng)某列值中有 NULL 值時(shí)，在進(jìn)行 NULL 值或者非 NULL 值的查詢難度就增加了。

所謂的查詢難度增加指的是當(dāng)進(jìn)行 NULL 值查詢時(shí)，必須使用 NULL 值匹配的查詢方法，比如 IS NULL 或者 IS NOT NULL 又或者是 IFNULL(cloumn) 這樣的表達(dá)式進(jìn)行查詢，而傳統(tǒng)的 =、!=、<>... 等這些表達(dá)式就不能使用了，這就增加了查詢的難度，尤其是對(duì)小白程序員來(lái)說(shuō)，接下來(lái)我們來(lái)演示一下這些問(wèn)題。

還是以 person 表為例，它的原始數(shù)據(jù)如下：

錯(cuò)誤用法 1：

select * from person where name<>null;

執(zhí)行結(jié)果為空，并沒(méi)有查詢到任何數(shù)據(jù)，如下圖所示：

錯(cuò)誤用法 2：

select * from person where name!=null;

執(zhí)行結(jié)果也為空，沒(méi)有查詢到任何數(shù)據(jù)，如下圖所示：

正確用法 1：

select * from person where name is not null;

執(zhí)行結(jié)果如下：

正確用法 2：

select * from person where !isnull(name);

執(zhí)行結(jié)果如下：

推薦用法

阿里巴巴《Java開(kāi)發(fā)手冊(cè)》推薦我們使用 ISNULL(cloumn) 來(lái)判斷 NULL 值，原因是在 SQL 語(yǔ)句中，如果在 null 前換行，影響可讀性；而 ISNULL(column) 是一個(gè)整體，簡(jiǎn)潔易懂。從性能數(shù)據(jù)上分析 ISNULL(column) 執(zhí)行效率也更快一些。

擴(kuò)展知識(shí)：NULL 不會(huì)影響索引

細(xì)心的朋友可能發(fā)現(xiàn)了，我在創(chuàng)建 person 表的 name 字段時(shí)，為其創(chuàng)建了一個(gè)普通索引，如下圖所示：

然后我們用 explain 來(lái)分析查詢計(jì)劃，看當(dāng) name 中有 NULL 值時(shí)是否會(huì)影響索引的選擇。

explain 的執(zhí)行結(jié)果如下圖所示：

從上述結(jié)果可以看出，即使 name 中有 NULL 值也不會(huì)影響 MySQL 使用索引進(jìn)行查詢。

總結(jié)

本文我們講了當(dāng)某列為 NULL 時(shí)可能會(huì)導(dǎo)致的 5 種問(wèn)題：丟失查詢結(jié)果、導(dǎo)致空指針異常和增加了查詢的難度。因此在最后提倡大家在創(chuàng)建表的時(shí)候盡量設(shè)置 is not null 的約束，如果某列確實(shí)沒(méi)有值，可以設(shè)置空值（’’）或 0 作為其默認(rèn)值。

最后：大家還有因?yàn)?NULL 而造成的各種坑嗎？歡迎評(píng)論區(qū)補(bǔ)充留言。

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

最新評(píng)論