以下方法生产慎用, 如果一旦存在环形结构, 则可能会出现死循环!!!
表结构
DROP TABLE IF EXISTS `s_menu`;
CREATE TABLE `s_menu` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`parent_id` bigint(20) DEFAULT NULL COMMENT '父级菜单',
`title` varchar(100) NOT NULL COMMENT '名称',
`url` varchar(255) NOT NULL COMMENT '链接',
`type` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '类型 0菜单 1权限',
`sort` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
`icon` varchar(255) DEFAULT NULL COMMENT '图标icon',
`create_time` datetime NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4;
数据
INSERT INTO `s_menu` VALUES ('1', '0', '系统管理', '#', '0', '1', 'layui-icon-set', '2020-05-21 11:33:55');
INSERT INTO `s_menu` VALUES ('2', '1', '菜单管理', '/sys/menu/list.html', '0', '1', null, '2020-05-21 12:23:29');
INSERT INTO `s_menu` VALUES ('3', '1', '角色管理', '/sys/role/list.html', '0', '2', null, '2020-05-21 12:23:29');
INSERT INTO `s_menu` VALUES ('4', '1', '用户管理', '/sys/user/list.html', '0', '3', null, '2020-05-21 12:23:30');
INSERT INTO `s_menu` VALUES ('8', '2', '设备管理', '#', '0', '2', 'layui-icon-cellphone', '2020-06-02 15:18:08');
INSERT INTO `s_menu` VALUES ('9', '8', '设备列表', '/operate/device/list.html', '0', '1', '', '2020-06-02 15:18:52');
INSERT INTO `s_menu` VALUES ('10', '8', '设备日志', '/operate/device_log/list.html', '0', '2', '', '2020-06-02 15:19:28');
INSERT INTO `s_menu` VALUES ('11', '1', '配置管理', '/sys/config/list.html', '0', '4', '', '2020-06-02 16:04:47');
INSERT INTO `s_menu` VALUES ('12', '1', '日志列表', '/sys/log/list.html', '0', '5', '', '2020-06-02 16:39:51');
INSERT INTO `s_menu` VALUES ('13', '0', '工单管理', '/operate/work_order/list.html', '0', '3', 'layui-icon-survey', '2020-06-02 21:32:16');
INSERT INTO `s_menu` VALUES ('14', '1', '机构管理', '/sys/company/list.html', '0', '6', '', '2020-06-03 18:04:22');
查询父级
定义变量r为id l为层数, :=表示赋值, 将当前节点的parent_id赋值给r, 当前层+1赋值给l, 递归查询, 直到r != 0, 然后关联menus表获取title
SELECT T2.id, T2.title
FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM s_menu WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := '9', @l := 0) vars,
s_menu h
WHERE @r <> 0) T1
JOIN s_menu T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC;
结果
1 系统管理
2 菜单管理
8 设备管理
9 设备列表
查询子集
使用GROUP_CONCAT将循环节点父级用逗号分隔存储为ids, 然后使用FIND_IN_SET查找父ID是否在ids内, 如果存在, 则说明子子集, 并且层级为l+1, 最后关联menu表获取title
SELECT u2.id, u2.title
FROM(
SELECT
@ids AS p_ids,
(SELECT @ids := GROUP_CONCAT(id) FROM s_menu WHERE FIND_IN_SET(parent_id, @ids)) AS c_ids,
@l := @l+1 AS LEVEL
FROM s_menu, (SELECT @ids := '8', @l := 0 ) b
WHERE @ids IS NOT NULL
) u1
JOIN s_menu u2
ON FIND_IN_SET(u2.id, u1.p_ids);
结果
8 设备管理
9 设备列表
10 设备日志