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