如何在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中文網其他相關文章！