MySQL层级数据处理：从子节点追溯到根父节点的高效策略-php教程-PHP中文网

MySQL层级数据处理：从子节点追溯到根父节点的高效策略

本文旨在探讨如何在mysql中高效地从任意子节点追溯到其最顶层的根父节点。我们将介绍两种主要的sql实现方法：利用mysql用户定义函数（udf）进行迭代查询，以及使用mysql 8.0及以上版本支持的递归公共表表达式（cte）。同时，文章也将提供数据表初始化示例、代码演示、性能考量及php实现思路，帮助读者深入理解并应用于实际开发。

在数据库管理中，处理具有层级关系的数据是一个常见需求，例如组织架构、评论回复链或产品分类。一个典型的场景是，给定一个子节点的ID，我们需要找出其在整个层级结构中最顶层的父节点（通常定义为parent_id为0的节点）。直接使用简单的JOIN操作只能获取到当前节点的直接父节点，无法实现多级追溯。

1. 数据模型与问题描述

我们以一个名为test的表为例，该表包含id、name和parent_id三个字段，其中parent_id指向其父节点的id，parent_id为0表示该节点是根节点。

表结构及示例数据：

CREATE TABLE test (
    id INT,
    name VARCHAR(255),
    parent_id INT
);

INSERT INTO test VALUES
(1, 'mike', 0),
(2, 'jeff', 0),
(3, 'bill', 2),
(4, 'sara', 1),
(5, 'sam',  4),
(6, 'shai', 5);

SELECT * FROM test;

登录后复制

id	name	parent_id
1	mike	0
2	jeff	0
3	bill	2
4	sara	1
5	sam	4
6	shai	5

问题： 如果我们查询id为6的节点（shai），期望得到其最顶层的父节点mike（id:1），而不是其直接父节点sam（id:5）。

2. 解决方案一：使用MySQL用户定义函数（UDF）

对于不支持递归CTE的MySQL版本（如MySQL 5.7），或者在需要封装复杂逻辑时，创建用户定义函数是一个有效的选择。该函数通过迭代查询，逐级向上追溯直到找到parent_id为0的根节点。

创建 get_most_parent 函数：

DELIMITER //

CREATE FUNCTION get_most_parent (initial_id INT)
RETURNS VARCHAR(255)
READS SQL DATA
BEGIN
    DECLARE current_id INT;
    DECLARE parent_name VARCHAR(255);
    DECLARE next_parent_id INT;

    SET current_id = initial_id;

    -- 循环向上追溯，直到找到根节点 (parent_id 为 0)
    REPEAT
        SELECT name, parent_id
        INTO parent_name, next_parent_id
        FROM test
        WHERE id = current_id;

        -- 如果当前节点的 parent_id 为 0，则它就是根节点，跳出循环
        IF next_parent_id = 0 THEN
            LEAVE REPEAT;
        END IF;

        -- 否则，将 current_id 更新为它的父节点ID，继续下一轮循环
        SET current_id = next_parent_id;

    UNTIL FALSE END REPEAT; -- 循环直到显式 LEAVE

    RETURN parent_name;
END //

DELIMITER ;

登录后复制

函数说明：

initial_id：要查询的子节点的起始ID。
DECLARE：声明局部变量用于存储当前节点ID、父节点名称和下一个父节点ID。
REPEAT...UNTIL：这是一个循环结构，它会执行循环体内的语句，直到UNTIL条件为真。在这里，我们使用LEAVE REPEAT在找到根节点时提前退出。
SELECT name, parent_id INTO ...：查询当前current_id对应的name和parent_id，并赋值给局部变量。
IF next_parent_id = 0 THEN LEAVE REPEAT; END IF;：判断是否已到达根节点。
SET current_id = next_parent_id;：更新current_id为当前节点的父节点ID，以便在下一次循环中查询其父节点。

使用函数查询根父节点：

SELECT
    t.id,
    t.name,
    t.parent_id,
    get_most_parent(t.id) AS TopParentName
FROM test t
WHERE t.id IN (3, 6);

登录后复制

查询结果：

id	name	parent_id	TopParentName
3	bill	2	jeff
6	shai	5	mike

注意事项：

性能考量： 这种基于UDF的迭代方法对于每一行输出都会独立执行整个追溯过程。如果需要查询大量行，或者层级深度很深，可能会导致显著的性能开销。
数据完整性： 确保数据中没有循环引用（即A的父节点是B，B的父节点是C，C的父节点又是A），否则函数可能陷入无限循环。
适用场景： 适用于查询少量特定节点的根父节点，或者在MySQL 8.0以下版本中。

3. 解决方案二：使用递归公共表表达式（CTE）

MySQL 8.0及以上版本支持递归CTE，这是处理层级数据更现代、更高效且SQL标准化的方法。递归CTE由一个“锚定成员”和一个或多个“递归成员”组成。

