织梦移动端网站建设,做网站教学,注册城乡规划师考试,淮安官方网站开发区引言
关联查询一直是非常重要的SQL使用技巧。
在一次查询操作中#xff0c;使用mybatis进行条件查询#xff0c;在没有使用 LEFT JOIN 关键字的情况下是这样写的#xff1a; !-- 查找成员 --select idselectUsers resultMapselectUsers_Res…引言
关联查询一直是非常重要的SQL使用技巧。
在一次查询操作中使用mybatis进行条件查询在没有使用 LEFT JOIN 关键字的情况下是这样写的 !-- 查找成员 --select idselectUsers resultMapselectUsers_ResultMapSELECT *FROMsys_user u, sys_user_role ur, sys_role r, sys_dept dwhereif testroleId ! nullAND ur.user_id u.user_idAND ur.role_id r.role_idAND r.role_id #{roleId}/ifif testdeptId ! nullAND u.department_id d.dept_idAND u.department_id #{deptId}/ifif testusername ! nullAND u.username #{username}/if/where/select
这个查询操作的需求是根据roleId(角色id)、deptId(部门id)、username(账号)查找用户列表。
虽然可以正常执行但是查询的结果并不完全正确。
BUG重现
上述查询SQL看似逻辑比较严谨该关联的都画了等号但是查询结果是错误的
首先进行接口调用查询所有的 roleId 12 的用户
真实数据
role表role_id 12user_role表role_id 12 ——user_id 31user表user_id 31 —— department_id 1dept表dept_id 1 —— 研发总监部数据库user_id 31的用户department_id 1 即“研发总监部”。
查询结果 上图是通过swagger API 间接调用的接口执行的就是引言中的SQL语句可以看到虽然 roleId 12 查询正常如果不细心可能不会发现这个问题部门信息为什么会是 deptId 9 的 “炼丹部”
错误原因分析
经过仔细思考得出结论 在错误的SQL中我们的 ID关联条件 都写在了WHERE子句中且通过动态SQL进行分支执行。 这就导致了由于只传入了roleId 12 的条件而deptId未作为查询条件传入此时 user.department_id dept.dept_id 也不会对查询进行外键约束换言之这个约束条件可能会被WHERE动态拼接后的SQL所舍弃。因此导致了查询结果中用户与部门的对应关系与数据库实际的对应关系不一致的情况。 引入LEFT JOIN 解决方案
清晰了问题的症结所在那么如何解决问题呢
我们的要求是不论WHERE子句中的查询条件有或没有都要将 id 进行关联。不论是 user.role_id role.role_id 还是user.department_id dept.dept_id 都要在任何查询情况下进行关联这样就可以得出与数据库对应关系相符的数据。
最终加入LEFT JOIN后的SQL是这样的
!-- 查找成员 此SQL必须用left join因为如果将关联条件写在where中分支将会忽略未执行的关联条件导致查询结果出错--select idselectUsers resultMapselectUsers_ResultMapSELECT *FROM (SELECT u.*, ur.role_idFROM sys_user uLEFT JOIN sys_user_role urON u.user_id ur.user_id) u_roleLEFT JOIN sys_role r ON u_role.role_id r.role_idLEFT JOIN sys_dept d ON u_role.department_id d.dept_idwhereif testroleId ! nullAND r.role_id #{roleId}/ifif testdeptId ! nullAND u_role.department_id #{deptId}/ifif testusername ! nullAND u_role.username #{username}/if/where/select 声明一个问题为什么会有子查询 子查询主要是解决 user 表、user_role 表、role 表之间的关联关系其中user_role 表是一个只存储 user_id 和 role_id 的中间表这条子查询仅仅适用于 一个用户只拥有一个角色的情况角色是用户一个分组本来可以完全不用中间表但是为了后期扩展为用户-角色 呈多对多的关系故加入user_role 中间表。 针对于上述实际的SQL语句来说这条子查询仅仅是将 用户所对应的唯一的 role_id 拼接到了user表的末尾并连同user表的所有数据一同查出。这里其实也可以使用一个LEFT JOIN 但是由于子查询中的WHERE 子句一定会执行因此这样写也是可以的。 另外注意 子查询一定要记得加别名否则SQL执行会报错 子查询一定要记得加别名否则SQL执行会报错 子查询一定要记得加别名否则SQL执行会报错 修改后测试
同样只传入 roleId 12 查询全部用户 可以看到查出的用户已经与数据库的关联信息保持一致了其他的成员也都是如此。说明我们的SQL执行结果符合我们的期望。
复习LEFT JOIN
定义 LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行即使在右表 (table_name2) 中没有匹配的行。 语法 SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_nametable_name2.column_name 综上就是对于SQL关联查询的爬坑随笔比较 隐蔽的一个错误。希望能够对大家有所帮助。欢迎文末留言。
