數(shù)據(jù)庫(kù)字段允許空值，會(huì)遇到一些問(wèn)題，此處包含的一些知識(shí)點(diǎn)，和大家聊一聊。

創(chuàng)新互聯(lián)公司是一家專注于做網(wǎng)站、網(wǎng)站制作與策劃設(shè)計(jì),玉龍網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)公司做網(wǎng)站,專注于網(wǎng)站建設(shè)10多年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:玉龍等地區(qū)。玉龍做網(wǎng)站價(jià)格咨詢:13518219792

數(shù)據(jù)準(zhǔn)備：

 
 
 
 

  
  
  
  
create table user (
  
  
  
  
id int,
  
  
  
  
name varchar(20),
  
  
  
  
index(id)
  
  
  
  
)engine=innodb;
  
  
  
  
 
  
  
  
  
insert into user values(1,'shenjian');
  
  
  
  
insert into user values(2,'zhangsan');
  
  
  
  
insert into user values(3,'lisi');
 
 
 
 

說(shuō)明：id為索引，非唯一(non unique)，允許空(null)。

知識(shí)點(diǎn)1(熱身)：負(fù)向查詢不能命中索引，會(huì)導(dǎo)致全表掃描。

 
 
 
 

  
  
  
  
explain select * from user where id!=1;
 
 
 
 

索引字段id上的不等于查詢，如上圖所示：

• type=ALL，全表掃描;
• rows=3，全表只有3行;

知識(shí)點(diǎn)2(劃重點(diǎn))：允許空值，不等于(!=)查詢，可能導(dǎo)致不符合預(yù)期的結(jié)果。

 
 
 
 

  
  
  
  
insert into user(name) values('wangwu');
 
 
 
 

先構(gòu)造一條id為NULL的數(shù)據(jù)，可以看到共有4條記錄。

 
 
 
 

  
  
  
  
select * from user where id!=1;
 
 
 
 

再次執(zhí)行不等于查詢。

你猜結(jié)果集有幾條記錄(共4條，不等于排除1條)?

答錯(cuò)了!

結(jié)果集只有2條記錄，空值記錄記錄并未出現(xiàn)在結(jié)果集里。

 
 
 
 

  
  
  
  
select * from user where id!=1 or id is null;
 
 
 
 

如果想到得到符合預(yù)期的結(jié)果集，必須加上一個(gè)or條件。

畫外音：惡心不惡心，這個(gè)大坑你踩過(guò)沒(méi)有?

知識(shí)點(diǎn)3(附加)：某些or條件，又可能導(dǎo)致全表掃描，此時(shí)應(yīng)該優(yōu)化為union。

 
 
 
 

  
  
  
  
explain select * from user where id=1;
 
 
 
 

索引字段id上的等值查詢，能命中索引，如上圖所示：

• type=ref，走非唯一索引;
• rows=1，預(yù)估掃描1行;

 
 
 
 

  
  
  
  
explain select * from user where id is null;
 
 
 
 

索引字段id上的null查詢，也能命中索引，如上圖所示：

• type=ref，走非唯一索引;
• rows=1，預(yù)估掃描1行;

 
 
 
 

  
  
  
  
explain select * from user where id=1 or id is null;
 
 
 
 

如果放到一個(gè)SQL語(yǔ)句里用or查詢，則會(huì)全表掃描，如上圖所示：

• type=ALL，全表掃描;
• rows=4，全表只有4行;

 
 
 
 

  
  
  
  
explain select * from user where id=1 
  
  
  
  
union
  
  
  
  
select * from user where id is null;
 
 
 
 

此時(shí)應(yīng)該優(yōu)化為union查詢，又能夠命中索引了，如上圖所示：

• type=ref，走非唯一索引;
• rows=1，預(yù)估掃描1行;

畫外音：第三行臨時(shí)表的ALL，是兩次結(jié)果集的合并。

總結(jié)

• 負(fù)向比較(例如：!=)會(huì)引發(fā)全表掃描;
• 如果允許空值，不等于(!=)的查詢，不會(huì)將空值行(row)包含進(jìn)來(lái)，此時(shí)的結(jié)果集往往是不符合預(yù)期的，此時(shí)往往要加上一個(gè)or條件，把空值(is null)結(jié)果包含進(jìn)來(lái);
• or可能會(huì)導(dǎo)致全表掃描，此時(shí)可以優(yōu)化為union查詢;
• 建表時(shí)加上默認(rèn)(default)值，這樣能避免空值的坑;
• explain工具是一個(gè)好東西;

希望大家有收獲!

畫外音：本文測(cè)試于MySQL5.6。

【本文為專欄作者“58沈劍”原創(chuàng)稿件，轉(zhuǎn)載請(qǐng)聯(lián)系原作者】

本文標(biāo)題：數(shù)據(jù)庫(kù)允許空值(null)，往往是悲劇的開(kāi)始（1分鐘系列）
瀏覽路徑：http://uogjgqi.cn/article/djdscgi.html