如何创建一个MySQL层级递归查询？

33 浏览
0 Comments

如何创建一个MySQL层级递归查询？

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

id name parent_id
19 category1 0
20 category2 19
21 category3 20
22 category4 21
... ... ...

现在，我想要一个单一的MySQL查询，只需提供id [比如说 id=19]，然后我就应该得到它的所有子id [即结果应该有\'id20、21、22\']....

子级的层次结构是未知的; 它可能会改变....

我知道如何使用一个for循环来实现... 但如何使用单一的MySQL查询实现相同的功能呢？

admin 更改状态以发布 2023年5月21日
0

2 答案

0 Comments

来自博客MySQL中的分层数据管理

表结构

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+

查询：

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

输出

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+

大多数用户在 SQL 数据库中处理分层数据时，不难学习到管理分层数据并不是关系型数据库的原本用途。关系型数据库的表不是分层结构（类似于XML），而仅仅是一个平面列表。分层数据具有父子关系，在关系型数据库表中并没有自然表示方式。
阅读更多

参考博客来获取更多详情。

编辑：

select @pv:=category_id as category_id, name, parent from category
join
(select @pv:=19)tmp
where parent=@pv

输出：

category_id name    parent
19  category1   0
20  category2   19
21  category3   20
22  category4   21

参考：如何在MySQL中进行递归SELECT查询？

0
0 Comments

对于MySQL 8+：使用递归的with语法。
对于MySQL 5.x：使用内联变量、路径ID或自连接。

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 = 19中指定的值应设置为您想要选择所有后代的父亲的id。

MySQL 5.x

对于不支持公共表表达式（版本5.7以下）的MySQL版本，可以使用以下查询实现：

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

这里，在@pv := '19'中指定的值应设置为您想要选择所有后代父亲的id。

这也适用于父母有多个孩子的情况。但是，需要满足每个记录都满足条件parent_id < id，否则结果将不完整。

查询内的变量赋值

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

  • 首先评估from子句。因此，这是@pv初始化的地方。
  • 对于从from别名检索的每个记录，where子句都会进行评估。因此，在此处设置条件，仅包括已确定为在后代树中的父代的记录（所有主要父代的后代逐步添加到@pv中）。
  • 在此where子句中的条件按顺序进行评估，并且只有在总结果确定时才会中止评估。因此，第二个条件必须位于第二个位置，因为它将id添加到父列表中，只有在id通过第一个条件时才会发生。只调用length函数是为了确保此条件始终为真，即使pv字符串由于某种原因产生了虚假值。

总而言之，人们可能会发现这些假设太冒险以依赖它们。文档警告：

您可能会得到预期的结果，但这不是保证的[...]涉及用户变量的表达式的评估顺序未定义。

因此，即使它与上面的查询一致地工作，评估顺序仍可能更改，例如当您添加条件或在更大的查询中使用此查询作为视图或子查询时。这是一个“特性”，将在未来的MySQL版本中被删除：

MySQL之前的版本允许在SET语句之外的语句中为用户变量分配值。为了向后兼容而支持这个功能在MySQL 8.0中，但是在以后的版本中就有可能被删除了。

如上所述，从MySQL 8.0开始，您应该使用递归的with语法。

效率

对于非常大的数据集，这种解决方案可能会变得缓慢，因为find_in_set操作不是在列表中查找数字的最理想方法，尤其是当列表的大小与返回的记录数目数量级相同时。

备选方案1：with recursive，connect by

越来越多的数据库采用 SQL:1999 ISO标准的WITH [RECURSIVE]语法进行递归查询（例如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+）。从8.0版本开始，MySQL也支持它。请参阅本答案顶部使用的语法。

一些数据库具有用于分层查找的替代非标准语法，例如在Oracle、DB2、Informix、CUBRID和其他数据库上可用的CONNECT BY子句。

MySQL版本5.7不提供此功能。如果您的数据库引擎提供此语法，或者您可以迁移到提供此功能的数据库引擎，则这肯定是最好的选择。如果没有，考虑以下替代方案。

替代方案2：路径式标识符

如果您为id赋予包含层次信息的层次结构值，则事情会变得更加容易：路径。例如，在您的情况下，这可能看起来像这样：

ID 名称
19 分类1
19/1 分类2
19/1/1 分类3
19/1/1/1 分类4

那么您的select将如下所示：

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;

请参见此示例

where条件指定要检索后代的父项。您可以根据需要扩展此查询的更多级别。

0