如何在MySQL 8.0中通过SQL查询完整无限级菜单树结构

场景说明 #

menudf表通过父级关联字段+同级前序节点字段，实现了不限制层级、排序固定的菜单存储结构，当前已知顶级菜单GUID为0x0000d780，运行环境为MySQL 8.0.29，直接用8.0新增的递归CTE特性即可实现树结构查询，不需要写存储过程或者多次拼接查询。

表结构回顾 #

表名：menudf

• menudf_iden_u：菜单项自身唯一GUID
• menudf_iden_p：当前菜单项绑定的父级菜单项GUID
• menudf_iden_s：当前菜单项同层级的前一个菜单项GUID，用于保证同级菜单排序固定
• oms：菜单项的展示描述信息

实现SQL #

-- 如果菜单层级超过1000层，先放开递归深度限制，比如设置为支持10000层
-- SET SESSION cte_max_recursion_depth = 10000;

WITH RECURSIVE menu_tree AS (
    -- 锚点查询：定位顶级菜单作为遍历起点
    SELECT
        menudf_iden_u,
        menudf_iden_p,
        menudf_iden_s,
        oms,
        1 AS menu_level,
        CAST(oms AS CHAR(2000)) AS full_path
    FROM menudf
    WHERE menudf_iden_u = 0x0000d780

    UNION ALL

    -- 递归关联：深度优先遍历，先遍历子节点再遍历同级节点
    SELECT
        m.menudf_iden_u,
        m.menudf_iden_p,
        m.menudf_iden_s,
        m.oms,
        -- 子节点层级+1，兄弟节点和当前节点层级保持一致
        IF(m.menudf_iden_p = mt.menudf_iden_u, mt.menu_level + 1, mt.menu_level) AS menu_level,
        IF(
            m.menudf_iden_p = mt.menudf_iden_u,
            CONCAT(mt.full_path, ' > ', m.oms),
            CONCAT(SUBSTRING_INDEX(mt.full_path, ' > ', mt.menu_level - 1), ' > ', m.oms)
        ) AS full_path
    FROM menu_tree mt
    INNER JOIN menudf m
      ON (m.menudf_iden_p = mt.menudf_iden_u AND m.menudf_iden_s IS NULL) -- 关联当前节点的第一个子节点
      OR (m.menudf_iden_p = mt.menudf_iden_p AND m.menudf_iden_s = mt.menudf_iden_u) -- 关联当前节点的下一个同级节点
)
SELECT * FROM menu_tree;

注意事项 #

• 查询结果默认按菜单实际展示顺序返回：采用深度优先遍历逻辑，先完整遍历当前节点的所有子节点，再遍历下一个同级节点，不需要额外加排序规则
• 返回字段里menu_level标识菜单层级，顶级菜单为1，每下探一层级数值+1
• 返回字段里full_path记录从顶级菜单到当前节点的完整路径，可直接用于层级路径展示
• 按照表设计逻辑，每个层级的第一个菜单没有前序同级节点，因此menudf_iden_s为NULL。如果业务中该字段存储的是其他占位值（比如父节点ID、固定空GUID），只需要把JOIN条件里的m.menudf_iden_s IS NULL替换成对应判断即可
• MySQL默认递归CTE最大深度为1000，层级超过这个值会报错，执行前按注释放开深度限制即可
• 提前排查数据脏值：如果存在菜单父级指向自己、同级前序指向自己、循环父子/同级关联的情况，会导致递归死循环报错，需要先清理异常数据。

内容的提问来源于stack exchange，提问作者Michel Creemers