MySQL如何创建数据库权限表代码汇总

MySQL的权限管理分为系统自带权限体系和自定义权限体系两类，系统权限表默认在初始化时自动创建，而业务自定义的权限表需要开发者根据需求手动编写创建代码，下面汇总相关的核心代码示例。

一、MySQL系统自带权限表说明

MySQL初始化后会自动在mysql数据库中创建多张权限相关表，包括user、db、tables_priv等，这些表的创建逻辑由MySQL安装程序自动执行，不需要手动编写创建代码。如果误删了这些表，可以通过重新初始化MySQL实例的方式恢复，初始化命令如下：

# 初始化MySQL数据目录，会自动重建系统权限表
mysqld --initialize --user=mysql --datadir=/var/lib/mysql

二、自定义业务权限表创建代码

业务场景中通常需要自定义权限表来存储更灵活的权限规则，下面是常见的权限表结构设计及创建代码。

1. 用户基础权限表

存储用户的全局权限标识，适合简单的权限管控场景：

-- 创建用户权限表
CREATE TABLE IF NOT EXISTS `user_permission` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` INT UNSIGNED NOT NULL COMMENT '关联用户表ID',
  `permission_code` VARCHAR(50) NOT NULL COMMENT '权限编码，如user:add、order:view',
  `permission_name` VARCHAR(100) NOT NULL COMMENT '权限名称',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_permission` (`user_id`, `permission_code`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户权限表';

2. 角色权限关联表

基于RBAC模型的权限表设计，通过角色关联用户和权限，扩展性更强：

-- 创建角色表
CREATE TABLE IF NOT EXISTS `role` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '角色ID',
  `role_name` VARCHAR(50) NOT NULL COMMENT '角色名称，如admin、editor',
  `role_desc` VARCHAR(200) DEFAULT NULL COMMENT '角色描述',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';

-- 创建角色权限关联表
CREATE TABLE IF NOT EXISTS `role_permission` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `role_id` INT UNSIGNED NOT NULL COMMENT '角色ID',
  `permission_code` VARCHAR(50) NOT NULL COMMENT '权限编码',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_role_permission` (`role_id`, `permission_code`),
  KEY `idx_role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色权限关联表';

-- 创建用户角色关联表
CREATE TABLE IF NOT EXISTS `user_role` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
  `role_id` INT UNSIGNED NOT NULL COMMENT '角色ID',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_role` (`user_id`, `role_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表';

三、权限分配相关代码

创建完权限表后，需要插入对应的权限数据完成权限分配，下面是常见的操作代码：

1. 插入权限数据

-- 插入基础权限数据
INSERT INTO `user_permission` (`user_id`, `permission_code`, `permission_name`) VALUES
(1, 'user:add', '添加用户权限'),
(1, 'user:delete', '删除用户权限'),
(2, 'order:view', '查看订单权限');

2. RBAC模型权限分配

-- 给角色分配权限
INSERT INTO `role_permission` (`role_id`, `permission_code`) VALUES
(1, 'user:add'),
(1, 'user:delete'),
(2, 'order:view');

-- 给用户分配角色
INSERT INTO `user_role` (`user_id`, `role_id`) VALUES
(1, 1),
(2, 2);

四、注意事项

• 创建权限表时字符集建议使用utf8mb4，避免中文乱码问题
• 权限编码建议统一格式，如模块:操作的形式，方便后续管理和查询
• 关联字段建议添加索引，提升权限查询的效率
• 系统权限表的修改需要使用GRANT等专用命令，不要直接修改mysql数据库的表结构

如果是需要给MySQL用户分配系统级权限，不需要创建自定义表，直接使用GRANT命令即可，示例如下：

-- 给用户test分配所有数据库的所有权限
GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' IDENTIFIED BY 'test_password';
-- 刷新权限使配置生效
FLUSH PRIVILEGES;

MySQL权限表创建表GRANT用户权限修改时间：2026-07-19 01:36:35