How to Efficiently Navigate Hierarchical Data with Recursive Queries in MySQL?

MySQL hierarchical data traversal and recursive query

When dealing with hierarchically structured data in a MySQL database, efficiently traversing and retrieving different levels of data can be a challenge. This article will introduce a comprehensive approach to creating recursive queries in MySQL, providing solutions for both modern and legacy versions.

Recursive query in MySQL 8.0

MySQL 8.0 introduces the powerful WITH RECURSIVE syntax, allowing you to write concise and efficient recursive queries. For example:

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;

In this query, a common table expression (CTE) named cte recursively traverses the products table, starting at the specified parent_id (here 19), and retrieves all of its child nodes.

Recursive query for MySQL 5.x

Before MySQL 8.0, direct recursive queries were not supported. However, there are two alternatives you can take:

Variable assignment:

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

This complex query uses variable assignment within the query itself. It iteratively identifies child nodes based on the provided parent_id (19 in this case). However, this approach may suffer from performance issues on large data sets.

Path style identifier:

Using a tree-like ID structure with path-style identifiers simplifies recursive queries. For example:

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

This approach allows efficient hierarchical traversal using wildcards:

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

Summary

The method you choose for recursive queries in MySQL depends on the specific version used and the size of your data set. For modern MySQL versions, the WITH RECURSIVE syntax provides an elegant and efficient solution. For legacy versions, the alternatives outlined above are available, with varying performance.

The above is the detailed content of How to Efficiently Navigate Hierarchical Data with Recursive Queries in MySQL?. For more information, please follow other related articles on the PHP Chinese website!