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

前言

此文是集体依据日常工作中遇到的坑以及通过各种材料总结的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  23  小黄  NULl 1第四条：2  小蓝  222  NULL第五条：1  小红  111  NULL2  小蓝  222  NULL3  小黄  NULL NULL

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

从数据库设计层面思考

咱们从设计的层面进行思考为什么会呈现这样的状况，上面是联合知乎给出的答案通过集体总结之后来理解为什么数据库会呈现这么多令人烦恼的问题：

少数程序员的认知外面包含编程语言的认知都是1和0，也就是"真和假"，然而支流数据库的设计却并不是如此，他们在设计中应用了第三个条件：不确定 ，什么叫不确定？用咱们艰深的语言的来讲就是"404"（找不到），而用程序的话来讲就是“unknown ”（不晓得）。然而数据库是只返回查问后果为“true”的后果的，对于false的内容和“unknown ”是不意识的。

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

三值逻辑真值表（NOT）：

xNOT x
tf
uu
ft

三值逻辑真值表（AND）：

ANDxuf
tfuf
uuuf
ftff

三值逻辑真值表（OR）：

ORxuf
tttt
utuu
ftuf

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

1 = NULL1 = NULL1 = NULL1 != NULLNULL = 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.genderwhere 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一些个性和细节不相熟，这本书比拟适宜补课。