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

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;

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 ;

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

<?php

class HierarchyManager {
    private $pdo;

    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }

    /**
     * 根据子节点ID查找其最顶层的父节点信息
     * @param int $childId 子节点ID
     * @return array|null 顶级父节点的ID和名称，如果找不到则返回null
     */
    public function findTopParent(int $childId): ?array {
        $currentId = $childId;
        $topParent = null;

        // 准备查询语句
        $stmt = $this->pdo->prepare("SELECT id, name, parent_id FROM test WHERE id = :id");

        // 循环追溯父节点
        while (true) {
            $stmt->execute([':id' => $currentId]);
            $node = $stmt->fetch(PDO::FETCH_ASSOC);

            // 如果找不到节点，或者已经追溯到最顶层（parent_id为0）
            if (!$node || $node['parent_id'] == 0) {
                // 如果当前节点存在，它就是顶级父节点
                if ($node) {
                    $topParent = ['id' => $node['id'], 'name' => $node['name']];
                }
                break; // 跳出循环
            }

            // 更新当前ID为父ID，继续向上追溯
            $currentId = $node['parent_id'];
        }

        return $topParent;
    }

    /**
     * 获取指定ID节点的所有信息
     * @param int $id
     * @return array|null
     */
    private function getNodeById(int $id): ?array {
        $stmt = $this->pdo->prepare("SELECT id, name, parent_id FROM test WHERE id = :id");
        $stmt->execute([':id' => $id]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }
}

// 示例用法
try {
    // 数据库连接配置
    $dsn = 'mysql:host=localhost;dbname=your_database_name;charset=utf8mb4';
    $username = 'your_username';
    $password = 'your_password';

    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);

    $manager = new HierarchyManager($pdo);

    $childId = 6; // shai 的 ID
    $topParent = $manager->findTopParent($childId);

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

    $childId2 = 3; // bill 的 ID
    $topParent2 = $manager->findTopParent($childId2);
    if ($topParent2) {
        echo "节点ID " . $childId2 . " 的顶级父节点是: " . $topParent2['name'] . " (ID: " . $topParent2['id'] . ")\n";
    }

} catch (PDOException $e) {
    echo "数据库连接失败或查询错误: " . $e->getMessage();
}

?>