Glarity

Glarity是一款免费开源的AI浏览器扩展，提供YouTube视频总结、网页摘要、写作工具等功能，支持免费的镜像翻译，电子邮件写作辅助，AI问答等功能。

131

查看详情

使用递归CTE查询根父节点：

WITH RECURSIVE AncestorPath AS (
    -- 锚定成员: 从查询的子节点开始
    SELECT
        id,
        name,
        parent_id,
        id AS original_child_id, -- 记录最初查询的子节点ID
        name AS original_child_name
    FROM test
    WHERE id IN (3, 6) -- 示例：查询ID为3和6的节点的根父节点

    UNION ALL

    -- 递归成员: 向上追溯父节点
    SELECT
        t.id,
        t.name,
        t.parent_id,
        ap.original_child_id,
        ap.original_child_name
    FROM test t
    JOIN AncestorPath ap ON t.id = ap.parent_id
    WHERE t.parent_id != 0 -- 停止条件：当找到根节点 (parent_id = 0) 时
)
SELECT
    ap.original_child_id AS child_id,
    ap.original_child_name AS child_name,
    t.id AS root_parent_id,
    t.name AS root_parent_name
FROM AncestorPath ap
JOIN test t ON t.id = ap.id
WHERE t.parent_id = 0;

登录后复制

CTE说明：

WITH RECURSIVE AncestorPath AS (...)：定义一个名为AncestorPath的递归CTE。
锚定成员： SELECT id, name, parent_id, id AS original_child_id, name AS original_child_name FROM test WHERE id IN (3, 6)。这部分定义了递归的起始点，即我们想要查询的子节点。original_child_id和original_child_name用于在递归过程中追踪最初的子节点。
UNION ALL： 连接锚定成员和递归成员的结果集。
递归成员： SELECT t.id, t.name, t.parent_id, ap.original_child_id, ap.original_child_name FROM test t JOIN AncestorPath ap ON t.id = ap.parent_id WHERE t.parent_id != 0。这部分通过JOIN自身（AncestorPath）来向上追溯父节点，直到parent_id为0（根节点）时停止。
最终查询： SELECT ... FROM AncestorPath ap JOIN test t ON t.id = ap.id WHERE t.parent_id = 0。从CTE的结果中筛选出那些parent_id为0的行，这些行就是对应original_child_id的根父节点。

查询结果：

child_id	child_name	root_parent_id	root_parent_name
3	bill	2	jeff
6	shai	1	mike

优势：

性能更优： 递归CTE通常比UDF在处理大量层级数据时表现出更好的性能，因为它能够更好地利用数据库的查询优化器。
代码可读性： 结构清晰，更符合SQL的声明式编程风格。
标准化： 遵循SQL标准，跨数据库兼容性更好（虽然语法可能略有差异）。

4. PHP实现思路

如果不想在数据库层面创建函数或使用CTE（例如，为了保持数据库的纯净性或兼容旧版MySQL），可以在PHP应用层实现相同的逻辑。

基本思路：

编写一个PHP函数，接受子节点ID作为参数。
在函数内部，使用循环结构。
在每次循环中，查询当前节点的直接父节点ID和名称。
如果查询到的parent_id为0，则当前节点就是根节点，返回其名称并退出循环。
否则，将当前节点ID更新为其父节点ID，继续下一轮循环。

PHP伪代码示例：

<?php

function getRootParent(PDO $pdo, int $childId): ?array
{
    $currentId = $childId;
    $rootParent = null;

    while (true) {
        $stmt = $pdo->prepare("SELECT id, name, parent_id FROM test WHERE id = :id");
        $stmt->execute([':id' => $currentId]);
        $node = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$node) {
            // 节点不存在，或者数据异常
            return null;
        }

        if ($node['parent_id'] == 0) {
            // 找到根节点
            $rootParent = ['id' => $node['id'], 'name' => $node['name']];
            break;
        }

        // 继续向上追溯
        $currentId = $node['parent_id'];
    }

    return $rootParent;
}

// 示例用法
// $pdo = new PDO("mysql:host=localhost;dbname=your_db", "user", "password");
// $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// $childId = 6;
// $root = getRootParent($pdo, $childId);

// if ($root) {
//     echo "子节点ID " . $childId . " 的根父节点是: " . $root['name'] . " (ID: " . $root['id'] . ")\n";
// } else {
//     echo "未找到根父节点。\n";
// }

?>

登录后复制

PHP实现注意事项：

数据库连接： 确保PHP脚本能够正确连接到MySQL数据库。
性能： 这种方法每次循环都会执行一次数据库查询。如果层级很深或需要查询大量子节点，可能会导致大量的数据库往返（N+1查询问题），从而影响性能。可以考虑一次性加载所有层级数据到内存中进行处理，但这会增加内存消耗。
错误处理： 需要妥善处理节点不存在或数据异常的情况。