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;

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);

DELIMITER //

CREATE FUNCTION get_most_parent (child_id INT)
RETURNS VARCHAR(255)
READS SQL DATA
BEGIN
    DECLARE current_name VARCHAR(255);
    DECLARE current_parent_id INT;
    DECLARE root_parent_name VARCHAR(255);
    DECLARE temp_id INT;

    SET temp_id = child_id;

    -- 循环直到找到根节点（parent_id = 0）
    -- 或者当前节点不存在（防止无限循环，虽然这里是向上追溯，不存在则会退出）
    REPEAT
        SELECT name, parent_id
        INTO current_name, current_parent_id
        FROM test
        WHERE id = temp_id;

        -- 如果当前节点是根节点，或者未找到该节点，则退出循环
        IF current_parent_id = 0 OR current_name IS NULL THEN
            SET root_parent_name = current_name;
            LEAVE REPEAT;
        END IF;

        -- 向上移动到父节点
        SET temp_id = current_parent_id;

    UNTIL FALSE END REPEAT; -- 循环条件由 IF/LEAVE 控制

    RETURN root_parent_name;
END //

DELIMITER ;

SELECT
    t.id,
    t.name,
    t.parent_id,
    get_most_parent(t.id) AS TopParentName
FROM
    test t
WHERE
    t.id IN (3, 6, 1); -- 查询 bill (ID:3), shai (ID:6), mike (ID:1) 的根父级

<?php

/**
 * 获取给定子节点的最顶层根父级节点
 *
 * @param int $childId 要查询的子节点ID
 * @param PDO $pdo 数据库连接对象
 * @return array|null 返回根父级节点的 id 和 name，如果未找到则返回 null
 */
function getRootParent(int $childId, PDO $pdo): ?array
{
    $currentId = $childId;
    $rootParent = null;

    // 循环直到找到一个 parent_id 为 0 的节点，或者当前 ID 不存在
    while (true) {
        $stmt = $pdo->prepare("SELECT id, name, parent_id FROM test WHERE id = :id");
        $stmt->execute([':id' => $currentId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        // 如果当前 ID 不存在于表中，或者已经到达了层级顶部（例如，传入的ID本身就是0），则退出
        if (!$row) {
            break;
        }

        // 如果当前节点的 parent_id 为 0，则它就是根父级
        if ($row['parent_id'] == 0) {
            $rootParent = ['id' => $row['id'], 'name' => $row['name']];
            break;
        }

        // 否则，将当前 ID 更新为其父节点的 ID，继续向上追溯
        $currentId = $row['parent_id'];
    }

    return $rootParent;
}

// 示例用法（假设您已建立 PDO 数据库连接）
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,
    ]);

    echo "--- PHP 根父级查询示例 ---" . PHP_EOL;

    // 查询 shai (ID: 6) 的根父级
    $rootOfShai = getRootParent(6, $pdo);
    if ($rootOfShai) {
        echo "Shai (ID: 6) 的根父级是: " . $rootOfShai['name'] . " (ID: " . $rootOfShai['id'] . ")" . PHP_EOL;
    } else {
        echo "未找到 Shai (ID: 6) 的根父级。" . PHP_EOL;
    }

    // 查询 bill (ID: 3) 的根父级
    $rootOfBill = getRootParent(3, $pdo);
    if ($rootOfBill) {
        echo "Bill (ID: 3) 的根父级是: " . $rootOfBill['name'] . " (ID: " . $rootOfBill['id'] . ")" . PHP_EOL;
    } else {
        echo "未找到 Bill (ID: 3) 的根父级。" . PHP_EOL;
    }

    // 查询 mike (ID: 1) 的根父级 (本身就是根)
    $rootOfMike = getRootParent(1, $pdo);
    if ($rootOfMike) {
        echo "Mike (ID: 1) 的根父级是: " . $rootOfMike['name'] . " (ID: " . $rootOfMike['id'] . ")" . PHP_EOL;
    } else {
        echo "未找到 Mike (ID: 1) 的根父级。" . PHP_EOL;
    }

} catch (PDOException $e) {
    echo "数据库连接失败: " . $e->getMessage() . PHP_EOL;
}

?>