如何在 MySQL 中递归查询树结构以查找父节点的后代？

在 MySQL 中递归树结构

在关系数据库中管理分层数据可能是一个挑战，特别是在需要递归查询时。在 MySQL 中，我们有一个特定的问题：如何有效地检索给定父位置的所有后代，无论层次结构的深度如何。

问题：

考虑以下位置数据库架构：

location (id, ....)
location_parent (location_id, parent_id)

位置表存储位置信息，而 location_parent 表定义位置之间的父子关系。

假设我们有一个带有 ID 的位置5. 我们想要检索其所有后代，包括那些多层深度的后代。

初始解决方案：

递归查询的一种方法是使用自递归查询连接如下：

<code class="sql">SELECT DISTINCT l.id
FROM location AS l
LEFT JOIN location_parent AS lp ON l.id = lp.child_id
WHERE lp.parent_id = 5
;</code>

此查询将检索位置 5 的直接子级。但是，对于更深的后代，我们需要多次重复查询，每次都使用上一个查询的结果作为输入下一个。对于大型层次结构，此方法效率低下且不切实际。

推荐解决方案：

此问题的推荐解决方案是使用递归公用表表达式 (CTE)。 CTE 是一个临时表，可以在单个查询中定义和引用。

<code class="sql">WITH RECURSIVE descendants AS (
    SELECT id, parent_id
    FROM location_parent
    WHERE parent_id = 5
    UNION ALL
    SELECT lp.child_id, lp.parent_id
    FROM descendants AS d
    JOIN location_parent AS lp ON d.id = lp.parent_id
)
SELECT id
FROM descendants
;</code>

此查询创建一个称为后代的递归 CTE，它从位置 5 的直接子级开始。然后是 UNION ALL 子句添加先前选择的后代的子代，有效地扩展查询以包含任何深度的所有后代。

查询的第二部分从后代 CTE 中选择 id 列，这为我们提供了所有后代的列表地点。这种方法比自连接方法高效得多，并且可以处理任何深度的层次结构。

以上是如何在 MySQL 中递归查询树结构以查找父节点的后代？的详细内容。更多信息请关注PHP中文网其他相关文章！