第2章 角色树的数据库设计
2.1 核心数据表结构
RBAC 标准五张表构成权限骨架：用户表、角色表、权限表、用户-角色关系表、角色-权限关系表。当角色支持层级（角色树）时，还需在角色表中加入父节点标识字段，并选择一种树形持久化方案。
| 表名 | 核心字段 | 说明 |
|---|---|---|
| user | id, username, password_hash, status | 用户主体 |
| role | id, name, parent_id, path, level | 角色树节点，parent_id 指向父角色 |
| permission | id, code, name, resource_type, action | 原子权限（如 user:create） |
| user_role | user_id, role_id | 多对多关联 |
| role_permission | role_id, permission_id | 多对多关联 |
2.2 角色表树形持久化方案对比
角色树常见实现方式有四种：邻接表、路径枚举、闭包表、嵌套集。在实际 RBAC 系统中，闭包表 与 邻接表 + 路径缓存 组合最为常见。
| 方案 | 存储方式 | 查询子树 | 插入/移动 | 空间 |
|---|---|---|---|---|
| 邻接表 | parent_id | 递归查询 | 极易 | 低 |
| 路径枚举 | path 字段（如 /1/4/7） | LIKE 前缀 | 需重算路径 | 中 |
| 闭包表 | ancestor/descendant 额外关系表 | 多表 JOIN，索引利用好 | 需维护所有祖先-后代记录 | 高 |
| 嵌套集 | left/right 序号 | 范围查询 | 插入/删除成本高 | 中 |
选型建议：角色树深度 ≤ 5 层且修改频繁 → 邻接表；深度 > 5 层且查询远多于写 → 闭包表。课程示例采用 邻接表 + 路径缓存（在 role 表中额外维护 level 和 path 字段，避免递归查询瓶颈）。
2.3 DDL 示例（MySQL/PostgreSQL 兼容）
-- 角色表（邻接表 + 路径缓存）
CREATE TABLE role (
id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(64) NOT NULL,
parent_id BIGINT NULL,
path VARCHAR(500) NOT NULL DEFAULT '', -- 例如 '/1/4/7'
level INT NOT NULL DEFAULT 0,
status TINYINT NOT NULL DEFAULT 1,
FOREIGN KEY (parent_id) REFERENCES role(id) ON DELETE CASCADE,
INDEX idx_parent_id (parent_id),
INDEX idx_level (level)
);
-- 用户表
CREATE TABLE user (
id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(32) NOT NULL UNIQUE,
password VARCHAR(256) NOT NULL,
status TINYINT NOT NULL DEFAULT 1
);
-- 权限表
CREATE TABLE permission (
id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
code VARCHAR(64) NOT NULL UNIQUE,
name VARCHAR(64) NOT NULL,
resource VARCHAR(64) NOT NULL,
action VARCHAR(32) NOT NULL
);
-- 用户-角色关系表
CREATE TABLE user_role (
user_id BIGINT NOT NULL,
role_id BIGINT NOT NULL,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE CASCADE
);
-- 角色-权限关系表
CREATE TABLE role_permission (
role_id BIGINT NOT NULL,
permission_id BIGINT NOT NULL,
PRIMARY KEY (role_id, permission_id),
FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE CASCADE,
FOREIGN KEY (permission_id) REFERENCES permission(id) ON DELETE CASCADE
);
重点：role 表的
parent_id 不能构成循环，业务层在插入/修改角色时必须做环检测；ON DELETE CASCADE 在删除角色时自动删除其子角色及关联关系，但在树形结构中需谨慎——避免误删整棵子树。
2.4 角色树数据库结构图
2.5 常见错误与陷阱
- 循环引用：插入角色时将 parent_id 设为自身或后代，导致递归死循环。插入前必须校验新 parent_id 是否已是当前节点的子孙。
- 级联删除误伤：删除一个中间角色，若使用
ON DELETE CASCADE，将删除它所有子角色。业务上往往需要先转移子角色到父角色或禁止删除。 - 路径缓存不一致：当修改角色 parent_id 后，未同步更新 path 和 level，后续查询可能返回错误层级信息。应通过触发器或业务代码保证原子更新。
2.6 性能优化要点
- 索引策略：角色表
parent_id必须建索引；路径枚举查询常用path LIKE 'prefix%'，可建立前缀索引（如 MySQL 指定长度索引）；用户-角色查询重点在user_id，应建联合索引。 - 闭包表预计算：若选择闭包表，在角色创建/删除时维护 ancestor-descendant 关系，借助
level字段避免全表扫描。 - 缓存层级：对于读多写少的角色树，可在应用层缓存完整的角色树结构（如 Redis ZSET），定期或异步刷新。
注意：不要在每次权限校验时都递归 SQL 查询整棵子树。利用
path 字段（如 path LIKE '1/3/%'）或者闭包表一次 JOIN 即可完成父子权限继承，这是角色树性能的关键。
2.7 部署检查清单
- 执行建表 SQL，确认所有外键、索引创建成功。
- 测试插入一条根角色（parent_id = NULL），并插入子角色。
- 查询子角色路径：
SELECT * FROM role WHERE path LIKE CONCAT((SELECT path FROM role WHERE id = ?), '%')。 - 测试级联删除行为：删除父角色，确认子角色被移除且用户-角色关系同步清理。
- 验证环检测逻辑：尝试将角色 parent_id 改为其自身，预期报错或业务拦截。