MySql递归查询子节点

MySql递归查询子节点

方式1：使用临时变量和子查询

循环查询id为2的节点下所有子id及id子节点信息

SELECT
            *
        FROM
            (
            SELECT
                @ids AS parentId,
                ( SELECT @ids := GROUP_CONCAT( id ) FROM performance_department WHERE FIND_IN_SET( parent_department_id, @ids ) ) AS childId,
                @l := @l + 1 AS LEVEL
            FROM
                performance_department,
                ( SELECT @ids := 2 ) b
            WHERE
                @ids IS NOT NULL
            ) id,
            performance_department DATA
        WHERE
            FIND_IN_SET( DATA.id, ID.parentId )
        ORDER BY
            LEVEL,
            id

方式2 使用 WITH RECURSIVE（Common Table Expressions，通用表表达式）

WITH RECURSIVE cte AS (
     SELECT id, parent_id, name
     FROM your_table
     WHERE parent_id IS NULL -- 选择根节点
     UNION ALL
     SELECT t.id, t.parent_id, t.name
     FROM your_table t
     JOIN cte ON t.parent_id = cte.id -- 递归连接条件
 )
 SELECT * FROM cte;