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

短命女 2023-10-04 17:46 123阅读 0赞

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

**数据准备：**

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> <p>6</p> <p>7</p> <p>8</p> <p>9</p> </td> 
   <td> <p><code>create</code>&nbsp;<code>table</code>&nbsp;<code>user</code>&nbsp;<code>(</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>id&nbsp;</code><code>int</code><code>,</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>name</code>&nbsp;<code>varchar</code><code>(20),</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>index</code><code>(id)</code></p> <p><code>)engine=innodb;</code></p> <p></p> <p><code>insert</code>&nbsp;<code>into</code>&nbsp;<code>user</code>&nbsp;<code>values</code><code>(1,</code><code>'shenjian'</code><code>);</code></p> <p><code>insert</code>&nbsp;<code>into</code>&nbsp;<code>user</code>&nbsp;<code>values</code><code>(2,</code><code>'zhangsan'</code><code>);</code></p> <p><code>insert</code>&nbsp;<code>into</code>&nbsp;<code>user</code>&nbsp;<code>values</code><code>(3,</code><code>'lisi'</code><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

**说明：**

id为索引，非唯一(non unique)，允许空(null)。

**知识点1（热身）：****负向查询不能命中索引，会导致全表扫描。**

![9bda0d2f57f083756bd31ce4207f15a9.png][]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>explain&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>user</code>&nbsp;<code>where</code>&nbsp;<code>id!=1;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

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

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

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

![046fd69d81508235412ebbb1d62c1772.png][]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>insert</code>&nbsp;<code>into</code>&nbsp;<code>user</code><code>(</code><code>name</code><code>)&nbsp;</code><code>values</code><code>(</code><code>'wangwu'</code><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

再次执行不等于查询。

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

答错了！

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

![c2abc567debb160e313d76b446a9c086.png][]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>user</code>&nbsp;<code>where</code>&nbsp;<code>id!=1&nbsp;</code><code>or</code>&nbsp;<code>id&nbsp;</code><code>is</code>&nbsp;<code>null</code><code>;　　</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

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

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

![85a306ad618fa51cbab37d5f0854ad16.png][]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>explain&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>user</code>&nbsp;<code>where</code>&nbsp;<code>id=1;　　</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

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

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

![1831ebc60736e55fcd31444d56ee7679.png][]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>explain&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>user</code>&nbsp;<code>where</code>&nbsp;<code>id&nbsp;</code><code>is</code>&nbsp;<code>null</code><code>;　</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

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

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

![b294a617fa656434deda49d45fbe4795.png][]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>explain&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>user</code>&nbsp;<code>where</code>&nbsp;<code>id=1&nbsp;</code><code>or</code>&nbsp;<code>id&nbsp;</code><code>is</code>&nbsp;<code>null</code><code>;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

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

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

![6a2ae09aa193bae15a4ac5de65f8cab4.png][]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p><code>explain&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>user</code>&nbsp;<code>where</code>&nbsp;<code>id=1</code></p> <p><code>union</code></p> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>user</code>&nbsp;<code>where</code>&nbsp;<code>id&nbsp;</code><code>is</code>&nbsp;<code>null</code><code>;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

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

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

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

**总结**

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

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

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

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

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

[9bda0d2f57f083756bd31ce4207f15a9.png]: https://img-blog.csdnimg.cn/img_convert/9bda0d2f57f083756bd31ce4207f15a9.png
[046fd69d81508235412ebbb1d62c1772.png]: https://img-blog.csdnimg.cn/img_convert/046fd69d81508235412ebbb1d62c1772.png
[c2abc567debb160e313d76b446a9c086.png]: https://img-blog.csdnimg.cn/img_convert/c2abc567debb160e313d76b446a9c086.png
[85a306ad618fa51cbab37d5f0854ad16.png]: https://img-blog.csdnimg.cn/img_convert/85a306ad618fa51cbab37d5f0854ad16.png
[1831ebc60736e55fcd31444d56ee7679.png]: https://img-blog.csdnimg.cn/img_convert/1831ebc60736e55fcd31444d56ee7679.png
[b294a617fa656434deda49d45fbe4795.png]: https://img-blog.csdnimg.cn/img_convert/b294a617fa656434deda49d45fbe4795.png
[6a2ae09aa193bae15a4ac5de65f8cab4.png]: https://img-blog.csdnimg.cn/img_convert/6a2ae09aa193bae15a4ac5de65f8cab4.png