mysql 8.0 递归实现菜单树

mysql 8.0 递归实现菜单树

WITH recursive rec_menu( id, menu_id, sup_menu, menu_name, `level`, rownum, order_str)
AS (
SELECT T0.id, T0.menu_id,T0.sup_menu, T0.menu_name, 0 AS  `level`,T0.id as rownum , CAST(T0.id AS CHAR) order_str
FROM  menu  T0 
WHERE T0.sup_menu is NULL
UNION ALL  
SELECT T1.id, T1.menu_id, T1.sup_menu, T1.menu_name, T2.`level`+1, T2.rownum, CONCAT(T2.order_str,T1.id) order_str
FROM menu T1
INNER JOIN rec_menu T2 
ON T1.sup_menu = T2.menu_id 
)
select   menu_id,  sup_menu, menu_name, `level`, rownum, order_str
FROM rec_menu  
ORDER BY rownum,order_str;

注意重点：

1、mysql 需要自增id  PK字段列。

2、菜单menu 表主要有 id, menu_id, parent_id, menu_name 字段。

3、排序需要两级，一级根排序，二级叶子排序。

 

优化版本：

WITH  recursive rec_menu (id, menu_id, sup_menu, menu_name, disp_no, `level`, rownum, order_str)
AS (-- 定义一个临时sql快,将原始表里按照排序字段排好后,连接@rownum固定下来, disp_no 优先级最高,解决根排序问题  
WITH T0 AS 
   (  SELECT a.id, a.menu_id, a.sup_menu, a.menu_name, a.disp_no 
			FROM  
			(SELECT id, menu_id,sup_menu, menu_name,disp_no FROM  menu  ORDER BY disp_no ASC) a,
			(SELECT @rownum:=0) b 
	  )   -- 连接起始数据和union all连接体
SELECT T0.id, T0.menu_id, T0.sup_menu, T0.menu_name, T0.disp_no, 1 	`level`, @rownum := @rownum + 1 rownum, CONVERT(@rownum,char(200000)) order_str 
FROM T0
WHERE T0.sup_menu IS NULL  
UNION ALL 
SELECT T0.id,  T0.menu_id, T0.sup_menu, T0.menu_name,T0.disp_no,T1.`level` + 1, @rownum := @rownum + 1 rownum, CONCAT(T1.order_str, '-', @rownum) order_str 
FROM T0, rec_menu T1  
WHERE T0.sup_menu = T1.menu_id )
-- 显示树深度tree 
SELECT id,  menu_id, sup_menu, menu_name,disp_no,  `level`, rownum, order_str, CONCAT(LPAD('-', (`level` - 1) * 2, '-'), id) tree
FROM rec_menu 
ORDER BY
 -- 第一顺序必须是取第一个数字进行排序,因为多个根节点的顺序。 注意深度优先算法。
 -- 第二顺序才是叶子节点 2-10,2-11,2-11-13,2-12这类排序
CAST(( 
     CASE WHEN INSTR(order_str,'-') <= 0  -- 根节点 
				 THEN  order_str 
				 ELSE SUBSTR(order_str,1,INSTR(order_str,'-')-1) 
		 END) 
AS DOUBLE), order_str;