如何在MySQL或PHP中获取任意子节点的顶级父节点

本文详细介绍了如何在具有层级关系的数据库表中，通过给定任意子节点的ID来查找其最顶层的父节点。我们将探讨两种主要实现方法：使用MySQL存储函数进行迭代查询，以及通过PHP编写循环逻辑进行数据追溯。文章将提供具体的代码示例、实现步骤，并讨论两种方法的适用场景及性能考量。

理解层级数据结构与挑战

在许多应用场景中，数据往往呈现出层级结构，例如组织架构中的员工与经理、商品分类中的主分类与子分类、或评论系统中的回复关系。一个常见的数据库设计模式是使用“邻接列表模型”（Adjacency List Model），其中每条记录包含一个指向其直接父级的parent_id字段。当parent_id为0或NULL时，通常表示该节点是顶层父节点。

考虑以下名为test的表结构：

我们的目标是，给定一个子节点的id（例如shai的id为6），能够追溯到其最顶层的父节点（即mike，id为1）。

立即学习“PHP免费学习笔记（深入）”；

仅查询直接父级的局限性

一个常见的误区是使用简单的JOIN操作来查找父级。例如，以下SQL查询可以找到id为6的节点的直接父级：

SELECT 
    child.id, 
    child.name, 
    child.parent_id, 
    parent.name AS ParentName 
FROM 
    test child 
JOIN 
    test parent ON child.parent_id = parent.id 
WHERE 
    child.id = 6;

登录后复制

这条查询的结果会是shai的直接父级sam（id: 5）。然而，它无法继续向上追溯到sam的父级sara，乃至最终的顶级父级mike。为了实现这一目标，我们需要一种迭代或递归的机制。

解决方案一：使用MySQL存储函数进行迭代追溯

MySQL从8.0版本开始支持递归CTE（Common Table Expressions），但对于早期版本或需要将逻辑封装在数据库层面的情况，存储函数是一个有效的选择。我们可以创建一个存储函数，通过循环查询来追溯直到找到parent_id为0的节点。

创建get_most_parent存储函数

DELIMITER //

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

    SET current_id = input_id;

    -- 循环追溯父节点，直到parent_id为0
    REPEAT
        SELECT name, parent_id
        INTO parent_name, current_parent_id
        FROM test
        WHERE id = current_id;

        -- 如果当前节点是顶级父节点（parent_id为0），则跳出循环
        IF current_parent_id = 0 THEN
            LEAVE REPEAT;
        END IF;

        -- 否则，将父节点的ID设为当前ID，继续向上追溯
        SET current_id = current_parent_id;
    UNTIL FALSE END REPEAT; -- 循环条件设置为FALSE，表示无限循环，直到LEAVE REPEAT跳出

    RETURN parent_name;
END //

DELIMITER ;

登录后复制

函数解析：

标签： mysql php word node 编程语言 ai sql语句 性能瓶颈 php编写