How to create a MySQL hierarchical recursive query?

Question

我有一个 MySQL 表，如下所示：

id	名称	parent_id
19	类别1	0
20	类别2	19
21	类别3	20
22	类别4	21
...	...	...

现在，我想要一个 MySQL 查询，我只需向其提供 id [例如 id=19]，然后我应该获取其所有子 id [即结果应该有 id '20,21,22']....

子级的层次结构未知；它可能会有所不同......

我知道如何使用 for 循环来做到这一点...但是如何使用单个 MySQL 查询来实现相同的目的？

Answer 1

For MySQL 8: Use recursion Use syntax.
For MySQL 5.x: Use inline variables, path IDs, or self-joins.

MySQL 8

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;

The value specified in

parent_id = 19 should be set to the id of the parent whose descendants you want to select.

MySQL 5.x

For versions of MySQL (up to version 5.7) that do not support common table expressions, you can use the following query to achieve this:

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 is a violin.

Here, the value specified in @pv := '19' should be set to the id of the parent whose descendants you want to select.

This will also work if the parent has multiple children. However, each record is required to meet the parent_id < id< id condition, otherwise the result will be incomplete.

Variable assignment within query

This query uses specific MySQL syntax: variables are allocated and modified during execution. Some assumptions are made about the execution order:

• Evaluate the from clause first. This is where @pv is initialized.
• The where clause is evaluated for each record in the order in which it was retrieved from the from alias. Therefore, the condition set here only includes records whose parent has been identified as being in the descendant tree (all descendants of the primary parent will be incrementally added to @pv).
• The conditions in this where clause are evaluated sequentially, and evaluation is interrupted once the overall result is determined. So the second condition must be in second position because it adds the id to the parent list and this will only happen if the id passes the first condition. The length function is called just to ensure that this condition is always true, even if the pv string produces a false value for some reason.

All in all, one may find these assumptions too risky to rely on. DocumentationWarning:

So even though it is consistent with the above query, the order of evaluation may still change, for example, when you add conditions or use this query as a view or subquery within a larger query. This is a "feature" that will be removed in a future MySQL version :

As mentioned above, starting with MySQL 8.0, you should use recursive with syntax.

efficiency

For very large data sets, this solution may be slow because the find_in_set operation is not the most ideal way to find numbers in a list, and certainly does not achieve the same goal as The number of records returned.

Alternative 1: Use recursion, Connection

More and more databases implement SQL:1999 ISO standard WITH [RECURSIVE] Syntax for recursive queries (e.g. Postgres 8.4 , SQL Server 2005 , DB2, Oracle 11gR2 , SQLite 3.8.4 , Firebird 2.1 , H2 , HyperSQL 2.1.0 , Teradata, MariaDB 10.2.2 ). Starting with version 8.0, MySQL also supports it. See the top of this answer for the syntax to use.

Some databases have alternative non-standard syntax for hierarchical lookups, such as the Oracle, DB2, Informix, CUBRID and other databases.

MySQL version 5.7 does not provide such functionality. When your database engine provides this syntax or you can migrate to a database engine that provides this syntax, then this is undoubtedly the best choice. If not, consider the following alternatives.

Alternative 2: Path Style Identifier

Things become much easier if you assign id values ​​that contain hierarchical information (path). For example, in your case this might look like this:

ID	Name
19	Category 1
19/1	Category 2
1/19	Category 3
19/1/1/1	Category 4

Then your selection will look like this:

select  id,
        name 
from    products
where   id like '19/%'

Alternative 3: Repeated Self-Join

If you know the upper limit of the depth of the hierarchical tree, you can use the standard sql query like this:

select      p6.parent_id as parent6_id,
            p5.parent_id as parent5_id,
            p4.parent_id as parent4_id,
            p3.parent_id as parent3_id,
            p2.parent_id as parent2_id,
            p1.parent_id as parent_id,
            p1.id as product_id,
            p1.name
from        products p1
left join   products p2 on p2.id = p1.parent_id 
left join   products p3 on p3.id = p2.parent_id 
left join   products p4 on p4.id = p3.parent_id  
left join   products p5 on p5.id = p4.parent_id  
left join   products p6 on p6.id = p5.parent_id
where       19 in (p1.parent_id, 
                   p2.parent_id, 
                   p3.parent_id, 
                   p4.parent_id, 
                   p5.parent_id, 
                   p6.parent_id) 
order       by 1, 2, 3, 4, 5, 6, 7;

See thisfiddle

The

where condition specifies which parent's descendants you want to retrieve. You can expand this query to more levels as needed.