How to Retrieve All Child IDs in a MySQL Hierarchical Structure Using a Single Query?

MYSQL hierarchical recursive query method creation method

Question

You have a layered MySQL table, each of which has an ID, a name, and a parent ID. You want to use a single MySQL query to retrieve all sub -IDs who give the parent ID.

Answer

For MySQL 8:

Use with recursive syntax:

For mysql 5.x:

<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
  INNER JOIN cte
          ON p.parent_id = cte.id
)
SELECT * FROM cte;</code>

Using internal variables, path IDs, or self -connection:

Neilian variables:

Path style identifier:

The ID value of allocating hierarchical information (path):

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

Then use this query:

The above is the detailed content of How to Retrieve All Child IDs in a MySQL Hierarchical Structure Using a Single Query?. For more information, please follow other related articles on the PHP Chinese website!