数据库允许空值(null)，往往是悲剧的开始（1分钟系列）

小鱼儿 2021-12-15 04:37 190阅读 0赞

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

数据准备：

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（热身）：负向查询不能命中索引，会导致全表扫描。

![640?wx\_fmt=png][640_wx_fmt_png]

explain select \* from user where id!=1;

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

（1）type=ALL，全表扫描；

（2）rows=3，全表只有3行；

知识点2（划重点）：允许空值，不等于(!=)查询，可能导致不符合预期的结果。

![640?wx\_fmt=png][640_wx_fmt_png 1]

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

先构造一条id为NULL的数据，可以看到共有4条记录。

select \* from user where id!=1;

再次执行不等于查询。

你猜结果集有几条记录（共4条，不等于排除1条）？

答错了！

结果集只有2条记录，空值记录记录并未出现在结果集里。

![640?wx\_fmt=png][640_wx_fmt_png 2]

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

如果想到得到符合预期的结果集，必须加上一个or条件。

画外音：恶心不恶心，这个大坑你踩过没有？

知识点3（附加）：某些or条件，又可能导致全表扫描，此时应该优化为union。

![640?wx\_fmt=png][640_wx_fmt_png 3]

explain select \* from user where id=1;

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

（1）type=ref，走非唯一索引；

（2）rows=1，预估扫描1行；

![640?wx\_fmt=png][640_wx_fmt_png 4]

explain select \* from user where id is null;

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

（1）type=ref，走非唯一索引；

（2）rows=1，预估扫描1行；

![640?wx\_fmt=png][640_wx_fmt_png 5]

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

如果放到一个SQL语句里用or查询，则会全表扫描，如上图所示：

（1）type=ALL，全表扫描；

（2）rows=4，全表只有4行；

![640?wx\_fmt=png][640_wx_fmt_png 6]

explain select \* from user where id=1

union

select \* from user where id is null;

此时应该优化为union查询，又能够命中索引了，如上图所示：

（1）type=ref，走非唯一索引；

（2）rows=1，预估扫描1行；

画外音：第三行临时表的ALL，是两次结果集的合并。

总结

（1）负向比较（例如：!=）会引发全表扫描；

（2）如果允许空值，不等于(!=)的查询，不会将空值行(row)包含进来，此时的结果集往往是不符合预期的，此时往往要加上一个or条件，把空值(is null)结果包含进来；

（3）or可能会导致全表扫描，此时可以优化为union查询；

（4）建表时加上默认(default)值，这样能避免空值的坑；

（5）explain工具是一个好东西；

[640_wx_fmt_png]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/YrezxckhYOw70icmDQkm3CkvFHfuNGq5FSTkJ6ic5JrAtMl5F6z8bdyhkCW66KOEVsbUIzy34rmPrpLab7SNcCiag/640?wx_fmt=png
[640_wx_fmt_png 1]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/YrezxckhYOw70icmDQkm3CkvFHfuNGq5F5MnZ8er4kLuQNQqSsEz0gyKTRYh3op0zXDuBrxicvrrZBr07M19ysCA/640?wx_fmt=png
[640_wx_fmt_png 2]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/YrezxckhYOw70icmDQkm3CkvFHfuNGq5F5ncufBpZytNicTl3Xia3htiastbjxtq0Pu4zACcRf9yATZNlKqSYo2ZNQ/640?wx_fmt=png
[640_wx_fmt_png 3]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/YrezxckhYOw70icmDQkm3CkvFHfuNGq5FW7DATAFr9u0fTwye60SwNZ3v9FDw0iaJLM473w2mFDZ4kSZU4sHeMeA/640?wx_fmt=png
[640_wx_fmt_png 4]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/YrezxckhYOw70icmDQkm3CkvFHfuNGq5FF1eY51npQ7f3YI1MIgadXuESicIlAtHWhib9UuHJRLicHndHrpbTN9Edg/640?wx_fmt=png
[640_wx_fmt_png 5]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/YrezxckhYOw70icmDQkm3CkvFHfuNGq5FmfHoicE2oC05zHLibtSUD61QkHYB25VeK9CRyKZBdn1sZ4jeorH5UaqA/640?wx_fmt=png
[640_wx_fmt_png 6]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/YrezxckhYOw70icmDQkm3CkvFHfuNGq5F7bKCPtmQv3THXl51CN7TcbXDFMmDvARfYAZRYatJ6iciagE7mx3EfXJw/640?wx_fmt=png