如何用MySQL递归查询获取C盘目录的完整路径？

现有folder表结构及数据如下：

id  name        parent_id
1   c:          null
2   Users       1
3   programfile 1
4   profile     2
5   d:          null
6   projects    5
7   ali         6

需要查询出C盘（c:）下的所有完整目录路径，期望输出为：

c:/
c:/Users
c:/Users/profile
c:/programfile

尝试了以下递归CTE查询语句，但未得到正确结果，寻求正确写法：

WITH RECURSIVE cte AS ( 
    SELECT
        id,
        id nextid,
        parent_id
    FROM
        folder AS t1

    UNION ALL

    SELECT
        cte.id,
        t2.id, 
        t2.parent_id
    FROM
        folder AS t2
        JOIN cte ON cte.parent_id = t2.id 
)
SELECT
    Id,
    nextid RootId
FROM
    cte
WHERE
    parent_id IS NULL;

正确的递归CTE需要从C盘节点出发，逐层拼接子目录路径，写法如下：

WITH RECURSIVE dir_paths AS (
    -- 锚点成员：选中C盘根节点，初始化路径
    SELECT 
        id,
        CONCAT(name, '/') AS full_path
    FROM folder
    WHERE name = 'c:'

    UNION ALL

    -- 递归成员：关联父节点，拼接完整路径
    SELECT
        f.id,
        CONCAT(dp.full_path, f.name) AS full_path
    FROM folder f
    JOIN dir_paths dp ON f.parent_id = dp.id
)
SELECT full_path FROM dir_paths ORDER BY full_path;

逻辑说明 #

1. 锚点成员先定位C盘根记录，直接生成根路径c:/
2. 递归成员通过parent_id关联父节点的路径，用CONCAT函数将父路径与当前目录名拼接成完整路径
3. 最后查询并排序结果，即可得到C盘下所有层级的完整目录路径

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