动态菜单设计

需求： 登录不同用户 显示不同的菜单

思路：根据用户id 左关联表 查询出对应的菜单选项

查询SQL 

select distinct-- 菜单表  去除重复记录sys_menu.id,sys_menu.parentId, sys_menu.name
from
-- 权限表sys_menu-- 角色与权限表       菜单表id = 角色菜单表的菜单id
left join sys_menu_role ON sys_menu.id = sys_menu_role.mid-- 角色表          角色菜单表的uid = 角色表id
left join sys_role ON sys_menu_role.uid = sys_role.id-- 用户角色表      角色表id = 用户角色表role_id
left join sys_user_role ON sys_role.id = sys_user_role.rid-- 用户表          用户角色表user_id = 用户表的id
left join sys_user ON sys_user_role.uid = sys_user.id
wheresys_user.id=1;

需要五张表 

用户表	sys_user
角色表	sys_role
菜单表(权限表)	sys_menu
用户角色关联表	sys_user_role
角色菜单关联表	sys_menu_role

新建表

sys_user   sys_role  sys_menu

CREATE TABLE `sys_user`  (`id` bigint NOT NULL AUTO_INCREMENT,`name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,`nick_name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,`password` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,`mobile` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,`status` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '1' COMMENT '\'用户账号状态\'',`create_time` datetime(3) NULL DEFAULT (now()) COMMENT '\'创建账号时间\'',`recent_login` datetime(3) NULL DEFAULT (now()) COMMENT '\'最近登录时间\'',`user_name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;CREATE TABLE `sys_role`  (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,`nameZh` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '角色名称',PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;CREATE TABLE `sys_menu`  (`id` int NOT NULL AUTO_INCREMENT,`url` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,`path` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,`component` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,`name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,`iconCls` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,`keepAlive` tinyint(1) NULL DEFAULT NULL,`requireAuth` tinyint(1) NULL DEFAULT NULL,`parentId` int NULL DEFAULT NULL,`enabled` tinyint(1) NULL DEFAULT 1,PRIMARY KEY (`id`) USING BTREE,INDEX `parentId`(`parentId` ASC) USING BTREE,CONSTRAINT `sys_menu_ibfk_1` FOREIGN KEY (`parentId`) REFERENCES `sys_menu` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 31 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;-- ----------------------------
-- Records of sys_menu
-- ----------------------------
INSERT INTO `sys_menu` VALUES (1, '/', NULL, NULL, '所有', NULL, NULL, NULL, NULL, 1);
INSERT INTO `sys_menu` VALUES (2, '/', '/home', 'Home', '服务器组1', 'fa fa-user-circle-o', NULL, 1, 1, 1);
INSERT INTO `sys_menu` VALUES (3, '/', '/home', 'Home', '服务器组2', 'fa fa-address-card-o', NULL, 1, 1, 1);
INSERT INTO `sys_menu` VALUES (4, '/', '/home', 'Home', '服务器组3', 'fa fa-money', NULL, 1, 1, 1);
INSERT INTO `sys_menu` VALUES (5, '/', '/home', 'Home', '服务器组4', 'fa fa-bar-chart', NULL, 1, 1, 1);
INSERT INTO `sys_menu` VALUES (6, '/', '/home', 'Home', '服务器组5', 'fa fa-windows', NULL, 1, 1, 1);
INSERT INTO `sys_menu` VALUES (7, '/', '/home', 'Home', '服务器组6', 'fa-solid fa-circle-arrow-up', NULL, 1, 1, 1);
INSERT INTO `sys_menu` VALUES (8, '/', '/home', 'Home', '服务器组7', NULL, NULL, 1, 1, 1);
INSERT INTO `sys_menu` VALUES (9, '/personnel/emp/**', '/per/emp', 'PerEmp', '主机资料', NULL, NULL, 1, 3, 1);
INSERT INTO `sys_menu` VALUES (10, '/personnel/ec/**', '/per/ec', 'PerEc', '待开发', NULL, NULL, 1, 3, 1);
INSERT INTO `sys_menu` VALUES (11, '/personnel/train/**', '/per/train', 'PerTrain', '待开发', NULL, NULL, 1, 3, 1);
INSERT INTO `sys_menu` VALUES (12, '/personnel/salary/**', '/per/salary', 'PerSalary', '待开发', NULL, NULL, 1, 3, 1);
INSERT INTO `sys_menu` VALUES (13, '/personnel/remove/**', '/per/mv', 'PerMv', '待开发', NULL, NULL, 1, 3, 1);
INSERT INTO `sys_menu` VALUES (14, '/salary/sob/**', '/sal/sob', 'SalSob', '待开发', NULL, NULL, 1, 4, 1);
INSERT INTO `sys_menu` VALUES (15, '/salary/sobcfg/**', '/sal/sobcfg', 'SalSobCfg', '待开发', NULL, NULL, 1, 4, 1);
INSERT INTO `sys_menu` VALUES (16, '/salary/table/**', '/sal/table', 'SalTable', '待开发', NULL, NULL, 1, 4, 1);
INSERT INTO `sys_menu` VALUES (17, '/salary/month/**', '/sal/month', 'SalMonth', '待开发', NULL, NULL, 1, 4, 1);
INSERT INTO `sys_menu` VALUES (18, '/salary/search/**', '/sal/search', 'SalSearch', '待开发', NULL, NULL, 1, 4, 1);
INSERT INTO `sys_menu` VALUES (19, '/statistics/all/**', '/sta/all', 'StaAll', '待开发', NULL, NULL, 1, 5, 1);
INSERT INTO `sys_menu` VALUES (20, '/statistics/score/**', '/sta/score', 'StaScore', '待开发', NULL, NULL, 1, 5, 1);
INSERT INTO `sys_menu` VALUES (21, '/statistics/personnel/**', '/sta/pers', 'StaPers', '待开发', NULL, NULL, 1, 5, 1);
INSERT INTO `sys_menu` VALUES (22, '/statistics/recored/**', '/sta/record', 'StaRecord', 'IP记录', NULL, NULL, 1, 5, 1);
INSERT INTO `sys_menu` VALUES (23, '/system/basic/**', '/sys/basic', 'SysBasic', '基本资料', NULL, NULL, 1, 6, 1);
INSERT INTO `sys_menu` VALUES (24, '/system/cfg/**', '/sys/cfg', 'SysCfg', '系统配置', 'fa fa-windows', NULL, 1, 6, 1);
INSERT INTO `sys_menu` VALUES (25, '/system/log/**', '/sys/log', 'SysLog', '操作日志管理', NULL, NULL, 1, 6, 1);
INSERT INTO `sys_menu` VALUES (26, '/system/hr/**', '/sys/hr', 'SysHr', '操作员管理', NULL, NULL, 1, 6, 1);
INSERT INTO `sys_menu` VALUES (27, '/system/data/**', '/sys/data', 'SysData', '备份恢复数据库', NULL, NULL, 1, 6, 1);
INSERT INTO `sys_menu` VALUES (28, '/system/site/**', '/sys/site', 'SysSite', '配置文件', NULL, NULL, 1, 6, 1);
INSERT INTO `sys_menu` VALUES (29, '/system/term/**', '/sys/term', 'SysTerm', '命令终端', 'fa fa-terminal', NULL, 1, 6, 1);
INSERT INTO `sys_menu` VALUES (30, '/system/deploy/**', '/sys/deploy', 'SysDeploy', '应用管理', 'fa fa-ad', NULL, 1, 6, 1);

中间关联表   

CREATE TABLE `sys_user_role`  (`id` int NOT NULL AUTO_INCREMENT,`uid` int NULL DEFAULT NULL,`rid` int NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE,INDEX `rid`(`rid` ASC) USING BTREE,INDEX `hr_role_ibfk_1`(`uid` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 76 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci COMMENT = '权限id' ROW_FORMAT = DYNAMIC;CREATE TABLE `sys_menu_role`  (`id` int NOT NULL AUTO_INCREMENT,`mid` int NULL DEFAULT NULL,`uid` int NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE,INDEX `mid`(`mid` ASC) USING BTREE,INDEX `rid`(`uid` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 288 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;-- ----------------------------
-- Records of sys_menu_role
-- ----------------------------
INSERT INTO `sys_menu_role` VALUES (1, 1, 1);
INSERT INTO `sys_menu_role` VALUES (2, 2, 1);
INSERT INTO `sys_menu_role` VALUES (3, 3, 1);
INSERT INTO `sys_menu_role` VALUES (4, 4, 1);
INSERT INTO `sys_menu_role` VALUES (5, 5, 1);
INSERT INTO `sys_menu_role` VALUES (6, 6, 1);
INSERT INTO `sys_menu_role` VALUES (7, 7, 1);
INSERT INTO `sys_menu_role` VALUES (8, 8, 1);

中间表进行权限关联 user_role 用户角色表 

uid为 用户表中的id 其中uid=1 是 超级管理员 

uid =1 对应的 

 rid=1 对应的  

角色菜单表   对应关系  menu_role 

sys_menu_role   代表 超级管理员对应7个菜单 选项

uid=1 表示用户id是1 

 mid {1,...7} 对应 菜单表的 

查询结果为 uid=1 对应7个菜单项 

 

 新增一个用户  需要依赖parentID 即子菜单的父菜单