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

柔情只为你懂 2021-10-13 00:49 184阅读 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（热身）：****负向查询不能命中索引，会导致全表扫描。**

![aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9ZcmV6eGNraFlPdzcwaWNtRFFrbTNDa3ZGSGZ1TkdxNUZTVGtKNmljNUpyQXRNbDVGNno4YmR5aGtDVzY2S09FVnNiVUl6eTM0cm1QcnBMYWI3U05jQ2lhZy82NDA_d3hfZm10PXBuZyZ0cD13ZWJwJnd4ZnJvbT01Jnd4X2xhenk9MSZ3eF9jbz0x][]

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

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

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

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

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

![aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9ZcmV6eGNraFlPdzcwaWNtRFFrbTNDa3ZGSGZ1TkdxNUY1TW5aOGVyNGtMdVFOUXFTc0V6MGd5S1RSWWgzb3AwelhEdUJyeGljdnJyWkJyMDdNMTl5c0NBLzY0MD93eF9mbXQ9cG5nJnRwPXdlYnAmd3hmcm9tPTUmd3hfbGF6eT0xJnd4X2NvPTE][]

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

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

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

再次执行不等于查询。

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

答错了！

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

![aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9ZcmV6eGNraFlPdzcwaWNtRFFrbTNDa3ZGSGZ1TkdxNUY1bmN1ZkJwWnl0TmljVGwzWGlhM2h0aWFzdGJqeHRxMFB1NHpBQ2NSZjl5QVRaTmxLcVNZbzJaTlEvNjQwP3d4X2ZtdD1wbmcmdHA9d2VicCZ3eGZyb209NSZ3eF9sYXp5PTEmd3hfY289MQ][]

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

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

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

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

![aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9ZcmV6eGNraFlPdzcwaWNtRFFrbTNDa3ZGSGZ1TkdxNUZXN0RBVEFGcjl1MGZUd3llNjBTd05aM3Y5RkR3MGlhSkxNNDczdzJtRkRaNGtTWlU0c0hlTWVBLzY0MD93eF9mbXQ9cG5nJnRwPXdlYnAmd3hmcm9tPTUmd3hfbGF6eT0xJnd4X2NvPTE][]

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

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

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

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

![aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9ZcmV6eGNraFlPdzcwaWNtRFFrbTNDa3ZGSGZ1TkdxNUZGMWVZNTFucFE3ZjNZSTFNSWdhZFh1RVNpY0lsQXRIV2hpYjlVdUhKUkxpY0huZEhycGJUTjlFZGcvNjQwP3d4X2ZtdD1wbmcmdHA9d2VicCZ3eGZyb209NSZ3eF9sYXp5PTEmd3hfY289MQ][]

*explain select \* from user where id is null;*

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

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

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

![aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9ZcmV6eGNraFlPdzcwaWNtRFFrbTNDa3ZGSGZ1TkdxNUZtZkhvaWNFMm9DMDV6SExpYnRTVUQ2MVFrSFlCMjVWZUs5Q1J5S1pCZG4xc1o0amVvckg1VWFxQS82NDA_d3hfZm10PXBuZyZ0cD13ZWJwJnd4ZnJvbT01Jnd4X2xhenk9MSZ3eF9jbz0x][]

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

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

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

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

![aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9ZcmV6eGNraFlPdzcwaWNtRFFrbTNDa3ZGSGZ1TkdxNUY3YktDUHRtUXYzVEhYbDUxQ043VGNiWERGTW1EdkFSZllBWlJZYXRKNmljaWFnRTdteDNFZlhKdy82NDA_d3hfZm10PXBuZyZ0cD13ZWJwJnd4ZnJvbT01Jnd4X2xhenk9MSZ3eF9jbz0x][]

*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。*

[aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9ZcmV6eGNraFlPdzcwaWNtRFFrbTNDa3ZGSGZ1TkdxNUZTVGtKNmljNUpyQXRNbDVGNno4YmR5aGtDVzY2S09FVnNiVUl6eTM0cm1QcnBMYWI3U05jQ2lhZy82NDA_d3hfZm10PXBuZyZ0cD13ZWJwJnd4ZnJvbT01Jnd4X2xhenk9MSZ3eF9jbz0x]: /images/20211013/2ea2f00d940f43b591455960109f43a5.png
[aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9ZcmV6eGNraFlPdzcwaWNtRFFrbTNDa3ZGSGZ1TkdxNUY1TW5aOGVyNGtMdVFOUXFTc0V6MGd5S1RSWWgzb3AwelhEdUJyeGljdnJyWkJyMDdNMTl5c0NBLzY0MD93eF9mbXQ9cG5nJnRwPXdlYnAmd3hmcm9tPTUmd3hfbGF6eT0xJnd4X2NvPTE]: /images/20211013/e2155461ede141b497791eccb81a223b.png
[aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9ZcmV6eGNraFlPdzcwaWNtRFFrbTNDa3ZGSGZ1TkdxNUY1bmN1ZkJwWnl0TmljVGwzWGlhM2h0aWFzdGJqeHRxMFB1NHpBQ2NSZjl5QVRaTmxLcVNZbzJaTlEvNjQwP3d4X2ZtdD1wbmcmdHA9d2VicCZ3eGZyb209NSZ3eF9sYXp5PTEmd3hfY289MQ]: /images/20211013/ebbdd88581b6467f92682b4ec7e92112.png
[aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9ZcmV6eGNraFlPdzcwaWNtRFFrbTNDa3ZGSGZ1TkdxNUZXN0RBVEFGcjl1MGZUd3llNjBTd05aM3Y5RkR3MGlhSkxNNDczdzJtRkRaNGtTWlU0c0hlTWVBLzY0MD93eF9mbXQ9cG5nJnRwPXdlYnAmd3hmcm9tPTUmd3hfbGF6eT0xJnd4X2NvPTE]: /images/20211013/3ea334c226f9421fb98b90bfcd9ac7e6.png
[aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9ZcmV6eGNraFlPdzcwaWNtRFFrbTNDa3ZGSGZ1TkdxNUZGMWVZNTFucFE3ZjNZSTFNSWdhZFh1RVNpY0lsQXRIV2hpYjlVdUhKUkxpY0huZEhycGJUTjlFZGcvNjQwP3d4X2ZtdD1wbmcmdHA9d2VicCZ3eGZyb209NSZ3eF9sYXp5PTEmd3hfY289MQ]: /images/20211013/865627cc57644b6a8fa4c10f68d1d7bc.png
[aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9ZcmV6eGNraFlPdzcwaWNtRFFrbTNDa3ZGSGZ1TkdxNUZtZkhvaWNFMm9DMDV6SExpYnRTVUQ2MVFrSFlCMjVWZUs5Q1J5S1pCZG4xc1o0amVvckg1VWFxQS82NDA_d3hfZm10PXBuZyZ0cD13ZWJwJnd4ZnJvbT01Jnd4X2xhenk9MSZ3eF9jbz0x]: /images/20211013/1284406bc8894244a4718278d883e428.png
[aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9ZcmV6eGNraFlPdzcwaWNtRFFrbTNDa3ZGSGZ1TkdxNUY3YktDUHRtUXYzVEhYbDUxQ043VGNiWERGTW1EdkFSZllBWlJZYXRKNmljaWFnRTdteDNFZlhKdy82NDA_d3hfZm10PXBuZyZ0cD13ZWJwJnd4ZnJvbT01Jnd4X2xhenk9MSZ3eF9jbz0x]: /images/20211013/b2a6549e69114815a35033b08548ad26.png