本教程详细阐述了如何在具有多级父子关系的数据库表中，通过给定子节点id，高效地查找其最顶层（根）父节点的id和名称。文章将介绍使用mysql存储函数进行迭代查询的方法，并探讨其性能考量及数据完整性注意事项，同时简述了在应用层（如php）实现相同逻辑的思路，旨在帮助开发者处理复杂层级数据。
在许多业务场景中，我们经常会遇到具有层级关系的数据，例如组织架构、商品分类或用户推荐链。这类数据通常通过在表中设置一个 parent_id 字段来表示父子关系。当需要从一个任意子节点追溯到其最顶层的祖先节点（即 parent_id 为0或NULL的节点）时，简单的单次 JOIN 查询往往无法满足需求。本教程将深入探讨如何解决这一问题。
假设我们有一个名为 test 的表，其结构如下：
| id | name | parent_id |
|---|---|---|
| 1 | mike | 0 |
| 2 | jeff | 0 |
| 3 | bill | 2 |
| 4 | sara | 1 |
| 5 | sam | 4 |
| 6 | shai | 5 |
在这个表中，parent_id 为 0 表示该节点没有父级，即为根节点。例如，shai (ID: 6) 的父级是 sam (ID: 5)，sam 的父级是 sara (ID: 4)，sara 的父级是 mike (ID: 1)，而 mike 的 parent_id 是 0，因此 mike 是 shai 的最顶层父节点。
如果使用简单的 JOIN 查询，例如：
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)，而无法继续向上追溯到 mike。这是因为 JOIN 操作只在两个表之间进行一次匹配，无法实现多级递归。
对于不支持递归CTE（Common Table Expressions）的MySQL版本（如5.7及更早版本），或者在需要封装复杂逻辑时，创建一个存储函数是有效的方法。该函数将通过迭代查询的方式，从给定的子节点开始，逐级向上查找其父节点，直到找到 parent_id 为 0 的根节点。
首先，确保您的数据库中存在上述的 test 表和数据：
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);以下是用于查找最顶层父节点的MySQL存储函数：
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 next_parent_id INT;
SET current_id = input_id;
-- 循环向上查找，直到parent_id为0
REPEAT
SELECT name, parent_id
INTO parent_name, next_parent_id
FROM test
WHERE id = current_id;
-- 如果当前节点就是根节点，或者没有父节点了，则停止
IF next_parent_id = 0 OR next_parent_id IS NULL THEN
RETURN parent_name;
END IF;
-- 更新当前ID为父ID，继续向上查找
SET current_id = next_parent_id;
UNTIL FALSE END REPEAT; -- 理论上会通过IF条件提前返回
-- 如果输入ID不存在或发生其他意外，返回NULL
RETURN NULL;
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);查询结果示例：
| id | name | parent_id | TopParentName |
|---|---|---|---|
| 3 | bill | 2 | jeff |
| 6 | shai | 5 | mike |
从结果可以看出，对于ID为3的bill，其最顶层父节点是jeff；对于ID为6的shai，其最顶层父节点是mike，这正是我们期望的结果。
如果数据库版本不支持存储函数，或者出于性能和业务逻辑分离的考虑，您也可以在应用层（如PHP）实现类似的迭代查找逻辑。
核心思路是：从给定的子节点ID开始，通过循环不断查询其父节点的ID，直到 parent_id 为 0。
connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli->connect_error;
exit();
}
/**
* 查找给定ID的最顶层父节点
*
* @param mysqli $mysqli 数据库连接对象
* @param int $childId 要查找的子节点ID
* @return array|null 包含 'id' 和 'name' 的父节点信息，如果未找到则返回 null
*/
function findTopParent(mysqli $mysqli, int $childId): ?array
{
$currentId = $childId;
$topParent = null;
while (true) {
$stmt = $mysqli->prepare("SELECT id, name, parent_id FROM test WHERE id = ?");
$stmt->bind_param("i", $currentId);
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
// 如果当前节点就是根节点
if ($row['parent_id'] == 0) {
$topParent = ['id' => $row['id'], 'name' => $row['name']];
break; // 找到根节点，退出循环
} else {
// 继续向上追溯
$currentId = $row['parent_id'];
}
} else {
// 如果当前ID在数据库中不存在，或者追溯到某个不存在的父节点
// 这通常意味着原始childId无效或数据存在问题
$topParent = null;
break;
}
}
$stmt->close();
return $topParent;
}
// 示例使用
$childIdToFind = 6; // shai
$topParentInfo = findTopParent($mysqli, $childIdToFind);
if ($topParentInfo) {
echo "子节点ID " . $childIdToFind . " 的最顶层父节点是: " . $topParentInfo['name'] . " (ID: " . $topParentInfo['id'] . ")\n";
} else {
echo "未找到子节点ID " . $childIdToFind . " 的最顶层父节点。\n";
}
$childIdToFind = 3; // bill
$topParentInfo = findTopParent($mysqli, $childIdToFind);
if ($topParentInfo) {
echo "子节点ID " . $childIdToFind . " 的最顶层父节点是: " . $topParentInfo['name'] . " (ID: " . $topParentInfo['id'] . ")\n";
} else {
echo "未找到子节点ID " . $childIdToFind . " 的最顶层父节点。\n";
}
$mysqli->close();
?>PHP代码解析：
在处理多级父子关系并查找根节点的问题时，选择哪种方案取决于您的具体需求、数据库版本和性能考量：
本，且查询量不大、层级不深的情况。它将逻辑封装在数据库层，简化了应用代码。但要注意性能瓶颈和循环引用问题。无论采用哪种方法，都应严格保证数据的一致性和完整性，避免出现循环引用，这可能导致无限循环或错误结果。在设计表结构时，可以考虑添加触发器或应用程序级别的校验来维护数据的层级关系。