应建roles、permissions、role_permissions、user_roles四张表，支持灵活授权；需避免JSON或逗号分隔存储，确保索引可查、审计可控、权限可维护。
角色表和权限表怎么建才支持灵活授权
MySQL 本身不提供原生的角色权限模型（5.7 及以前），得靠自己设计表结构来模拟。核心是把「角色」「权限」「用户-角色」「角色-权限」四张表拆清楚，避免硬编码权限字符串或用 JSON 字段存权限——后期没法走索引、难审计、改权限容易出错。
-
roles表存角色元信息：id、name（如'admin'、'editor'）、description -
permissions表存原子权限项：id、code（如'user:read'、'post:delete'）、description—— 这个code是程序里做鉴权时实际比对的字段 -
role_permissions是关联表，字段只有role_id和permission_id，加联合唯一索引防止重复赋权 -
user_roles同理，存user_id和role_id，支持一个用户多个角色
别用单字段存逗号分隔的权限列表（比如 permissions VARCHAR(255) 存 'user:read,user:write'），这种设计会让 SQL 查询权限变得又慢又脆弱。
如何查某个用户的所有有效权限（含角色继承）
用户权限 = 自身直授权限 + 所属角色的全部权限。但 MySQL 没有递归 CTE（8.0+ 才支持），所以得用 JOIN 拼出来。常见错误是只查 user_roles → role_permissions，漏掉用户可能被单独授过某些权限（比如 DBA 给某人加了 'backup:full'，但没给角色）。
SELECT DISTINCT p.code FROM users u LEFT JOIN user_roles ur ON u.id = ur.user_id LEFT JOIN role_permissions rp ON ur.role_id = rp.role_id LEFT JOIN permissions p ON rp.permission_id = p.id WHERE u.id = 123 UNION SELECT p2.code FROM user_permissions up JOIN permissions p2 ON up.permission_id = p2.id WHERE up.user_id = 123;
注意两点：用 UNION 而不是 UNION ALL 去重；user_permissions 表需单独存在（如果业务需要用户粒度授权）。没有这张表就删掉 UNION 后半部分。
INSERT 权限记录时为什么总报外键错误
插入 role_permissions 或 user_roles 时提示 Cannot add or update a child row: a foreign key constraint fails，基本就是两边数据没对齐：
- 插入前没确认
role_id = 5真的存在于roles表中（可能刚删过或 ID 写错） -
permission_id = 99在permissions表里根本不存在（比如权限初始化脚本漏跑，或手误输成999） - 表引擎不是
InnoDB—— MyISAM 不支持外键，约束形同虚设，但插入时仍可能因定义残留报错
建议所有权限相关表统一用 InnoDB，并在建表时显式写上 FOREIGN KEY 约束，而不是靠应用层保证一致性。
MySQL 8.0+ 原生 ROLE 怎么和自定义权限表共存
MySQL 8.0 引入了 CREATE ROLE、GRANT ... TO role 等语法，但它管的是数据库对象级权限（如 SELECT ON mydb.users），和业务系统里的功能权限（如 'order:cancel'）完全不在一个维度。两者可以并存，但别混用：
- MySQL 原生 ROLE 适合 DBA 控制谁能在哪些库/表上执行 DDL/DML —— 安全边界在数据库层
- 你的
roles和permissions表负责应用逻辑层的菜单控制、按钮显隐、API 接口拦截 —— 安全边界在代码里 - 不要试图把
'report:export'映射成GRANT SELECT ON reports TO 'report_role'，语义错位，维护爆炸
真正容易被忽略的是：应用登录后拿到用户 ID，查权限时必须走你自己的表，而不是调 SHOW GRANTS FOR CURRENT_USER —— 后者返回的是 MySQL 层权限，对业务毫无意义。
