How to Build a Recursive Query in MySQL: Methods for Versions 5.x and 8 ?

A guide to building recursive queries in MySQL

MySQL provides multiple methods to create recursive queries. Here are a few ways:

MySQL 8.0 and above

For MySQL 8.0 and higher, you can use the WITH RECURSIVE syntax:

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

Method 1: Variable assignment

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

Method 2: Path style identifier

If your hierarchy is represented as a path in the id column, you can use the following query:

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

Method 3: Self-connection

Recursion can be implemented using self-joining:

<code class="language-sql">SELECT *
FROM products AS t1
JOIN products AS t2 ON t1.parent_id = t2.id
WHERE t1.parent_id = 19;</code>

Choose the method that best suits your needs and MySQL version.

The above is the detailed content of How to Build a Recursive Query in MySQL: Methods for Versions 5.x and 8 ?. For more information, please follow other related articles on the PHP Chinese website!