一个 Sql 语句引发的诡异事件

大家好，我是娟姐。

01

App内测的一个下午，测试组的小美女就向我反馈了一个事情。一个身在外地的小哥哥新注册的账号，他和小美女并无交集，但是他俩却莫名成为好友了。老板听闻后，直呼诡异。

“在两点到四点之间，我都在忙别的事情，并未使用App。但当我打开App时，我发现他居然成了我的好友？并且给我发了信息！”，小美女盯着我的眼睛说。

“有没有其他人使用过你的账号？”

“没有，如果有的话，我的账号会被挤掉的！”，小美女十分肯定。

“你俩谁加的谁呢？”

“他收到了我的好友请求，但是我并不认识他，也不知道他的手机号！”

“你的意思是，你没有发好友请求，但是他接收到了你的好友请求，并且同意了？”

“是的，我没有发好友请求，他接收到了我的好友请求，并且同意了。”

“好的，我来调查一下吧！”

我的心里也充满疑惑，但是猜不出具体原因出在哪里，还是调查之后再下结论吧。

02

打开服务器端的接口日志，发现了同意好友请求的记录和通知，但是没有看到加好友的请求记录。查看源码，在申请加好友时并没有打印相关日志。

打开路由日志，在路由日志里还是只有同意好友请求的路由地址，没有申请加好友的路由地址。

这么说，这条加好友请求不是人为发起的，而是无中生有冒出来的？

这时开发的同事给我发了一个图片，申请加好友的一个公共方法，被三处调用。

其中两处在一个方法里，是 if 和 else 的关系，这个方法是通过人为发起的，也就是用户需要先知道对方的手机号码，或者通过群聊，才能申请加好友。

还有一处是自动生成好友请求的业务逻辑，这段业务逻辑就不说了，涉及到商业机密，初步定位问题就出在这里。

自动生成好友请求的业务逻辑非常简单，第一个判断语句前有一个Sql查询语句引起了我的注意，在 left join 的 on 后面，and 了好几个查询条件。

03

现在来模拟一下这个Sql语句。

1、先建立两个表

一个是 user_info 用户表，另一个是 user_addr 用户收货地址表，一个用户可以有多个收货地址。 用户表与收货地址表关系图 user_info 用户表

用户表中，有三个字段，uid 是用户主键不可重复，name是用户名可以重复，is_delete 是删除标识。

user_addr 收货地址表

在收货地址表中，有七个字段，其中 user_uid 是来自用户表的外键，id 为本表的主键。

2、两张表分表录入几条数据

user_info 表中有三条数据

三个用户数据，李四的用户数据已被标识为删除。

user_addr 表中有三条数据

张三有三个收货地址，一个是自己的，一个是张父的，最后一个是张姐的。其他几人暂无收货地址。

3、此次查询的要求是，查看数据库里是否还有一个叫“张父”的收货地址，并且把这个数据所属的用户找出来。

select A.*,B.user_link,B.is_delete from user_info as A
left join user_addr as B on A.uid= B.user_uid and B.is_delete = 0 and B.user_link = '张父'
where A.is_delete = 0 ;

执行结果如下图所示，好家伙，把没有关系的 老王 也扯了进来。估计老王一脸的黑线。

图片

04

也有同学说，我可以不用左连接，可以用内连接呀！

关于内连接和左连接，MySql 的官方文档是这么描述的：

https://dev.mysql.com/doc/refman/5.7/en/join.html

在MySQL中，JOIN、CROSS JOIN 和 INNER JOIN 在语法上是等价的(它们可以相互替换)。在标准SQL中，它们是不相等的。INNER JOIN与ON子句一起使用，否则使用CROSS JOIN。

关于 逗号，内连接、左连接和右连接，它们还有其他区别吗？Mysql 的官方文档是这么描述的：

图片

在没有连接条件的情况下，INNER JOIN 和 逗号 在语义上是等价的：它们都在指定的表之间产生笛卡尔积(也就是说，第一个表中的每一行都与第二个表中的每一行连接)。

但是，逗号操作符的优先级低于INNER JOIN、CROSS JOIN、LEFT JOIN等。如果在 存在连接条件时 将逗号连接与其他连接类型混合使用，则可能会出现“on子句”中未知列“col_name”形式的错误。

与ON一起使用的查询条件 是可以在WHERE子句中使用的任何形式的条件表达式。通常，ON子句用于指定如何连接表的条件，WHERE子句限制在结果集中包含哪些行。

划重点：
ON 子句用于指定如何连接表的条件，WHERE 子句限制在结果集中包含哪些行。

这么说，限制条件要加在 where 后面，两表之间的连接关系才放在 ON 后面。

调整后的Sql语句为：

select A.*,B.user_link,B.is_delete from user_info as A
left join user_addr as B on A.uid= B.user_uid and B.is_delete = 0
where A.is_delete = 0 and  B.user_link = '张父';

查询结果如下图所示，终于查到了我们想要的数据。

图片

可能有同学觉得，where后面的限制条件太多了，挪几个放在ON后面吧，反正都一样。错了，还真不一样。哪些能放在ON后面，哪些不能放在ON后面，还需要仔细掂量一下。

05

如果我把张父的收货地址删除，并且只查询未删除的，收货地址表的is_delete放在哪里一样吗？

select A.*,B.user_link,B.is_delete from user_info as A
left join user_addr as B on A.uid= B.user_uid and B.is_delete = 0
where A.is_delete = 0 and  B.user_link = '张父';

select A.*,B.user_link,B.is_delete from user_info as A
left join user_addr as B on A.uid= B.user_uid 
where A.is_delete = 0 and B.is_delete = 0 and B.user_link = '张父';

图片 图片

这次查询结果却是一致的，为什么呢？

在使用 left join 时，on 和 where 条件的区别如下：

• on 条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

• where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

这样就解释得通了，不管 on 中的条件是否为真，都会返回主表中的记录。把where 条件注释掉再执行：

select A.*,B.user_link,B.is_delete from user_info as A
left join user_addr as B on A.uid= B.user_uid and B.is_delete = 0;
# where A.is_delete = 0 and  B.user_link = '张父';

select A.*,B.user_link,B.is_delete from user_info as A
left join user_addr as B on A.uid= B.user_uid 
# where A.is_delete = 0 and B.is_delete = 0 and B.user_link = '张父';

查询语句1执行结果

第一个sql语句在生成临时表的时候，把收货地址表已删除的数据过滤掉了。排除删除的收获地址，用户表和收获地址表进行了笛卡尔积关联。

查询语句2执行结果

第二个sql语句在生成临时表的时候，没有把收货地址表已删除的数据过滤掉，返回了一个完整的笛卡尔积数据集。

再来执行一下那个有错误的 Sql 语句，把 where 条件注释掉。

select A.*,B.user_link,B.is_delete from user_info as A
left join user_addr as B on A.uid= B.user_uid and B.is_delete = 0 and B.user_link = '张父'
# where A.is_delete = 0 ;

图片

这个 sql 语句在生成临时表的时候，就把联系人为 ‘张父’的数据过滤了出来，由于是临时表，又是笛卡尔积关联，所以另外两个没有收获地址的朋友也会被关联出来。所以呢，问题就出在这里。

以上便是这次诡异事件的调查结果，一个Sql语句写法不当所导致的bug。