mysql JOIN ON 多表查询用法

在 MySQL 中，JOIN ... ON 是用于多表查询的关键语法，通过指定表之间的关联条件，将多个表的数据组合起来。结合你之前提到的 RBAC 和权限管理场景，我会详细解释 JOIN ... ON 的用法，并以查询用户权限为例展示如何应用。

---

JOIN ... ON 基本概念

• JOIN：用于连接多个表。
• ON：指定表之间的连接条件，通常基于主键和外键或某些匹配字段。
• 作用：从多个表中提取相关数据，形成一个结果集。

常见 JOIN 类型

1. INNER JOIN（内连接）：只返回满足 ON 条件的记录。
2. LEFT JOIN（左外连接）：返回左表所有记录，右表匹配不到时返回 NULL。
3. RIGHT JOIN（右外连接）：返回右表所有记录，左表匹配不到时返回 NULL。
4. FULL JOIN（全外连接）：返回两表所有记录，MySQL 不直接支持，但可以用 LEFT JOIN 和 RIGHT JOIN 模拟。

基本语法

SELECT 列名
FROM 表1
JOIN 表2 ON 表1.列 = 表2.列
WHERE 条件;

---

示例场景：RBAC 权限查询

假设我们有以下表结构（基于 RBAC）：

• users：用户表（id, username）。
• user_roles：用户-角色关系表（user_id, role_id）。
• roles：角色表（id, role_name）。
• role_permissions：角色-权限关系表（role_id, permission_id）。
• permissions：权限表（id, permission_name, permission_code）。

目标：查询用户 admin（id = 1）的所有权限。

数据示例

• users: id=1, username='admin'
• user_roles: user_id=1, role_id=1
• roles: id=1, role_name='管理员'
• role_permissions: role_id=1, permission_id=1, role_id=1, permission_id=2
• permissions: id=1, permission_name='用户管理', id=2, permission_name='用户查看'

---

使用 JOIN ... ON 查询权限

SQL 查询

SELECT DISTINCT u.username, r.role_name, p.permission_name, p.permission_code
FROM users u
INNER JOIN user_roles ur ON u.id = ur.user_id
INNER JOIN roles r ON ur.role_id = r.id
INNER JOIN role_permissions rp ON r.id = rp.role_id
INNER JOIN permissions p ON rp.permission_id = p.id
WHERE u.id = 1;

逐步解释

1. FROM users u：从 users 表开始，查询用户数据，起别名 u。
2. INNER JOIN user_roles ur ON u.id = ur.user_id：
   • 连接 user_roles 表（别名 ur）。
   • 条件：users.id 等于 user_roles.user_id，找到用户关联的角色。
3. INNER JOIN roles r ON ur.role_id = r.id：
   • 连接 roles 表（别名 r）。
   • 条件：user_roles.role_id 等于 roles.id，获取角色详情。
4. INNER JOIN role_permissions rp ON r.id = rp.role_id：
   • 连接 role_permissions 表（别名 rp）。
   • 条件：roles.id 等于 role_permissions.role_id，找到角色关联的权限。
5. INNER JOIN permissions p ON rp.permission_id = p.id：
   • 连接 permissions 表（别名 p）。
   • 条件：role_permissions.permission_id 等于 permissions.id，获取权限详情。
6. WHERE u.id = 1：筛选用户 id = 1 的数据。
7. DISTINCT：避免重复记录（因为用户可能通过多个角色拥有相同权限）。

查询结果

username	role_name	permission_name	permission_code
admin	管理员	用户管理	user:manage
admin	管理员	用户查看	user:view

---

不同 JOIN 类型的应用

1. INNER JOIN

如上例，只返回匹配的记录。如果某个用户没有角色或权限，结果为空。

2. LEFT JOIN

如果想查询所有用户（即使没有角色或权限），用 LEFT JOIN：

SELECT u.username, r.role_name, p.permission_name
FROM users u
LEFT JOIN user_roles ur ON u.id = ur.user_id
LEFT JOIN roles r ON ur.role_id = r.id
LEFT JOIN role_permissions rp ON r.id = rp.role_id
LEFT JOIN permissions p ON rp.permission_id = p.id
WHERE u.id = 1;

• 如果 admin 没有角色，role_name 和 permission_name 会是 NULL。

3. RIGHT JOIN

如果想保留所有权限（即使没有角色关联），用 RIGHT JOIN（少见）：

SELECT u.username, r.role_name, p.permission_name
FROM users u
RIGHT JOIN user_roles ur ON u.id = ur.user_id
RIGHT JOIN roles r ON ur.role_id = r.id
RIGHT JOIN role_permissions rp ON r.id = rp.role_id
RIGHT JOIN permissions p ON rp.permission_id = p.id;

---

结合菜单树生成

如果要生成菜单按钮树，可以在 permissions 表中利用 parent_id，结合 JOIN 查询后再处理层级关系。参考之前的回答，这里简化为：

SELECT p.id, p.permission_name, p.permission_code, p.type, p.parent_id, p.url
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN role_permissions rp ON ur.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE u.id = 1;

然后用程序（如 Python）根据 parent_id 构建树形结构。

---

注意事项

1. 性能：多表 JOIN 时，确保关联字段有索引（如 user_id, role_id, permission_id）。

   CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);
   CREATE INDEX idx_role_permissions_role_id ON role_permissions(role_id);
   

2. 别名：为表起别名（如 u, ur）提高可读性。
3. 重复数据：用 DISTINCT 或 GROUP BY 消除重复。

---

JOIN ... ON 是 RBAC 查询的基础。