如何在 MySQL 中使用遞迴查詢有效率地導覽分層資料？

MySQL分層數據遍歷與遞歸查詢

在MySQL數據庫中處理層級結構數據時，高效地遍歷和檢索不同層級的數據可能是一項挑戰。本文將介紹在MySQL中創建遞歸查詢的全面方法，為現代版本和遺留版本提供解決方案。

MySQL 8.0 的遞歸查詢

MySQL 8.0 引入了強大的 WITH RECURSIVE 語法，使您可以編寫簡潔高效的遞歸查詢。例如：

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

在這個查詢中，名為 cte 的公共表表達式 (CTE) 遞歸遍歷 products 表，從指定的 parent_id (此處為 19) 開始，並檢索其所有子節點。

MySQL 5.x 的遞歸查詢

在 MySQL 8.0 之前，不支持直接的遞歸查詢。但是，您可以採用兩種替代方法：

變量賦值:

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

這個複雜的查詢在查詢本身內使用變量賦值。它根據提供的 parent_id (此例中為 19) 迭代地識別子節點。但是，這種方法在大數據集上可能會出現性能問題。

路徑樣式標識符:

採用具有路徑樣式標識符的樹狀 ID 結構可以簡化遞歸查詢。例如：

CREATE TABLE products (id VARCHAR(255) PRIMARY KEY, name VARCHAR(255));
INSERT INTO products (id, name) VALUES ('19', 'category1');
INSERT INTO products (id, name) VALUES ('19/1', 'category2');
INSERT INTO products (id, name) VALUES ('19/1/1', 'category3');
INSERT INTO products (id, name) VALUES ('19/1/1/1', 'category4');

這種方法允許使用通配符高效地進行分層遍歷：

SELECT * FROM products WHERE id LIKE '19%';

總結

在 MySQL 中選擇遞歸查詢的方法取決於使用的具體版本和數據集的大小。對於現代 MySQL 版本，WITH RECURSIVE 語法提供了一種優雅高效的解決方案。對於遺留版本，可以採用上面概述的替代方法，其性能各有不同。

以上是如何在 MySQL 中使用遞迴查詢有效率地導覽分層資料？的詳細內容。更多資訊請關注PHP中文網其他相關文章！