MySQL8的with通用表表达式

CREATE TABLE `sys_menu` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '菜单ID',
  `parent_id` bigint NOT NULL DEFAULT '0' COMMENT '父菜单ID',
  `menu_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '菜单名称',
  `xpath` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'XPATH',
  `sort_number` int DEFAULT '0' COMMENT '显示顺序',
  `path` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '路由地址',
  `component` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '组件路径',
  `query_param` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '路由参数',
  `frame_status` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '是否为外链（Y是 N否）',
  `cache_status` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '是否缓存（Y是 N否）',
  `menu_type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '菜单类型（P产品 M目录 C菜单 F按钮）',
  `visible_status` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '是否显示（Y是 N否）',
  `deactivate_status` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '是否停用（Y是 N否））',
  `permission` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '权限标识',
  `icon` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '菜单图标',
  `select_icon` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '选中图表',
  `create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建者',
  `update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `rank` int DEFAULT '0' COMMENT '层级深度',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=570 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='系统菜单表';

递归的用法

WITH recursive my_cte(id,parent_id,xpath, parent_path) AS (
    -- 根节点查询
    SELECT id,parent_id,xpath,CAST(id AS CHAR(100)) as parent_path FROM sys_menu WHERE parent_id = '0' 
    UNION ALL
    -- 递归查询子节点
    SELECT s2.id,s2.parent_id,s2.xpath, concat(s1.parent_path, '/', s2.id, '/') as parent_path FROM my_cte s1
    JOIN sys_menu s2 ON s1.id = s2.parent_id 
) 
SELECT 
    id,parent_id,xpath, 
    parent_path
FROM my_cte 
ORDER BY parent_path;

　　

1.定义一个临时表  my_cte

2.指定临时表的列名 id, parent_path

SELECT id,CAST(id AS CHAR(100)) as parent_path FROM sys_menu WHERE parent_id = '0'  

 将查询结果填充临时表

3.递归查询

SELECT s2.id, concat(s1.parent_path, '-', s2.id) as parent_path FROM my_cte s1 JOIN sys_menu s2 ON s1.id = s2.parent_id 

 将查询结果填充临时表