如何从MySQL层级表中获取子类型的所有父类/超类型？

问题原因 #

你的递归CTE触发无限循环，核心原因是表中存在ObjectType = ObjectSubType的自引用行（比如Iron→Iron），而原代码的递归逻辑未排除这类行，导致每次递归都会重复匹配自身节点，直到触发MySQL的递归次数上限。同时初始查询的条件和递归连接逻辑没有准确指向父节点的层级关系。

修正后的递归CTE代码 #

WITH RECURSIVE ancestorCategories AS (
    -- 初始步骤：选中目标节点自身（这里以Iron为例）
    SELECT ObjectType, ObjectSubType, 0 AS depth
    FROM ObjectTypeHierarchyTable
    WHERE ObjectType = 'Iron' AND ObjectType = ObjectSubType
    UNION ALL
    -- 递归步骤：查找当前节点的父节点，排除自引用行避免循环
    SELECT c.ObjectType, c.ObjectSubType, ac.depth + 1
    FROM ancestorCategories AS ac
    JOIN ObjectTypeHierarchyTable AS c 
        ON ac.ObjectType = c.ObjectSubType
        AND c.ObjectType != c.ObjectSubType
)
-- 提取并按层级排序祖先节点
SELECT ObjectType AS ancestor 
FROM ancestorCategories
ORDER BY depth;

代码说明 #

1. 初始查询：精准定位目标节点的自引用行（比如Iron→Iron），确保从目标节点自身开始遍历。
2. 递归逻辑：
   • 通过ac.ObjectType = c.ObjectSubType关联当前节点与它的父节点（比如Iron的父节点是Metal，对应表中Metal→Iron的行）。
   • 添加c.ObjectType != c.ObjectSubType过滤掉自引用行，彻底避免无限循环。
   • depth字段递增，用于标记节点在层级中的位置，最终排序后得到从子到根的顺序。
3. 结果输出：提取ObjectType作为祖先节点，按depth排序后得到Iron, Metal, Solid, Everything，完全符合你的期望。

通用化调整 #

如果要查询任意ObjectSubType的祖先，只需修改初始查询中的WHERE条件，比如查询Metal的祖先：

-- 初始步骤改为
WHERE ObjectType = 'Metal' AND ObjectType = ObjectSubType

内容的提问来源于stack exchange，提问作者Gauthum.J