ForMySQL 8:Use recursionUsesyntax.
ForMySQL 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;

parent_id = 19should be set to theidof 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 aviolin.

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

This will also work if the parent has multiple children. However, each record is required to meet theparent_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 thefromclause first. This is where@pvis initialized.
• Thewhereclause is evaluated for each record in the order in which it was retrieved from thefromalias. 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 thiswhereclause 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. Thelengthfunction is called just to ensure that this condition is always true, even if thepvstring 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 recursivewithsyntax.

efficiency

For very large data sets, this solution may be slow because thefind_in_setoperation 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 implementSQL:1999 ISO standardWITH [RECURSIVE]Syntaxfor recursive queries (e.g.Postgres 8.4,SQL Server 2005 a>,DB2,Oracle 11gR2,SQLite 3.8.4,Firebird 2.1,H2,HyperSQL 2.1.0,Teradata,MariaDB 10.2.2). Starting withversion 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 theOracle,DB2,Informix,CUBRID a> 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:

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

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;