sql 子查询的巨坑 ，80%的后端都不知道这个问题

前言

前几天在做一个需求，用户所在的部门被删除了，对应用户的角色也要清空。测试测的时候发现，只要测我的这个需求系统的所有角色都被删除了。。。。。。。

我看了日志也没报错呀，我也没有删除所有账号的角色呀。我有点不相信，就再让测试测一下，发现真的执行了我的需求，账号角色立马就没了。

问题描述

下面删除角色的SQL，仔细一看 子查询的 user_id 字段写错了，卧槽t_user 中没有 user_id字段呀为什么不报错呢

DELETE 
FROM
	t_user_role 
WHERE
	user_id IN ( SELECT user_id FROM t_user WHERE org_id = 1 );

( SELECT user_id FROM t_user WHERE org_id = 1 )，t_user 中没有 user_id 这个字段。

是不是很奇怪，单独执行 SELECT user_id FROM t_user WHERE org_id = 1确实会报错：

1054 - Unknown column 'user_id' in 'field list'

但是我们执行整个SQL：

Affected rows: 3

分析问题

1. 数据版本问题、或者是数据库类型的问题吗 ？
   都不是。因为最开始我用的国产数据库kingbase，以为是国产数据库的bug，结果换成mysql，还是一样。
2. 难道用了in语法，导致里面报错，会忽略这个条件？
   不是。把里面的user_id 字段，换成了外层表和内层表都没有的字段，却会报错。
3. 那就是子查询里面的字段，虽然在子查询表中不存在，但是外层表存在，就不会报错，sql底层肯定是做了什么处理？
   对。去网上搜了一下比较官方的解释就是，子查询里面出现字段不存在就会去外层表查询

翻译如下：如果在子查询中引用的列不存在于子查询的FROM子句引用的表中，但存在于外部查询的FROM语句引用的表内，则查询执行时不会出错。SQL Server使用外部查询中的表名隐式限定子查询中的列

解决方案

1.子查询中，大家还是把字段的限定名加上吧；
2.上面有问题的SQl,实际上应该等于下面这条SQL吧

DELETE 
FROM
	t_user_role 
WHERE
	user_id IN ( SELECT user_id FROM t_user,t_user_role WHERE org_id = 1 );

测试SQL脚本需要自取

建表

-- 建表
CREATE TABLE `t_user` (
  `id` varchar(32) NOT NULL COMMENT '主键id',
  `username` varchar(100) DEFAULT NULL COMMENT '登录账号',
  `password` varchar(255) DEFAULT NULL COMMENT '密码',
  `org_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uniq_sys_user_username` (`username`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户表';

CREATE TABLE `t_user_role` (
  `id` varchar(32) NOT NULL COMMENT '主键id',
  `user_id` varchar(32) DEFAULT NULL COMMENT '用户id',
  `role_id` varchar(32) DEFAULT NULL COMMENT '角色id',
  `tenant_id` int(10) DEFAULT '0' COMMENT '租户ID',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_sur_user_id` (`user_id`) USING BTREE,
  KEY `idx_sur_role_id` (`role_id`) USING BTREE,
  KEY `idx_sur_user_role_id` (`user_id`,`role_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户角色表';

-- 插入数据
INSERT INTO `lf-tes`.`t_user` (`id`, `username`, `password`, `org_id`) VALUES ('1788833429027057666', 'f38bfcc5-342a-4a3d-be37-06c6b76a3f3c', '63591882b6c1f51e606b3f2707c0782ee7eb9785ff03088a0b23cf3e8d3da7f852e9253a521518bf132', 6);
INSERT INTO `lf-tes`.`t_user` (`id`, `username`, `password`, `org_id`) VALUES ('1793887804468568065', '6d4b31fb-15c1-4769-8360-4520654ec215', '13', 7911717);
INSERT INTO `lf-tes`.`t_user` (`id`, `username`, `password`, `org_id`) VALUES ('1805118368626364418', '89ad452a-8301-408f-b0cb-a88514d20808', '13', 9267);
INSERT INTO `lf-tes`.`t_user` (`id`, `username`, `password`, `org_id`) VALUES ('1806245320191922178', '555e353e-ac5e-49f6-aa14-3dec585c07e1', '123', 1);
INSERT INTO `lf-tes`.`t_user` (`id`, `username`, `password`, `org_id`) VALUES ('1810256812759429121', '5c852068-a580-4d93-b6d0-8c9eba2d779a', '123', 16);
INSERT INTO `lf-tes`.`t_user` (`id`, `username`, `password`, `org_id`) VALUES ('1810498373703282689', '1e564564-c034-4cf2-a8ac-06c71bd1c598', '3213', 1);
INSERT INTO `lf-tes`.`t_user` (`id`, `username`, `password`, `org_id`) VALUES ('1811311348454297602', 'c92a96cd-c114-47d7-b73d-de69d4db11e6', '2', 9267);
INSERT INTO `lf-tes`.`t_user` (`id`, `username`, `password`, `org_id`) VALUES ('1811323232628805633', '4209f8ed-b4c2-4e21-8c66-1ad95ed35030', 'a044b591cc9dd85f28504f07bd1a6fbfac2913331cb5858012a13e9c50321d6baed824038132277f0', 1);
INSERT INTO `lf-tes`.`t_user` (`id`, `username`, `password`, `org_id`) VALUES ('e9ca23d68d884d4ebb19d07889727dae', '123', '9dab18c9eae63a19', 9267);

INSERT INTO `lf-tes`.`t_user_role` (`id`, `user_id`, `role_id`, `tenant_id`) VALUES ('1', '1788833429027057666', '1', 0);
INSERT INTO `lf-tes`.`t_user_role` (`id`, `user_id`, `role_id`, `tenant_id`) VALUES ('2', '1788833429027057666', '2', 0);
INSERT INTO `lf-tes`.`t_user_role` (`id`, `user_id`, `role_id`, `tenant_id`) VALUES ('3', '1788833429027057666', '3', 0);
INSERT INTO `lf-tes`.`t_user_role` (`id`, `user_id`, `role_id`, `tenant_id`) VALUES ('4', '1805118368626364418', '1', 0);
INSERT INTO `lf-tes`.`t_user_role` (`id`, `user_id`, `role_id`, `tenant_id`) VALUES ('5', '1811311348454297602', '1', 0);

测试脚本

DELETE 
FROM
	t_user_role 
WHERE
	user_id IN ( SELECT user_id FROM t_user WHERE org_id = 1 );