盘点数据库中的一些坑（一）

前言

此文是个人根据日常工作中遇到的坑以及通过各种资料总结的sql 的踩坑点，有些坑如果在编写sql的时候不注意确实是容易翻车，所以这里一并进行总结。

前置条件

在讲解具体的踩坑点之前，我们构建一个简单的表：

CREATE TABLE `admin` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键，自增',
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户名',
  `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '密码',
  `gender` int DEFAULT NULL COMMENT '1为男，2为女',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;

INSERT INTO `admin` (`id`, `username`, `password`, `gender`) VALUES (1, '小红', '111', 2);
INSERT INTO `admin` (`id`, `username`, `password`, `gender`) VALUES (2, '小蓝', '222', NULL);
INSERT INTO `admin` (`id`, `username`, `password`, `gender`) VALUES (0, '小黄', NULL, 1);

为了进行后续的操作，我们先往里面随便造一些数据：

为什么不能使用`!= null`

首先我们来看一下sql中的null值问题，null值是编程界一个臭名昭著的问题，当然对于数据管理的数据库来说也是没有办法避免的，下面我们根据几个案例列举一些null值的坑来说明为什么不建议使用null值，而是尽量给数据库一个默认值（以MYSQL5.7版本为例）：

比较 `!= null` 和 `is null`

首先我们来看下<>和!= 两种写法的查询结果的区别：

<> 在数据库中表示不等于，虽然和 !=的作用等价，但是有些数据库不支持!=的写法，比如sqlserver，所以使用<> 会比较规范一些。

select id,username,password, gender from admin where gender <> null; 
select id,username,password, gender from admin where gender != null; 
select id,username,password, gender from admin where gender is not null; 
select id,username,password, gender from admin where gender is  null; 
-- 如果使用case when 会有比较有意思的结果：
select id,username,password, case when gender != null then gender end as gender from admin

下面是运行结果：

再次强调使用了mysql的数据库，版本为5.7。

下面是运行结果：
第一条：没有记录
第二条：没有记录
第三条：
1  小红  111  2
3  小黄  NULl 1
第四条：
2  小蓝  222  NULL
第五条：
1  小红  111  NULL
2  小蓝  222  NULL
3  小黄  NULL NULL

这样的sql语句是初学数据库的同学有可能犯错的点，尤其是!= null，is not null这两条语句经常被弄混，他们在表面的含义似乎都是“不为空”，但是实际上他们含义是完全不同的，强烈建议在进行判断数据库字段内容是否为null的时候用not null来表示不为空，但是为什么要这么用始终摸不着头脑，所以笔者并没有就此停止，翻阅了网络的一些资料之后总结出后面的内容，下面我们就从数据库的设计层面来了解一下为什么不能使用!=null。

从数据库设计层面思考

我们从设计的层面进行思考为什么会出现这样的情况，下面是结合知乎给出的答案经过个人总结之后来了解为什么数据库会出现这么多令人烦恼的问题：

多数程序员的认知里面包括编程语言的认知都是1和0，也就是"真和假"，但是主流数据库的设计却并不是如此，他们在设计中使用了第三个条件：不确定 ，什么叫不确定？用我们通俗的语言的来讲就是"404"（找不到），而用程序的话来讲就是“unknown ”（不知道）。但是数据库是只返回查询结果为“true”的结果的，对于false的内容和“unknown ”是不认识的。

这里读者可能又有疑问了，为什么case when 出来的结果是空？为了解释这个问题，这里直接用三张表来进行表示：

三值逻辑真值表（NOT）：

x	NOT x
t	f
u	u
f	t

三值逻辑真值表（AND）：

AND	x	u	f
t	f	u	f
u	u	u	f
f	t	f	f

三值逻辑真值表（OR）：

OR	x	u	f
t	t	t	t
u	t	u	u
f	t	u	f

根据这三个表格，我们基本可以了解到为什么上面的case when 查询会出现如此的结果，null和很多的结果进行比较结果总是unknown，所以如果我们编写 != null 根据上面表格会出现 Unkown，也就是说sql会认为他压根不存在也就不会出现结果了，最后再举几个例子，下面所有语句的执行结果都是 unknown：

1 = NULL
1 = NULL
1 = NULL
1 != NULL
NULL = NULL

null的含义和解释

如果读者对于上面的内容概念依然比较模糊，这里我们再从Null的语义角度来了解，下面同样是关于知乎的一位同学写的答案，这里觉得写的十分不错，就直接粘贴过来了：

null 你可以理解为一片空。=代表等值，假设数据库是一个货架，你可以问货架上的一个格子里装的是1吗（是否=1）？然后传回一个值，这个格子里是1或者不是1。你也可以问格子里装的是0吗？然后返回一个值，是0或者不是0。但是你不能问这个格子里装的是空吗（是否=null） ？因为不存在『装在格子里的空』，只存在『空的格子』 。所以应该问『这个格子是否是空的？（是否 is null） 』所以永远都应该使用 is null 而不是=null。

通常情况下数据库建表的时候默认是 NULL，但在工作中一般建表的时候都会禁止使用 NULL 的！
因为NULL 表示的是什么都没有，它与空字符串、0 这些是不等价的，是不能用于比较的如： <expr> = NULL 、 NULL = '' 得到的结果为 false，判断 NULL 必须使用 IS NULL 或 IS NOT NULL 进行判断。

为什么工作中不使用 NULL？

最后我们再补充为什么工作的时候不能使用NULL几个理由，如果以后需要自己设计数据库的场合，一定要小心null值。

不利于代码的可读性和可维护性，特别是强类型语言，查询 INT 值，结果得到一个 NULL，程序可能会崩溃...如果要兼容这些情况程序往往需要多做很多操作来兜底（异常情况多数发生在Mapper的实体属性映射上）
索引失效：若所在列存在 NULL 值，会影响 count()、 <col> != <value>、 NULL + 1 等查询、统计、运算情景的结果，null值会让可能索引失效。

让人摸不着头脑的not in

介绍完null值之后，我们再来介绍null 另一个比较坑的场景，那就是 not in的时候碰到null，这里我们直接用一个案例亲身感受一下：

select id,username,password,gender from admin 
where gender in (select gender from admin);
-- 运行结果：select gender from admin
-- gender 
-- 2
-- NUll
-- 1
-- 最终结果：
-- id,username,password,gender
-- 1  小红  111  2
-- 3  小黄    1

通常我们使用 in 的查询如果在子查询的结果里面有null会被排除，因为这里的in只会拿出结果为true的数据，所以最终结果是对的也是正常的（如非必要尽量避免使用子查询，此处仅仅做展示），现在我们换一种写法，通过对于上面的sql改写为not in看下会有什么样的效果。

select id,username,password,gender from admin 
where gender not in (select gender from admin);
-- 运行结果：
无记录

为什么会出现这样的情况呢？上面我们进行过详细的解释其实答案就是unkdown 的问题，我们可以把上面的sql语句看作是下面的写法：

select id,username,password,gender from admin where gender <> null;

这样写是不是就好理解多了呢？如果读者依旧无法理解，只要牢记下面的话即可，避免not in 中的结果存在null值：

NOT IN returns 0 records when compared against an unknown value
Since NULL is an unknown, a NOT IN query containing a NULL or NULLs in the list of possible values will always return 0 records since there is no way to be sure that the NULL value is not the value being tested.
NOT IN 与未知值比较时返回 0 条记录
由于“NULL”是未知的，因此在可能值列表中包含“NULL”或“NULL”的“NOT IN”查询将始终返回“0”记录，因为无法确定“NULL” value 不是被测试的值。

另外对于存在not in 语句最理想的解决办法是尽量避免使用NOT IN，除非必须使用并且可以保证结果绝对有默认值， 更好的建议是使用 left join 连接查询进行替代，或者可以使用not exists 语句进行改写：

-- not exists 进行改写
SELECT
  id,
  username,
  PASSWORD,
  gender 
FROM
  admin a 
WHERE
  NOT EXISTS ( SELECT 1 FROM admin b WHERE a.gender = b.gender );
  
-- 使用left join进行改写
SELECT
  a.id,
  a.username,
  a.PASSWORD,
  a.gender 
FROM
  admin a 
  left join admin b on a.gender = b.gender
where 
b.gender is null;

复合列not in 查询

对于单列的not in的避坑我们发现还是比较好掌握的，但是对于复合列的not in查询又会出现什么情况？下面我们先来看一下使用not in的错误用法，注意是错误用法 ：

SELECT
  id,
  username,
  PASSWORD,
  gender 
FROM
  admin 
WHERE
  (password,gender) NOT IN ( SELECT password,gender FROM admin_sub );

如果要正确使用，我们可以使用下面的写法，当然也可以使用not exists进行重写：

SELECT
  a.id,
  a.username,
  a.PASSWORD,
  a.gender 
FROM
  admin a 
WHERE
  ( PASSWORD, gender ) NOT IN ( SELECT PASSWORD, gender FROM admin b WHERE a.gender = b.gender AND a.PASSWORD = b.PASSWORD );

上面的结果我们根据前面的结论可以看作下面内容：

这里可能有点绕


 SELECT
  a.id,
  a.username,
  a.PASSWORD,
  a.gender 
FROM
  admin a 
WHERE
  (gender <> 222 or password <> null) and
  (gender <> null or password <> 1)

因为是or连接，虽然两个都有一个unkdown，但是使用了or进行关联所以他们的结果是true，以上就是not in的一些注意事项。

join的笛卡尔积

我们都知道如果join的时候如果关联条件并不唯一，那么就有可能存在笛卡尔积，当然实际上join内连接查询就是通过两表相乘的笛卡尔积的形式进行拼接的，这时如果两个表的关联条件不是唯一的时候，就有可能存在问题，这个坑比较好理解，所以这里直接总结join的一些特性：

两表直接笛卡尔积的结果数量是两表的数据量相乘
带where条件id相等的笛卡尔积和inner join结果相同，但是inner join效率快一点
left join：主表的关联条件ID为空时拼接连接表的内容为空，right join则相反
full join：等于left join和right join的并集

两张表的关系存在一对多的关系，所以就会出现重复情况，无论是join还是left join，都是先把表以笛卡尔积的方式连接，然后通过on来筛选数据，join只显示符合条件的数据，left join不仅会显示所有满足条件的数据，而且还会把主表没有匹配上的也显示出来，最后需要注意left join后面必须加上on。

一个left join的查询顺序

一个join的过程大致如下：

1、from
2、有多表关联的情况，先产生笛卡尔积
3、on，对产生的笛卡尔积进行筛选
4、join，对on筛选的结果生成一张临时表
5、如果是out join（left），还需要把没匹配上的行数添加和join的数据合并，生成一张临时表
6、where，对临时表进行过滤

exists 真的比in 快么？

在介绍本小节的主题之前，我们回顾exists和in的基本特点和区别。

in的用法

select ? from tab where field in (value1,value2,value3.....)

in允许使用多个值
in 可以简化理解为多个OR语句的拼接
子查询返回结果必须只有一个值，并且选取结果为true的值
子查询会先根据条件把记录全部查出来
在有限并且数据量比较小的时候，in的执行效率很高

exists的用法

exists 返回的结果是一个 boolean 值 true 或者 false ，而不是某个结果集，exists 对外表用 loop 逐条查询，每次查询都会查看 exists 的条件语句。下面是大致的伪代码：

for (i < result.size){
  if(exists(codition == true)){//如果建立有索引，这执行很快,O(1)时间  
      result.add(row) 
  }  
}

in 和 exists区别

in进行查询会优先执行子查询的内容，然后根据结果进行筛选，所以数据量比较小的时候in是比较快的。
exists 语句：根据外表的行数执行N次，检查外表的数据和内表的数据是否是一致的，匹配就放入结果集
两者的区别主要在驱动表的顺序上，同时涉及查询的执行先后问题，另外in可以使用但是不建议使用Not in 而建议使用not exists，至于原因我们前文说过了。

待验证的结论？

网上存在下面的结论，但是并没有实际操作和mysql版本做支撑，后续会根据一个实际的版本进行试验。

外层查询表小于子查询表，则用 exists ，外层查询表大于子查询表，则用 in ，如果外层和子查询表差不多，则爱用哪个用哪个。

2.not exists 比 not in 效率高。

写在最后

不得不说sql是一个伟大发明的同时也有有很多令人烦恼的地方，平时需要多踩坑和积累才行。

巨人的肩膀

MySQL null与not null和null与空值''的区别

is NULL和= NULL，is not NULL和!= NULL有什么区别？

书籍推荐

《SQL进阶教程》：如果对于sql一些特性和细节不熟悉，这本书比较适合补课。

盘点数据库中的一些坑（一）