MySQL递归视图实现无限层级父子关系扁平化查询

针对groups表的多级嵌套父子关系，使用MySQL 8.0+支持的递归CTE可以实现任意层级（含最多6级）的子节点与所有父节点配对视图，具体方案如下：

视图创建代码 #

CREATE VIEW group_parent_hierarchy AS
WITH RECURSIVE hierarchy AS (
    -- 锚点成员：获取所有直接父子配对（一级父节点）
    SELECT 
        g.id AS child_id,
        g.name AS child_name,
        gp.id AS parent_id,
        gp.name AS parent_name,
        1 AS hierarchy_level
    FROM groups g
    JOIN groups gp ON g.parent_id = gp.id
    WHERE g.parent_id IS NOT NULL -- 排除无父节点的根节点

    UNION ALL

    -- 递归成员：向上遍历父节点的父节点，直到根节点
    SELECT 
        h.child_id,
        h.child_name,
        gp.id AS parent_id,
        gp.name AS parent_name,
        h.hierarchy_level + 1 AS hierarchy_level
    FROM hierarchy h
    JOIN groups gp ON h.parent_id = gp.id
    WHERE gp.parent_id IS NOT NULL -- 父节点为根节点时停止递归
)
SELECT child_id, child_name, parent_id, parent_name, hierarchy_level
FROM hierarchy
ORDER BY child_id, hierarchy_level;

代码说明 #

• 锚点成员：先抓取所有节点与其直接父节点的配对，标记层级为1，自动排除无父节点的根节点（若你的根节点parent_id为0而非NULL，将IS NOT NULL替换为<> 0即可）。
• 递归成员：基于上一轮的父节点，继续向上关联其自身的父节点，层级逐次加1，直到遍历到根节点时停止递归。
• 视图结果：每个子节点会对应其所有层级的父节点记录，例如三级子节点会生成3条配对记录（分别对应一级、二级、三级父节点），完全覆盖任意嵌套层级需求（MySQL默认递归深度为1000，远超你提到的最多6级限制）。

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