SQL存储过程如何实现父子结构路径展开？利用递归CTE拼接路径的方法是什么

在处理具有父子层级关系的数据时，比如公司部门架构、商品类目树等场景，我们常常需要将每个节点的完整层级路径拼接出来，方便后续做层级查询、路径展示等操作。利用SQL的递归CTE结合存储过程，可以高效实现这类需求，不需要在应用层做复杂的递归处理。

父子结构表设计示例

首先我们需要有一张存储父子关系的基础表，通常这类表会包含节点ID、父节点ID、节点名称三个核心字段，以下是一个典型的分类表结构：

-- 创建分类表
CREATE TABLE category (
    id INT PRIMARY KEY,
    parent_id INT,
    category_name VARCHAR(50),
    FOREIGN KEY (parent_id) REFERENCES category(id)
);

-- 插入测试数据
INSERT INTO category (id, parent_id, category_name) VALUES
(1, NULL, '数码产品'),
(2, 1, '手机'),
(3, 2, '智能手机'),
(4, 2, '功能手机'),
(5, 1, '电脑'),
(6, 5, '笔记本电脑'),
(7, 5, '台式电脑');

递归CTE的基本原理

递归CTE（Common Table Expression，公用表表达式）是SQL标准中支持的递归查询语法，它分为两个部分：锚定部分和递归部分。锚定部分用于获取递归的初始数据集，通常是层级结构的最顶层节点；递归部分则基于上一次查询的结果，不断关联子节点，直到没有更多子节点为止。

递归CTE的基本语法结构如下：

WITH RECURSIVE cte_name AS (
    -- 锚定部分：查询初始节点
    SELECT 初始字段列表 FROM 表名 WHERE 初始条件
    UNION ALL
    -- 递归部分：关联子节点
    SELECT 关联后的字段列表 FROM 表名 JOIN cte_name ON 关联条件
)
SELECT * FROM cte_name;

存储过程中实现路径拼接

我们可以在存储过程中封装递归CTE逻辑，实现自动拼接每个节点的完整层级路径。以下是MySQL环境下的存储过程实现示例：

DELIMITER //
CREATE PROCEDURE get_category_path()
BEGIN
    -- 定义递归CTE拼接路径
    WITH RECURSIVE category_path AS (
        -- 锚定部分：处理顶层节点，路径为自身名称
        SELECT 
            id,
            parent_id,
            category_name,
            CAST(category_name AS CHAR(1000)) AS full_path
        FROM category
        WHERE parent_id IS NULL
        UNION ALL
        -- 递归部分：拼接父节点路径和当前节点名称
        SELECT 
            c.id,
            c.parent_id,
            c.category_name,
            CONCAT(cp.full_path, '/', c.category_name) AS full_path
        FROM category c
        INNER JOIN category_path cp ON c.parent_id = cp.id
    )
    -- 输出最终结果
    SELECT id, category_name, full_path FROM category_path ORDER BY id;
END //
DELIMITER ;

调用这个存储过程就可以得到每个分类的完整路径：

-- 调用存储过程
CALL get_category_path();

执行后输出的结果如下：

注意事项

• 不同数据库对递归CTE的支持有差异，MySQL需要8.0及以上版本，SQL Server、PostgreSQL也支持类似语法，但细节略有不同。
• 递归CTE默认有递归深度限制，如果层级过深可能需要调整数据库的递归深度配置参数。
• 路径拼接时需要注意字段长度，避免拼接后的路径超出字段定义的长度导致截断。
• 如果表中存在循环引用（比如子节点关联回祖先节点），递归会陷入死循环，需要在逻辑中增加防循环的判断。

扩展：带参数的存储过程

如果需要查询指定节点及其所有子节点的路径，可以给存储过程增加输入参数，示例如下：

DELIMITER //
CREATE PROCEDURE get_sub_category_path(IN root_id INT)
BEGIN
    WITH RECURSIVE category_path AS (
        -- 锚定部分：查询指定根节点
        SELECT 
            id,
            parent_id,
            category_name,
            CAST(category_name AS CHAR(1000)) AS full_path
        FROM category
        WHERE id = root_id
        UNION ALL
        -- 递归部分：查询子节点
        SELECT 
            c.id,
            c.parent_id,
            c.category_name,
            CONCAT(cp.full_path, '/', c.category_name) AS full_path
        FROM category c
        INNER JOIN category_path cp ON c.parent_id = cp.id
    )
    SELECT id, category_name, full_path FROM category_path ORDER BY id;
END //
DELIMITER ;

调用时传入根节点ID即可，比如查询手机分类下的所有子分类路径：

CALL get_sub_category_path(2);

SQL存储过程递归CTE父子结构路径路径拼接修改时间：2026-07-08 03:09:27