1、使用了MySQL 的with 关键字 配合 RECURSIVE 递归查出用户的菜单。
2、例子如下
RBAC(Role-Based Access Control)即:基于角色的权限控制。通过角色关联用户,角色关联权限的方式间接赋予用户权限。
下面设计5个表来完成这个控制。
t_menu表、t_role表、t_role_menu表、t_user表、t_user_role表。其中t_role_menu表示每个角色里拥有的菜单,t_user_role表示每个用户拥有多少个角色。
-- ----------------------------
-- Table structure for t_menu
-- ----------------------------
DROP TABLE IF EXISTS `t_menu`;
CREATE TABLE `t_menu` (
`id` bigint(0) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '菜单名称',
`url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '菜单路径',
`permission_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '菜单权限码',
`icon` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '图标',
`parent_id` bigint(0) NULL DEFAULT NULL COMMENT '父级ID',
`sort_number` int(0) NULL DEFAULT NULL COMMENT '排序',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for t_role
-- ----------------------------
DROP TABLE IF EXISTS `t_role`;
CREATE TABLE `t_role` (
`id` bigint(0) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for t_role_menu
-- ----------------------------
DROP TABLE IF EXISTS `t_role_menu`;
CREATE TABLE `t_role_menu` (
`id` bigint(0) NOT NULL AUTO_INCREMENT,
`role_id` bigint(0) NULL DEFAULT NULL,
`menu_id` bigint(0) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`id` bigint(0) NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for t_user_role
-- ----------------------------
DROP TABLE IF EXISTS `t_user_role`;
CREATE TABLE `t_user_role` (
`id` bigint(0) NOT NULL AUTO_INCREMENT,
`user_id` bigint(0) NULL DEFAULT NULL,
`role_id` bigint(0) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
View Code- 创建表的T-SQL语句
3、查询某个用户的菜单列表如下
WITH RECURSIVE r(`id`, `name`, `url`, `permission_code`, `icon`, `parent_id`, `sort_number`) AS (
SELECT DISTINCT d.* FROM t_user_role a
LEFT JOIN t_role b ON a.role_id = b.id
LEFT JOIN t_role_menu c ON b.id = c.role_id
LEFT JOIN t_menu d ON c.menu_id = d.id
WHERE a.user_id = {userId}
UNION DISTINCT
SELECT m.* FROM t_menu m,r WHERE m.id = r.parent_id
)
SELECT * FROM r;