采用五张表结构：users、roles、permissions、user_roles、role_permissions，通过多对多关联实现灵活权限控制，避免硬编码或json存储，确保索引、唯一约束与缓存同步机制完备。
角色表和权限表怎么建才不翻车
直接用三张表：用户表 users、角色表 roles、权限表 permissions，再加两张关联表——user_roles（用户-角色多对多）和 role_permissions（角色-权限多对多）。别把权限字段塞进角色表里硬编码，也别用 JSON 字段存权限列表，后期查权限、审计、动态授权全会卡住。
关键点：
-
roles表至少含id、name（如'admin'、'editor'）、description -
permissions表用唯一字符串标识权限，比如'post:create'、'user:delete'，别用中文或带空格的描述 -
role_permissions必须加联合唯一索引：UNIQUE KEY `role_id_permission_id` (`role_id`,`permission_id`)，防止重复赋权
查某个用户有哪些权限，SQL 怎么写才高效
典型需求：登录后加载当前用户的全部权限用于前端按钮控制或后端接口校验。别嵌套三层子查询，用 JOIN + GROUP_CONCAT 或应用层聚合更稳。
推荐写法（MySQL 5.7+）：
SELECT DISTINCT p.code 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 = 123;
注意：
- 确保
user_roles(user_id)、role_permissions(role_id)、permissions(id)都有索引 - 如果权限数量大（>100 条），避免用
GROUP_CONCAT拼成单字段返回，容易截断；前端/服务层合并更可控 - 不要在 WHERE 里写
p.code LIKE 'post:%'做模糊匹配来查某类权限——这是设计倒置，应提前归类或加category字段
给角色批量加权限时，INSERT IGNORE 还是 REPLACE INTO
上线新功能要给 'editor' 角色新增 'comment:audit' 权限，但不确定是否已存在。用 INSERT IGNORE 更安全。
示例（先查出 role_id 和 permission_id）：
INSERT IGNORE INTO role_permissions (role_id, permission_id) VALUES (4, 27), (4, 28);
不用 REPLACE INTO 的原因：
-
REPLACE INTO实际是「删+插」，会触发 DELETE 触发器、影响自增 ID、可能误删其他关联数据 - 如果
role_permissions有 ON DELETE CASCADE 外键，风险放大 -
INSERT IGNORE只跳过冲突行，其余正常插入，语义清晰
权限变更后，缓存怎么同步才不漏判
权限不是静态配置，运营随时可能调整角色权限。Redis 缓存用户权限时，不能只按 user_id 缓存，得把「用户 → 角色 → 权限」链路的变更都纳入失效逻辑。
实操建议：
- 给每个
role_id单独缓存其权限列表，key 形如role_perms:4；用户权限由应用层组合多个 role key 获取 - 当往
role_permissions插入或删除记录时，主动DEL role_perms:{role_id} - 不要依赖过期时间自动清理——权限错一次，就可能越权访问或功能不可用
- 如果用了连接池且权限检查在 SQL 层（如视图或存储过程），注意 MySQL 查询缓存已弃用，别白费劲
最易被忽略的是：权限变更操作和缓存失效不在同一事务中。哪怕只差几毫秒，中间请求就可能读到旧权限。要么用消息队列异步刷缓存，要么在更新数据库后立即同步 DEL，别省这一步。
