如何在 MySQL 中创建分层递归查询？

在MySQL中创建分层递归查询

理解层次结构

在MySQL中创建分层查询，需要理解层次结构是如何构建的。考虑以下示例表：

id	名称	父ID
19	类别1	0
20	类别2	19
21	类别3	20
22	类别4	21

在此表中，parent_id列表示给定类别的父类别的ID。例如，类别2的parent_id为19，表示它是类别1的子类别。

MySQL 8 解决方案：递归WITH

对于MySQL 8及以上版本，可以使用递归WITH语法：

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
  INNER JOIN cte ON p.parent_id = cte.id
)
SELECT * FROM cte;

此查询将递归检索给定父ID（此示例中为19）的所有子类别。输出将包括直接或间接属于类别1（id=19）的所有类别。

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

对于较旧的MySQL版本（5.x），有两种替代方法：

内联变量：

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

此查询利用MySQL的特定功能，在其执行过程中分配和修改变量。通过用父ID（在本例中为19）初始化@pv变量，并使用FIND_IN_SET函数检查parent_id是否出现在后代列表中，我们可以逐步构建后代ID列表。

自连接：

SELECT DISTINCT child.id, child.name, child.parent_id
FROM products AS child
JOIN products AS parent ON child.parent_id = parent.id
WHERE parent.id = 19;

此查询使用自连接从指定的父ID（19）开始遍历层次结构。通过根据parent_id列将子表与父表连接，我们可以递归地提取给定父级的所有后代。

以上是如何在 MySQL 中创建分层递归查询？的详细内容。更多信息请关注PHP中文网其他相关文章！