MySQ树状结构数据 递归查询

for example:

create table products(
  id int,
  name varchar(100),
  parent_id int
);
insert into products values 
(15, 'category15', 0), -- not a descendant of 19
(16, 'category16', 15), -- not a descendant of 19
(19, 'category19', 0),
(20, 'category20', 19), -- level 1
(21, 'category21', 20), -- level 2
(22, 'category22', 21), -- level 3
(23, 'category23', 19), -- level 1
(24, 'category24', 21), -- level 3
(25, 'category25', 22), -- level 4
(26, 'category26', 22), -- level 4
(27, 'category26', 25), -- level 5
(30, 'category21', 16); -- not a descendant of 19

solution:

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

result:

id name parent_id
20 category20 19
23 category23 19
21 category21 20
22 category22 21
24 category24 21
25 category25 22
26 category26 22
27 category26 25

指定的值@pv := ‘19’应该设置为id要选择所有子项的父id。

如果父节点有多个子节点，这一样有效。但是，要求每条记录都满足条件parent_id < id，否则结果不准确。

该查询使用特定的MySql语法：在执行期间分配和修改变量。对执行顺序做了一些假设：

from项首先被执行。所以这就是@pv初始化的地方。
where子句按照从from别名中检索的顺序执行每条记录。所以这是一个条件被放置的地方，只包括父母已经被识别为在后代树中的记录（主要父母的所有后代都被逐步添加到@pv）。
本节中的条件按where顺序进行评估，一旦总体结果确定，评估就会中断。因此，第二个条件必须排在第二位，因为它将它添加id到父列表中，并且只有在id传递第一个条件时才会发生。length函数仅用于确保此条件始终为真，即使该pv字符串会产生虚假值。
所以，人们可能会发现这些假设风险太高而无法依赖 - 它们没有文件保证，即使它一贯地工作，当您将此查询用作视图或子视图时，执行顺序在理论上可能仍会发生变化。

另外注意，如果是非常大的数据，这个解决方案可能会变慢，因为find_in_set操作并不是在列表中找到数字的最理想方式，当然不是在与数量相同的数量级达到大小的列表中记录返回。

方案1： WITH RECURSIVE，CONNECT BY
现在越来越多的数据库都支持以下标准：1999 ISO标准WITH [RECURSIVE]语法的递归查询（如Postgres的8.4+，SQL Server的2005+，DB2，甲骨文11gR2的+，SQLite的3.8.4+，火鸟2.1+，H2，的HyperSQL 2.1.0+，Teradata的，MariaDB 10.2.2+）。从版本8.0开始，MySql也将支持它。使用该语法，查询如下所示：

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;

一些数据库有用于分层查找的替代非标准语法，例如CONNECT BYOracle数据库上可用的子句。DB2也支持这种替代语法。

MySql 5.7不支持。如果你的数据库版本支持这种语法时，那最好了。如果不支持，请考虑以下选择。

方案2：id标示路径
如果id包含路径层次结构信息，那就很好处理。例如，在你的情况下，这可能看

ID       | NAME
19       | category1   
19/1     | category2  
19/1/1   | category3  
19/1/1/1 | category4  -- 

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

方案3：同表连接
如果知道层次结构树有几层，可以试试下面的sql：

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;

from:https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query 谷歌翻译