内容摘要：数据库字段允许空值，会遇到一些问题，此处包含的一些知识点，和大家聊一聊。数据准备：createtableuser(idint,namevarchar(20),index(id))engine=inno

数据库字段允许空值，数据会遇到一些问题，库允开始此处包含的许空系列一些知识点，和大家聊一聊。往往悲

数据准备：

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。

【本文为专栏作者“58沈剑”原创稿件，转载请联系原作者】

戳这里，看该作者更多好文