How Can I Efficiently Retrieve Hierarchical Data in MySQL Using Recursive Queries?

MySQL Recursive Inquiry: High -efficiency processing hierarchical data

Background:

Many levels of data structures, such as file systems or tissue structure diagrams, require efficient methods to retrieve data based on parent -child relationships. MySQL provides multiple methods to write recursive queries that effectively traversed these structures.

mysql 8 recursive grammar solution

MySQL 8 introduces recursive with grammar, which greatly simplifies recursive query.

Just replace the value of to the parent node ID you want to retrieve your child node.

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

MySQL 5.x path ID solution parent_id = 19

Before MySQL 8, you need to use other methods for recursive query. One method is to allocate the path ID and embed the hierarchical information into the ID column.

For example, the table containing path ID may be shown below:

Now, the following inquiries will retrieve all sub -nodes of Category1:

ID	NAME
19	category1
19/1	category2
19/1/1	category3
19/1/1/1	category4

MySQL 5.X Internal Link variables and self -connection solutions

<code class="language-sql">SELECT * FROM products WHERE id LIKE '19%'</code>

MySQL 5.X Another option is to create recursive queries with internal variables, path IDs or self -connection. Here are examples of internal variables:

Set the value in as the parent node ID of the node to retrieve its child node.

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

Summary

MySQL provides a variety of methods to write recursive queries to process hierarchical data. The method to choose depends on the specific requirements of the MySQL version and query. @pv := '19'

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