數據庫字段允許空值，會遇到一些問題，此處包含的一些知識點，和大家聊一聊。

數據準備：

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'); 

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

知識點1(熱身)：負向查詢不能命中索引，會導致全表掃描。

explain select * from user where id!=1; 

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

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

知識點2(劃重點)：允許空值，不等于(!=)查詢，可能導致不符合預期的結果。

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

先構造一條id為NULL的數據，可以看到共有4條記錄。

select * from user where id!=1; 

再次執行不等于查詢。

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

答錯了!

結果集只有2條記錄，空值記錄記錄并未出現在結果集里。

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

如果想到得到符合預期的結果集，必須加上一個or條件。

畫外音：惡心不惡心，這個大坑你踩過沒有?

知識點3(附加)：某些or條件，又可能導致全表掃描，此時應該優化為union。

explain select * from user where id=1; 

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

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

explain select * from user where id is null; 

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

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

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

如果放到一個SQL語句里用or查詢，則會全表掃描，如上圖所示：

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

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

此時應該優化為union查詢，又能夠命中索引了，如上圖所示：

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

畫外音：第三行臨時表的ALL，是兩次結果集的合并。

總結

• 負向比較(例如：!=)會引發全表掃描;
• 如果允許空值，不等于(!=)的查詢，不會將空值行(row)包含進來，此時的結果集往往是不符合預期的，此時往往要加上一個or條件，把空值(is null)結果包含進來;
• or可能會導致全表掃描，此時可以優化為union查詢;
• 建表時加上默認(default)值，這樣能避免空值的坑;
• explain工具是一個好東西;

希望大家有收獲!

畫外音：本文測試于MySQL5.6。

【本文為51CTO專欄作者“58沈劍”原創稿件，轉載請聯系原作者】

戳這里，看該作者更多好文