如何使用递归查询在MySQL中有效检索层次数据？

MySQL递归查询：高效处理层级数据

背景:

许多层级数据结构，例如文件系统或组织结构图，都需要高效的方法来根据父子关系检索数据。MySQL 提供多种方法来编写有效遍历这些结构的递归查询。

MySQL 8 递归语法解决方案

MySQL 8 引入了递归 WITH 语法，这大大简化了递归查询。

<code class="language-sql">WITH RECURSIVE cte (id, name, parent_id) AS (
  SELECT id, name, parent_id
  FROM products
  WHERE parent_id = 19
  UNION ALL
  SELECT p.id, p.name, p.parent_id
  FROM products p
  JOIN cte ON p.parent_id = cte.id
)
SELECT * FROM cte;</code>

只需将 parent_id = 19 中的值替换为您要检索其子节点的父节点 ID。

MySQL 5.x 路径 ID 解决方案

在 MySQL 8 之前，需要使用其他方法进行递归查询。一种方法是分配路径 ID，将层级信息嵌入 ID 列中。

例如，包含路径 ID 的表可能如下所示：

ID	NAME
19	category1
19/1	category2
19/1/1	category3
19/1/1/1	category4

现在，以下查询将检索 category1 的所有子节点：

<code class="language-sql">SELECT * FROM products WHERE id LIKE '19%'</code>

MySQL 5.x 内联变量和自连接解决方案

MySQL 5.x 的另一个选项是使用内联变量、路径 ID 或自连接来创建递归查询。以下是用内联变量的示例：

<code class="language-sql">SELECT id, name, parent_id
FROM (SELECT * FROM products
ORDER BY parent_id, id) products_sorted,
(SELECT @pv := '19') initialisation
WHERE FIND_IN_SET(parent_id, @pv)
AND LENGTH(@pv := CONCAT(@pv, ',', id))</code>

将 @pv := '19' 中的值设置为要检索其子节点的父节点 ID。

总结

MySQL 提供了多种方法来编写处理层级数据的递归查询。选择哪种方法取决于使用的 MySQL 版本和查询的具体要求。

以上是如何使用递归查询在MySQL中有效检索层次数据？的详细内容。更多信息请关注PHP中文网其他相关文章！