How to Create Hierarchical Recursive Queries in MySQL?

Creating hierarchical recursive queries in MySQL

Understanding Hierarchy

To create hierarchical queries in MySQL, you need to understand how hierarchies are built. Consider the following example table:

id	名称	父ID
19	类别1	0
20	类别2	19
21	类别3	20
22	类别4	21

In this table, the parent_id column represents the ID of the parent category of a given category. For example, Category 2 has a parent_id of 19, indicating that it is a subcategory of Category 1.

MySQL 8 solution: recursion WITH

For MySQL 8 and above, you can use the recursive WITH syntax:

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

This query will recursively retrieve all child categories for a given parent ID (19 in this example). The output will include all categories that directly or indirectly belong to category 1 (id=19).

MySQL 5.x solution: inline variables or self-join

For older MySQL versions (5.x), there are two alternatives:

Inline variables:

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 query takes advantage of MySQL's specific capabilities to allocate and modify variables during its execution. By initializing the @pv variable with the parent ID (19 in this case) and using the FIND_IN_SET function to check if parent_id appears in the descendant list, we can build the list of descendant IDs incrementally.

Self-connection:

SELECT DISTINCT child.id, child.name, child.parent_id
FROM products AS child
JOIN products AS parent ON child.parent_id = parent.id
WHERE parent.id = 19;

This query traverses the hierarchy starting at the specified parent ID (19) using a self-join. By joining the child table with the parent table based on the parent_id column, we can recursively extract all descendants of a given parent.

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