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

前言

此文是集体依据日常工作中遇到的坑以及通过各种材料总结的 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）：

三值逻辑真值表（AND）：

三值逻辑真值表（OR）：

​ 依据这三个表格，咱们根本能够理解到为什么下面的 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 值。

1. 不利于代码的可读性和可维护性，特地是强类型语言，查问 INT 值，后果失去一个 NULL，程序可能会解体 … 如果要兼容这些状况程序往往须要多做很多操作来兜底（异常情况少数产生在 Mapper 的实体属性映射上）
2. 索引生效：若所在列存在 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.....)

1. in 容许应用多个值
2. in 能够简化了解为多个 OR 语句的拼接
3. 子查问返回后果必须只有一个值，并且选取后果为 true 的值
4. 子查问会先依据条件把记录全副查出来
5. 在无限并且数据量比拟小的时候，in 的执行效率很高

exists 的用法

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

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

in 和 exists 区别

1. in 进行查问会优先执行子查问的内容，而后依据后果进行筛选，所以数据量比拟小的时候 in 是比拟快的。
2. exists 语句：依据表面的行数执行 N 次，查看表面的数据和内表的数据是否是统一的，匹配就放入后果集
3. 两者的区别次要在驱动表的程序上，同时波及查问的执行先后问题，另外 in 能够应用然而不倡议应用 Not in 而倡议应用 not exists，至于起因咱们前文说过了。

待验证的论断？

网上存在上面的论断，然而并没有实际操作和 mysql 版本做撑持，后续会依据一个理论的版本进行试验。

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

2.not exists 比 not in 效率高。

写在最初

​ 不得不说 sql 是一个平凡创造的同时也有有很多令人烦恼的中央，平时须要多踩坑和积攒才行。

伟人的肩膀

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

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

书籍举荐

1. 《SQL 进阶教程》：如果对于 sql 一些个性和细节不相熟，这本书比拟适宜补课。