PHP，MySql权限系统设计_upm权限系统表设计-优快云博客

本文链接：https://blog.youkuaiyun.com/leyangjun/article/details/80708336

本文介绍了一个包含用户、角色、权限等模块的数据库表结构设计，详细展示了各表字段及其关联关系。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

 = = = = = = = = = = = = = = = = 表结构，仅供参考  = = = = = = = = = = = = = = = = = = =
CREATE DATABASE leyangjunDB;
Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名称',
  `fullname` varchar(20) NOT NULL DEFAULT '' COMMENT '真实用户名',
  `telephone` varchar(13) NOT NULL DEFAULT '' COMMENT '手机号',
  `mail` varchar(20) NOT NULL DEFAULT '' COMMENT '邮箱',
  `password` varchar(100) DEFAULT NULL,
  `project_id` varchar(200) NOT NULL DEFAULT '' COMMENT '项目id 可能会挂多个项目json',
  `type` int(11) NOT NULL DEFAULT '0' COMMENT '账号类型(1:后台账号,2:普通账号)',
  `project_num` int(11) NOT NULL DEFAULT '0' COMMENT '项目数',
  `status` int(11) NOT NULL DEFAULT '1' COMMENT '状态: 1正常, 0冻结状态, 2删除',
  `remark` varchar(200) NOT NULL DEFAULT '' COMMENT '备注',
  `operator` varchar(20) NOT NULL DEFAULT '' COMMENT '操作者',
  `user_id` varchar(100) DEFAULT NULL,
  `operate_ip` varchar(20) NOT NULL DEFAULT '' COMMENT '最后一次更新者的ip地址',
  `create_time` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  KEY `index_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'



Create Table: CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '角色id',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '角色名称',
  `project_id` int(11) NOT NULL DEFAULT '0' COMMENT '项目id',
  `status` int(11) NOT NULL DEFAULT '1' COMMENT '状态: 1正常, 2删除',
  `type` int(11) NOT NULL DEFAULT '0' COMMENT '角色类型(1:后台账号,2:前端用户角色)',
  `remark` varchar(200) NOT NULL DEFAULT '' COMMENT '备注',
  `operator` varchar(20) NOT NULL DEFAULT '' COMMENT '操作者',
  `operate_ip` varchar(20) NOT NULL DEFAULT '' COMMENT '最后一次更新者的ip地址',
  `create_time` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  KEY `index_project_id` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='角色表'




Create Table: CREATE TABLE `permission` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '权限id',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '权限名称',
  `permissions_value` text NOT NULL COMMENT '权限值菜单ID json',
  `type` int(11) NOT NULL DEFAULT '3' COMMENT '类型: 1菜单,2按钮,3其他',
  `status` int(11) NOT NULL DEFAULT '1' COMMENT '状态: 1正常，0冻结',
  `remark` varchar(200) NOT NULL DEFAULT '' COMMENT '备注',
  `operator` varchar(20) NOT NULL DEFAULT '' COMMENT '操作者',
  `operate_ip` varchar(20) NOT NULL DEFAULT '' COMMENT '最后一个更新者的ip地址',
  `create_time` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='权限表'



Create Table: CREATE TABLE `user_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '关系id',
  `userid` varchar(50) NOT NULL DEFAULT '' COMMENT '用户id',
  `roleid` varchar(200) NOT NULL DEFAULT '' COMMENT '角色Id，一个用户会有多个角色ID，json',
  `status` int(11) NOT NULL DEFAULT '1' COMMENT '状态: 1正常, 2删除',
  `create_time` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  KEY `index_user_id` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户与角色关系表'


Create Table: CREATE TABLE `role_permission` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '关系id',
  `roleid` varchar(50) NOT NULL DEFAULT '' COMMENT '角色id',
  `permission_id` int(11) NOT NULL DEFAULT '0' COMMENT '权限Id',
  `status` int(11) NOT NULL DEFAULT '1' COMMENT '权限: 1可用,2不可用',
  `create_time` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  KEY `index_roleid` (`roleid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='角色与权限关联表'



Create Table: CREATE TABLE `menus` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `menus_name` varchar(100) NOT NULL DEFAULT '' COMMENT '权限名称',
  `father_id` int(11) NOT NULL DEFAULT '0' COMMENT '父id',
  `route` varchar(200) NOT NULL DEFAULT '' COMMENT '路由',
  `status` int(11) NOT NULL DEFAULT '1' COMMENT '权限: 1启用,2关闭',
  `type` int(11) NOT NULL DEFAULT '1' COMMENT '菜单类型: 1前端,2后台',
  `create_time` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='权限菜单表'
菜单路由看你自己想咋控制：我是这么设计的（服务名+方法名，唯独控制的）

UserLogic/addUser：

INSERT INTO `menus` (`menus_name`,`route`, `father_id`, `status`, `create_time`) VALUES ('添加操作','UserLogic/addUser', '1', '1', '2018-05-18 00:00:00');

Create Table: CREATE TABLE `logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '日志主键',
  `project_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '表记录id',
  `type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '类型: 1添加，2修改，3删除 4:操作',
  `ip` varchar(20) NOT NULL DEFAULT '' COMMENT '登录ip',
  `userid` varchar(64) NOT NULL DEFAULT '' COMMENT '用户id',
  `operator` varchar(64) NOT NULL DEFAULT '' COMMENT '操作者',
  `content` text COMMENT '操作内容json',
  `remark` varchar(200) NOT NULL DEFAULT '' COMMENT '备注说明',
  `create_time` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态: 1有效，0无效',
  PRIMARY KEY (`id`),
  KEY `idx_project_id` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='操作日志表'

 = = = = = = = = = = = = = = = = = = = = = = = = END = = = = = = = = = = = = = = = = = = =

-- 表结构SQL modify
修改： table projects MODIFY `status` int(11) NOT NULL DEFAULT 1 COMMENT '状态: 1正常, 2删除'
增加： ALTER table projects add column `status` int(11) NOT NULL DEFAULT 1 COMMENT '状态: 1正常, 2删除' AFTER getPrice;
      ALTER table project_excel add column `excel_content` MEDIUMTEXT COMMENT '内容json' AFTER seed;
      alter table id_name drop column age,drop column address;

删除：alter table project_trial_user_factor drop radio_button；

修改字段类型：ALTER TABLE user MODIFY COLUMN user_id VARCHAR(100);
            ALTER TABLE user_role MODIFY COLUMN roleid VARCHAR(200) NOT NULL DEFAULT '' COMMENT '角色Id，一个用户会有多个角色ID，json';
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =