如何使用遞歸查詢在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中文網其他相關文章！