MySQL实现树形查询

编程语言2024-11-29 16:53:44

背景

通常我们会使用数据库表保存菜单信息。
此类信息一般会使用树形结构存储，即通过parent_id=resource_id方式实现数据库表记录的关联。
在Oracle下我们可以使用start with connect by prior语句构建查询。
使用MySQL8.0以上时，我们也可以使用递归树查询实现相同功能。
这里记录以下MySQL8的递归查询语法写法。

MySQL递归查询

测试表信息准备

CREATE TABLE `perm_resource` (
  `resource_id` varchar(50) NOT NULL COMMENT '资源ID',
  `resource_name` varchar(100) DEFAULT NULL COMMENT '资源名称',
  `resource_parent_id` varchar(50) DEFAULT NULL COMMENT '父节点ID',
  `is_enable` varchar(1) DEFAULT NULL COMMENT '是否启用',
  `order_id` varchar(20) DEFAULT NULL COMMENT '排序ID',
  `resource_path` varchar(200) DEFAULT NULL COMMENT '资源路径',
  `menu_level` varchar(2) DEFAULT NULL COMMENT '菜单级别',
  `belong_systems` varchar(20) DEFAULT NULL COMMENT '所属系统',
  `is_del` varchar(1) DEFAULT NULL COMMENT '是否可删除',
  `resource_type` varchar(1) DEFAULT NULL COMMENT '资源类型(COMM_CODE_ZYLX)',
  `button_id` varchar(50) DEFAULT NULL COMMENT '功能菜单ID',
  `menu_path` varchar(200) DEFAULT NULL COMMENT '树结构路径',
  `login_flag` varchar(1) DEFAULT NULL COMMENT '是否需要登录标志',
  `login_page` varchar(200) DEFAULT NULL COMMENT '登录页面',
  `color` varchar(20) DEFAULT NULL COMMENT '按钮背景颜色',
  `icon` varchar(50) DEFAULT NULL COMMENT '图标资源',
  PRIMARY KEY (`resource_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='资源';

准备测试数据

INSERT INTO `perm_resource` VALUES ('011', '社会保险（个人）', '1', '1', '1', null, '2', '1', '1', null, null, '.1.011.', '1', null, null, null);
INSERT INTO `perm_resource` VALUES ('01101', '个人中心', '011', '1', '10', null, '3', '1', '1', '1', null, '.1.011.01101.', '1', '1', null, null);
INSERT INTO `perm_resource` VALUES ('0110102', '待遇资格', '01101', '1', '2', '/aio/view/neuqsoft/handan/yl/facecompare.jsp', '4', '1', '1', '1', null, '.1.011.01101.0110102.', '1', '1', '#4cd964', '/aio/aio/comm/image/menuIcon10.png');
INSERT INTO `perm_resource` VALUES ('0110103', '缴费证明打印', '01101', '1', '1', '/aio/view/neuqsoft/handan/yl/grjfzm.jsp', '4', '1', '1', '1', null, '.1.011.01101.0110103.', '1', '1', '#ff2d55', '/aio/aio/comm/image/menuIcon3.png');
INSERT INTO `perm_resource` VALUES ('01103', '医疗保险', '011', '1', '30', null, '3', '1', '1', '1', null, '.1.011.01103.', '1', '1', null, null);
INSERT INTO `perm_resource` VALUES ('0110307', '基本信息查询', '01103', '1', '10', '/aio/view/neuqsoft/handan/yb/query_basicinfo.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110307.', '1', '1', '#5ac8fa', '/aio/aio/comm/image/menuIcon14.png');
INSERT INTO `perm_resource` VALUES ('0110308', '参保信息查询', '01103', '1', '20', '/aio/view/neuqsoft/handan/yb/grcbxxquery.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110308.', '1', '1', null, '/aio/aio/comm/image/menuIcon1.png');
INSERT INTO `perm_resource` VALUES ('0110309', '缴费明细查询', '01103', '1', '30', '/aio/view/neuqsoft/handan/yb/query_jfmx.jsp', '4', '1', '1', '1', null, '.1.011.01103.0110309.', '1', '1', '#FF9900', '/aio/aio/comm/image/menuIcon12.png');
INSERT INTO `perm_resource` VALUES ('0110310', '医保账户查询', '01103', '1', '40', '/aio/view/neuqsoft/handan/yb/query_grzh.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110310.', '1', '1', null, '/aio/aio/comm/image/menuIcon17.png');
INSERT INTO `perm_resource` VALUES ('0110311', '医疗消费查询', '01103', '1', '50', '/aio/view/neuqsoft/handan/yb/querydoctor_zhzc.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110311.', '1', '1', null, '/aio/aio/comm/image/menuIcon16.png');
INSERT INTO `perm_resource` VALUES ('0110312', '药品目录', '01103', '1', '60', '/aio/view/neuqsoft/handan/yb/drugscatalog.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110312.', '1', '1', null, '/aio/aio/comm/image/menuIcon4.png');
INSERT INTO `perm_resource` VALUES ('0110313', '诊疗目录', '01103', '1', '70', '/aio/view/neuqsoft/handan/yb/zhenliaomulu.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110313.', '1', '1', null, '/aio/aio/comm/image/menuIcon23.png');
INSERT INTO `perm_resource` VALUES ('0110314', '医疗机构', '01103', '1', '80', '/aio/view/neuqsoft/handan/yb/query_yljg.jsp', '4', '1', '1', '2', null, '.1.011.01103.0110314.', '1', '1', null, '/aio/aio/comm/image/menuIcon21.png');
INSERT INTO `perm_resource` VALUES ('01104', '工伤保险', '011', '1', '40', null, '3', '1', '1', '1', null, '.1.011.01104.', '1', '1', null, null);
INSERT INTO `perm_resource` VALUES ('0110401', '参保信息查询', '01104', '1', '20', '/aio/view/neuqsoft/handan/gs/grcbxxquery.jsp', '4', '1', '1', '2', null, '.1.011.01104.0110401.', '1', '1', '#ff9500', '/aio/aio/comm/image/menuIcon1.png');
INSERT INTO `perm_resource` VALUES ('0110402', '待遇发放查询', '01104', '1', '30', '/aio/view/neuqsoft/handan/gs/query_dyff.jsp', '4', '1', '1', '2', null, '.1.011.01104.0110402.', '1', '1', '#4cd964', '/aio/aio/comm/image/menuIcon22.png');
INSERT INTO `perm_resource` VALUES ('0110403', '基本信息查询', '01104', '1', '10', '/aio/view/neuqsoft/handan/gs/query_basicinfo.jsp', '4', '1', '1', '2', null, '.1.011.01104.0110403.', '1', '1', '#4cd964', '/aio/aio/comm/image/menuIcon14.png');
INSERT INTO `perm_resource` VALUES ('0110404', '缴费明细查询', '01104', '1', '40', '/aio/view/neuqsoft/handan/gs/query_jfmx.jsp', '4', '1', '1', '2', null, '.1.011.01104.0110404.', '1', '1', null, '/aio/aio/comm/image/menuIcon12.png');
INSERT INTO `perm_resource` VALUES ('01105', '企业养老保险', '011', '1', '20', null, '3', '1', '1', '1', null, '.1.011.01105.', '1', '1', null, null);
INSERT INTO `perm_resource` VALUES ('0110501', '灵活人员缴费核定', '01105', '1', '80', '/aio/view/neuqsoft/handan/yl/hdsb.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110501.', '1', '1', null, '/aio/aio/comm/image/lhjfhd.png');
INSERT INTO `perm_resource` VALUES ('0110502', '个人权益单打印', '01105', '1', '85', '/aio/view/neuqsoft/handan/yl/grqyd.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110502.', '1', '1', null, '/aio/aio/comm/image/menuIcon16.png');
INSERT INTO `perm_resource` VALUES ('0110503', '待遇发放查询', '01105', '1', '40', '/aio/view/neuqsoft/handan/yl/query_yljff.jsp', '4', '1', '1', '1', null, '.1.011.01105.0110503.', '1', '1', '#ff2d55', '/aio/aio/comm/image/menuIcon19.png');
INSERT INTO `perm_resource` VALUES ('0110504', '退休金证明信打印', '01105', '1', '90', '/aio/view/neuqsoft/handan/yl/txrygzzm.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110504.', '1', '1', null, '/aio/aio/comm/image/menuIcon17.png');
INSERT INTO `perm_resource` VALUES ('0110505', '基本信息查询', '01105', '1', '10', '/aio/view/neuqsoft/handan/yl/query_basicinfo.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110505.', '1', '1', '#ffcc00', '/aio/aio/comm/image/menuIcon14.png');
INSERT INTO `perm_resource` VALUES ('0110506', '参保信息查询', '01105', '1', '20', '/aio/view/neuqsoft/handan/yl/grcbxxquery.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110506.', '1', '1', '#ff9500', '/aio/aio/comm/image/menuIcon1.png');
INSERT INTO `perm_resource` VALUES ('0110510', '养老账户查询', '01105', '1', '60', '/aio/view/neuqsoft/handan/yl/query_ylyzh.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110510.', '1', '1', null, '/aio/aio/comm/image/menuIcon17.png');
INSERT INTO `perm_resource` VALUES ('0110511', '缴费明细查询', '01105', '1', '30', '/aio/view/neuqsoft/handan/yl/query_jfmx.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110511.', '1', '1', null, '/aio/aio/comm/image/menuIcon12.png');
INSERT INTO `perm_resource` VALUES ('0110512', '缴费证明打印', '01105', '1', '70', '/aio/view/neuqsoft/handan/yl/grjfzm.jsp', '4', '1', '1', '2', null, '.1.011.01105.0110512.', '1', '1', null, '/aio/aio/comm/image/menuIcon3.png');

递归查询SQL语法

with recursive tab1(resource_id,resource_name,menu_level,resource_path,rn,orderstr) AS (
    SELECT resource_id,resource_name,menu_level,resource_path,
           @rn := 1 rn,
           cast(@rn AS CHAR) orderstr
    FROM perm_resource t0
    WHERE t0.belong_systems = '1' AND t0.resource_id='011'

    union all 

    SELECT t1.resource_id,t1.resource_name,t1.menu_level,t2.resource_path,
           @rn := @rn + 1 rn,
           concat(t2.orderstr, '-', @rn)
    FROM perm_resource t1, tab1 t2
    WHERE t1.belong_systems = '1' AND t1.resource_parent_id = t2.resource_id
)
SELECT t3.* FROM tab1 t3 
WHERE t3.rn > '0'
ORDER BY t3.orderstr

递归查询SQL解析

MySQL8的查询语法分为以下几部分：

申明一个递归查询的结果表及表里的字段，如这里的tab1
1.1 申明的递归查询的数据组成为两个SQL查询结果的union all
1.2. 第一个子句查询出起始条件的查询结果，相当于Oracle树形查询中的start with定义的条件
1.3. 第二个子句，通常from子句至少包含两个表，其中一个表为申明的表tab1，在该子句的的where中定义关联关系如：t1.resource_parent_id = t2.resource_id
1.4. 由于union all查询出来的数据排序并不是我们想要的深度优先方式，可以使用变量定义rownum，通过数据连接形成排序字符串orderstr。
针对递归查询生成的表tab1中的数据进行检查。在此查询语句中可以有where和order by子句，order by子句可以使用生成的orderstr字段实现深度优先效果。最终等效于ORACLE的树形查询。

查看全文

https://www.xamrdz.com/lan/5aa2016158.html

背景

MySQL递归查询

递归查询SQL解析

相关文章：