数据库允许空值(null)，往往是悲剧的开始代码片段

CodeSnippet.Cn
代码片段

数据库允许空值(null)，往往是悲剧的开始

MySql 小笨蛋发布于：2022年07月17日更新于：2022年07月17日 144

> 数据库字段允许空值(null)的问题，你遇到过吗？

**实验过程：**

```sql
CREATE TABLE users ( 
	id INT,
	NAME VARCHAR ( 20 ),
	INDEX ( id ) 
) ENGINE = INNODB;
```

**【说明：id为索引，非唯一(non unique)，允许空(null)】**

```sql
insert into users values(1,'zuoming');

insert into users values(2,'parker');

insert into users values(3,'liqun');
```

【问题一：以下select返回什么？】
`select * from users where id!=1;`

【插入一行，id会出现空值(null)】
`insert into users(name) values('scott');`

【问题二：再次select，会返回什么？】
`select * from users where id!=1;`

一起来看一下，这个小实验，涉及哪些知识点呢？

**知识点1（热身）：负向查询不能命中索引。**
![图片alt](/uploads/images/20220717/111511-98679fa7f5b9419d93b6d1fc33914434.png '代码片段：Www.CodeSnippet.Cn')

`explain select * from users where id!=1;`

索引字段id上的不等于查询，如上图所示：
（1）type=range；
（2）rows=3，全表只有3行；

> 画外音：第一次select的结果。

---

**知识点2（划重点）：允许空值，不等于(!=)查询，可能导致不符合预期的结果。**
![图片alt](/uploads/images/20220717/111742-61491beb6c6b45a897e923e3d764a75f.png '代码片段：Www.CodeSnippet.Cn')

`insert into users(name) values('scott');`

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

`select * from users where id!=1;`

再次执行不等于查询。

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

答错了！

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

> 画外音：第二次select的结果，意不意外？

![图片alt](/uploads/images/20220717/111853-8d8fd81253c9467a96004234da564855.png '代码片段：Www.CodeSnippet.Cn')

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

![图片alt](/uploads/images/20220717/111911-82a849999e7b4dde8c93d513ee0dabab.png '代码片段：Www.CodeSnippet.Cn')

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

---

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

![图片alt](/uploads/images/20220717/112054-ace9435374084c2eb5c41991a49b9b0e.png '代码片段：Www.CodeSnippet.Cn')

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

（1）type=ref，走非唯一索引；
（2）rows=1，预估扫描1行；

------------

![图片alt](/uploads/images/20220717/112150-c57fdda8e62343de9a807624290e8cb9.png '代码片段：Www.CodeSnippet.Cn')

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

（1）type=ref，走非唯一索引；
（2）rows=1，预估扫描1行；

------------

![图片alt](/uploads/images/20220717/112401-2807e0b299f249dd9180e479219692dd.png '代码片段：Www.CodeSnippet.Cn')
```sql
explain select * from users where id=1 
union
select * from users 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工具是一个好东西；

这里⇓感觉得写点什么，要不显得有点空，但还没想好写什么... 返回顶部