MySQL如何设计角色权限表？项目实战中权限表结构怎么规划

在MySQL项目开发中，角色权限表的设计直接关系到系统的安全性和扩展性，合理的表结构可以避免后续权限逻辑混乱，降低维护成本。目前主流的权限设计模型是RBAC（基于角色的访问控制），核心思路是将权限分配给角色，再将角色分配给用户，减少用户和权限的直接关联。

核心表结构设计

基于RBAC模型，我们需要设计四张核心表，分别是用户表、角色表、权限表、用户角色关联表、角色权限关联表，各表的作用和字段设计如下：

1. 用户表（sys_user）

存储系统用户的基础信息，除了常规的用户ID、账号、密码等字段，还可以根据业务需求添加状态、创建时间等字段。

-- 用户表建表语句
CREATE TABLE `sys_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(50) NOT NULL COMMENT '用户账号',
  `password` varchar(100) NOT NULL COMMENT '用户密码',
  `status` tinyint(1) DEFAULT 1 COMMENT '状态 1正常 0禁用',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统用户表';

2. 角色表（sys_role）

存储系统内的角色信息，角色是权限的载体，比如管理员、普通用户、审核员等角色。

-- 角色表建表语句
CREATE TABLE `sys_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '角色ID',
  `role_name` varchar(50) NOT NULL COMMENT '角色名称',
  `role_code` varchar(50) NOT NULL COMMENT '角色编码',
  `description` varchar(200) DEFAULT NULL COMMENT '角色描述',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_role_code` (`role_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统角色表';

3. 权限表（sys_permission）

存储系统的具体权限信息，权限可以对应菜单、接口、操作按钮等，通常我们会设计成树形结构，方便前端展示和权限分组。

-- 权限表建表语句
CREATE TABLE `sys_permission` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '权限ID',
  `permission_name` varchar(50) NOT NULL COMMENT '权限名称',
  `permission_code` varchar(100) NOT NULL COMMENT '权限编码',
  `parent_id` bigint(20) DEFAULT 0 COMMENT '父权限ID 0表示顶级权限',
  `permission_type` tinyint(1) DEFAULT 1 COMMENT '权限类型 1菜单 2接口 3按钮',
  `url` varchar(200) DEFAULT NULL COMMENT '权限对应地址 接口或菜单路径',
  `sort` int(11) DEFAULT 0 COMMENT '排序号',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_permission_code` (`permission_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统权限表';

4. 用户角色关联表（sys_user_role）

建立用户和角色的多对多关联关系，一个用户可以有多个角色，一个角色也可以分配给多个用户。

-- 用户角色关联表建表语句
CREATE TABLE `sys_user_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `role_id` bigint(20) NOT NULL COMMENT '角色ID',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_role` (`user_id`,`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表';

5. 角色权限关联表（sys_role_permission）

建立角色和权限的多对多关联关系，一个角色可以拥有多个权限，一个权限也可以分配给多个角色。

-- 角色权限关联表建表语句
CREATE TABLE `sys_role_permission` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `role_id` bigint(20) NOT NULL COMMENT '角色ID',
  `permission_id` bigint(20) NOT NULL COMMENT '权限ID',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_role_permission` (`role_id`,`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色权限关联表';

权限校验实现

在实际项目中，用户登录后需要校验其是否拥有某个权限，我们可以通过多表关联查询实现。比如校验用户ID为1的用户是否拥有权限编码为user:add的权限，SQL语句如下：

-- 校验用户是否拥有指定权限
SELECT COUNT(1) AS has_permission
FROM sys_user u
INNER JOIN sys_user_role ur ON u.id = ur.user_id
INNER JOIN sys_role_permission rp ON ur.role_id = rp.role_id
INNER JOIN sys_permission p ON rp.permission_id = p.id
WHERE u.id = 1
  AND p.permission_code = 'user:add';

如果查询结果中has_permission大于0，说明用户拥有该权限，否则没有权限。

设计注意事项

• 权限编码建议统一规范，比如采用模块:操作的格式，方便后续管理和识别。
• 如果用户量较大，权限校验的SQL可以适当添加索引，比如在sys_user_role的user_id字段、sys_role_permission的role_id字段添加普通索引，提升查询效率。
• 如果业务中存在用户直接绑定权限的需求，可以额外增加用户权限关联表，不过会增加逻辑复杂度，非必要不建议添加。
• 权限表如果采用树形结构，查询子权限时可以使用MySQL的递归查询（MySQL8.0以上支持WITH RECURSIVE），或者提前存储所有父权限ID的路径，方便查询。

总结

MySQL角色权限表的设计核心是基于RBAC模型拆分用户、角色、权限三者的关系，通过关联表实现多对多绑定，既保证了权限分配的灵活性，也避免了直接绑定带来的维护问题。实际项目中可以根据业务需求调整字段，比如增加角色的数据权限范围、权限的有效期等，让权限体系更贴合业务场景。

MySQL角色权限表权限设计RBAC修改时间：2026-07-08 23:00:37