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

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

数据准备：

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 上的不等于查询，如上图所示：

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

（2）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 上的等值查询，能命中索引，如上图所示：

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

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

explain select * from user where id is null;

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

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

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

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

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

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

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

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 工具是一个好东西；

希望大家有收获！

画外音：本文测试于 MySQL5.6。