MySQL树状结构数据递归查询实例讲解

程序员文章站 2022-08-31 18:43:04

for example: create table products( id int, name varchar(100), parent_id int...

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要选择所有后代的父项。

如果父母有多个孩子，这也将起作用。但是，要求每条记录都满足条件parent_id < id，否则结果将不完整。

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

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

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

选择1： WITH RECURSIVE，CONNECT BY

越来越多的数据库执行SQL：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：路径样式标识符

如果知道层次结构树可以变得多深的上限，则可以使用如下标准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;

上一篇： python中正则表达式的使用方法

下一篇：智慧屏V85等升级HarmonyOS 2.0 解锁五大新功能

MySQL树状结构数据递归查询实例讲解