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、排序需要两级，一级根排序，二级叶子排序。