MySQL递归查询 获取某节点的各层父集和子集

表结构

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;

查询子集

使用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);