mysql递归查询

最新推荐文章于 2024-09-27 09:59:22 发布
转载 最新推荐文章于 2024-09-27 09:59:22 发布 · 2w 阅读 · 7
文章标签：

#mysql #recursion #query #table #insert #function

 

在Oracle 中我们知道有一个 Hierarchical Queries 通过CONNECT BY 我们可以方便的查了所有当前节点下的所有子节点。但很遗憾，在MySQL的目前版本中还没有对应的功能。

 

在MySQL中如果是有限的层次，比如我们事先如果可以确定这个树的最大深度是4, 那么所有节点为根的树的深度均不会超过4，则我们可以直接通过left join 来实现。

 

但很多时候我们无法控制树的深度。这时就需要在MySQL中用存储过程来实现或在你的程序中来实现这个递归。本文讨论一下几种实现的方法。

 

样例数据：


mysql> create table treeNodes
    -> (
    ->  id int primary key,
    ->  nodename varchar(20),
    ->  pid int
    -> );
Query OK, 0 rows affected (0.09 sec)
mysql> select * from treenodes;
+----+----------+------+
| id | nodename | pid  |
+----+----------+------+
|  1 | A        |    0 |
|  2 | B        |    1 |
|  3 | C        |    1 |
|  4 | D        |    2 |
|  5 | E        |    2 |
|  6 | F        |    3 |
|  7 | G        |    6 |
|  8 | H        |    0 |
|  9 | I        |    8 |
| 10 | J        |    8 |
| 11 | K        |    8 |
| 12 | L        |    9 |
| 13 | M        |    9 |
| 14 | N        |   12 |
| 15 | O        |   12 |
| 16 | P        |   15 |
| 17 | Q        |   15 |
+----+----------+------+
17 rows in set (0.00 sec)

树形图如下


 1:A
  +-- 2:B
  |    +-- 4:D
  |    +-- 5:E
  +-- 3:C
       +-- 6:F
            +-- 7:G
 8:H
  +-- 9:I
  |    +-- 12:L
  |    |    +--14:N
  |    |    +--15:O
  |    |        +--16:P
  |    |        +--17:Q
  |    +-- 13:M
  +-- 10:J
  +-- 11:K  

 

方法一：利用函数来得到所有子节点号。

创建一个function getChildLst, 得到一个由所有子节点号组成的字符串. 


mysql> delimiter //
mysql>
mysql> CREATE FUNCTION `getChildLst`(rootId INT)
    -> RETURNS varchar(1000)
    -> BEGIN
    ->   DECLARE sTemp VARCHAR(1000);
    ->   DECLARE sTempChd VARCHAR(1000);
    ->
    ->   SET sTemp = '$';
    ->   SET sTempChd =cast(rootId as CHAR);
    ->
    ->   WHILE sTempChd is not null DO
    ->     SET sTemp = concat(sTemp,',',sTempChd);
    ->     SELECT group_concat(id) INTO sTempChd FROM treeNodes where FIND_IN_SET(pid,sTempChd)>0;
    ->   END WHILE;
    ->   RETURN sTemp;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;


使用我们直接利用find_in_set函数配合这个getChildlst来查找


mysql> select getChildLst(1);
+-----------------+
| getChildLst(1)  |
+-----------------+
| $,1,2,3,4,5,6,7 |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from treeNodes
    -> where FIND_IN_SET(id, getChildLst(1));
+----+----------+------+
| id | nodename | pid  |
+----+----------+------+
|  1 | A        |    0 |
|  2 | B        |    1 |
|  3 | C        |    1 |
|  4 | D        |    2 |
|  5 | E        |    2 |
|  6 | F        |    3 |
|  7 | G        |    6 |
+----+----------+------+
7 rows in set (0.01 sec)

mysql> select * from treeNodes
    -> where FIND_IN_SET(id, getChildLst(3));
+----+----------+------+
| id | nodename | pid  |
+----+----------+------+
|  3 | C        |    1 |
|  6 | F        |    3 |
|  7 | G        |    6 |
+----+----------+------+
3 rows in set (0.01 sec)

 

优点: 简单，方便，没有递归调用层次深度的限制 (max_sp_recursion_depth,最大255) ；

缺点：长度受限，虽然可以扩大 RETURNS varchar(1000)，但总是有最大限制的。

MySQL目前版本( 5.1.33-community)中还不支持function 的递归调用。

 

方法二：利用临时表和过程递归

创建存储过程如下。createChildLst 为递归过程，showChildLst为调用入口过程，准备临时表及初始化。


mysql> delimiter //
mysql>
mysql> # 入口过程
mysql> CREATE PROCEDURE showChildLst (IN rootId INT)
    -> BEGIN
    ->  CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst
    ->   (sno int primary key auto_increment,id int,depth int);
    ->  DELETE FROM tmpLst;
    ->
    ->  CALL createChildLst(rootId,0);
    ->
    ->  select tmpLst.*,treeNodes.* from tmpLst,treeNodes where tmpLst.id=treeNodes.id order by tmpLst.sno;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> # 递归过程
mysql> CREATE PROCEDURE createChildLst (IN rootId INT,IN nDepth INT)
    -> BEGIN
    ->  DECLARE done INT DEFAULT 0;
    ->  DECLARE b INT;
    ->  DECLARE cur1 CURSOR FOR SELECT id FROM treeNodes where pid=rootId;
    ->  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    ->
    ->  insert into tmpLst values (null,rootId,nDepth);

    ->
    ->  OPEN cur1;
    ->
    ->  FETCH cur1 INTO b;
    ->  WHILE done=0 DO
    ->          CALL createChildLst(b,nDepth+1);
    ->          FETCH cur1 INTO b;
    ->  END WHILE;
    ->
    ->  CLOSE cur1;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

调用时传入结点


mysql> call showChildLst(1);
+-----+------+-------+----+----------+------+
| sno | id   | depth | id | nodename | pid  |
+-----+------+-------+----+----------+------+
|   4 |    1 |     0 |  1 | A        |    0 |
|   5 |    2 |     1 |  2 | B        |    1 |
|   6 |    4 |     2 |  4 | D        |    2 |
|   7 |    5 |     2 |  5 | E        |    2 |
|   8 |    3 |     1 |  3 | C        |    1 |
|   9 |    6 |     2 |  6 | F        |    3 |
|  10 |    7 |     3 |  7 | G        |    6 |
+-----+------+-------+----+----------+------+

7 rows in set (0.13 sec)

Query OK, 0 rows affected, 1 warning (0.14 sec)

mysql>
mysql> call showChildLst(3);
+-----+------+-------+----+----------+------+
| sno | id   | depth | id | nodename | pid  |
+-----+------+-------+----+----------+------+
|   1 |    3 |     0 |  3 | C        |    1 |
|   2 |    6 |     1 |  6 | F        |    3 |
|   3 |    7 |     2 |  7 | G        |    6 |
+-----+------+-------+----+----------+------+

3 rows in set (0.11 sec)

Query OK, 0 rows affected, 1 warning (0.11 sec)

depth 为深度，这样可以在程序进行一些显示上的格式化处理。类似于oracle中的 level 伪列。sno 仅供排序控制。这样你还可以通过临时表tmpLst与数据库中其它表进行联接查询。

 

MySQL中你可以利用系统参数 max_sp_recursion_depth 来控制递归调用的层数上限。如下例设为12.


mysql> set max_sp_recursion_depth=12;
Query OK, 0 rows affected (0.00 sec)

 

优点 : 可以更灵活处理，及层数的显示。并且可以按照树的遍历顺序得到结果。

缺点 : 递归有255的限制。

 

方法三：利用中间表和过程

(本方法由yongyupost2000提供样子改编)

创建存储过程如下。由于MySQL中不允许在同一语句中对临时表多次引用，只以使用普通表tmpLst来实现了。当然你的程序中负责在用完后清除这个表。

 


delimiter //

drop PROCEDURE IF EXISTS  showTreeNodes_yongyupost2000//

CREATE PROCEDURE showTreeNodes_yongyupost2000 (IN rootid INT)
BEGIN
 DECLARE Level int ;
 drop TABLE IF EXISTS tmpLst;
 CREATE TABLE tmpLst (
  id int,
  nLevel int,
  sCort varchar(8000)
 );
 
 Set Level=0 ;
 INSERT into tmpLst SELECT id,Level,ID FROM treeNodes WHERE PID=rootid;
 WHILE ROW_COUNT()>0 DO
  SET Level=Level+1 ;
  INSERT into tmpLst
   SELECT A.ID,Level,concat(B.sCort,A.ID) FROM treeNodes A,tmpLst B
    WHERE  A.PID=B.ID AND B.nLevel=Level-1  ;
 END WHILE;
 
END;
//

delimiter ;

CALL showTreeNodes_yongyupost2000(0);

执行完后会产生一个tmpLst表，nLevel 为节点深度，sCort 为排序字段。
使用方法


SELECT concat(SPACE(B.nLevel*2),'+--',A.nodename)
FROM treeNodes A,tmpLst B
WHERE A.ID=B.ID
ORDER BY B.sCort;

+--------------------------------------------+
| concat(SPACE(B.nLevel*2),'+--',A.nodename) |
+--------------------------------------------+
| +--A                                       |
|   +--B                                     |
|     +--D                                   |
|     +--E                                   |
|   +--C                                     |
|     +--F                                   |
|       +--G                                 |
| +--H                                       |
|   +--J                                     |
|   +--K                                     |
|   +--I                                     |
|     +--L                                   |
|       +--N                                 |
|       +--O                                 |
|         +--P                               |
|         +--Q                               |
|     +--M                                   |
+--------------------------------------------+
17 rows in set (0.00 sec)

 

 

 

优点 : 层数的显示。并且可以按照树的遍历顺序得到结果。没有递归限制。
缺点 : MySQL中对临时表的限制，只能使用普通表，需做事后清理。

MySQL中进行树状所有子节点的查询
ACMAIN_CHM的专栏
05-02 14万+
在Oracle 中我们知道有一个 Hierarchical Queries 通过CONNECT BY 我们可以方便的查了所有当前节点下的所有子节点。但很遗憾，在MySQL的目前版本中还没有对应的功能。 在MySQL中如果是有限的层次，比如我们事先如果可以确定这个树的最大深度是4, 那么所有节点为根的树的深度均不会超过4，则我们可以直接通过left join 来实现。 但很多时候我们
如何使用递归查询（例如使用 WITH RECURSIVE）？
最新发布
破损的天堂鸟博客
09-19 892
递归查询是一种特殊的公共表表达式（CTE），允许查询自我引用以处理层次结构或树状数据。其核心原理是通过非递归项（基础查询）和递归项（递归部分）的组合，迭代生成结果集直至满足终止条件。PostgreSQL中的递归查询必须使用关键字来启用递归功能，这是与其他非递归CTE的关键区别。递归CTE的定义包含两个基本组成部分：非递归项作为查询的起始点，定义了初始数据集；递归项则通过引用CTE自身的输出生成后续层级的数据。
mysql 递归查询
weixin_33810006的博客
10-09 215
1、创建表： DROP TABLE IF EXISTS `t_areainfo`; CREATE TABLE `t_areainfo` ( `id` int(11) NOT '0' AUTO_INCREMENT, `level` int(11) DEFAULT '0', `name` varchar(255) DEFAULT '0', `parentId` int(11) ...
MySQL-递归查询
weixin_51456741的博客
08-19 4684
相信大家在平时开发的时候都遇见过以下这种树形数据这种树形数据如何落库应该这里就不赘述了核心就是使用额外一个字段parent_id保存父亲节点的id，如下图所示这里的classpath指的是当前节点的路径，后续说明其作用现有需求如下：1、查询指定id的分类节点的所有2、查询指定id的分类节点的所有3、查询整棵分类树，可指定。
MySQL实现树状所有子节点查询的方法
09-10
主要介绍了MySQL实现树状所有子节点查询的方法,涉及mysql节点查询、存储过程调用等操作技巧,具有一定参考借鉴价值,需要的朋友可以参考下
MySQL递归查询
07-15
MySQL 递归查询实现方法 MySQL 递归查询是指在 MySQL 中实现类似 Oracle Hierarchical Queries 的功能，用于查询树形结构中的所有子节点。由于 MySQL 目前还没有内置的递归查询功能，因此需要使用其他方法来实现。 ...
MySQL递归查询笔记
黄团团的个人博客
09-27 1115
创建表结构和插入数据、查询所有子节点、查询所有父节点、查询指定节点的根节点、查询所有兄弟节点（同级节点）、获取祖先节点及其所有子节点、查询每个节点之间的层级关系、查询指定节点之间的层级关系。
mysql递归查询.txt
10-30
### MySQL 递归查询知识点详解 #### 一、MySQL 递归查询概述 在数据库查询中，递归查询主要用于处理层级或树形结构的数据。这类数据的特点是每一项记录不仅包含自身的信息，还可能与其他记录形成上下级关系。MySQL...
MySQL递归查询树状表的子节点、父节点具体实现
09-10
在MySQL中，处理树状结构的数据表时，由于不直接支持循环递归查询，因此需要采用一些特殊的技巧来实现对子节点和父节点的查找。本例中提到了两个存储过程，分别用于获取树状表中的子节点列表和父节点列表。 1. 获取...
mysql递归查询.doc
08-17
MySQL 递归查询实现多分类查询 MySQL 递归查询是指在 MySQL 数据库中使用递归函数来实现树形结构数据的查询，例如部门表中某个部门的所有下属部分或者某个部分的所有上级部门。 在 MySQL 中实现递归查询需要使用...
MySQL的递归查询
walle的博客
11-15 1057
https://blog.csdn.net/wangchaoqi1985/article/details/80772562
mysql以及oracle的递归查询
yangnianbing110的专栏
07-03 8687
在oracle中实现递归查询的途径jiaoduo
mySQL递归查询
03-25
<think>嗯，用户问的是如何在MySQL中使用递归查询，特别是CTE的例子。我需要先回忆一下MySQL对CTE的支持情况。记得从MySQL 8.0开始支持CTE，包括递归的。那用户可能用的是8.0或更高版本，但需要确认版本兼容性。 首先，CTE的基本结构是WITH子句，然后定义CTE的名称和列，再写递归部分。递归CTE通常由两部分组成：非递归的种子查询和递归部分，用UNION ALL连接。比如生成数字序列，或者层次结构查询，比如组织架构或者树形数据。 用户可能想要一个具体的例子，比如生成一个数列，或者遍历树形结构的数据。例如，生成1到5的数字，或者查询员工层级。比如引用中的例子，递归部分是从种子开始，逐步增加n直到满足条件。例如，引用1和3中提到的例子，使用n < 5或者n < 6，然后递归增加n的值。 我需要确保例子正确，符合MySQL语法。比如，在递归CTE中，种子部分初始化，然后递归部分引用CTE自身。需要注意，递归CTE必须使用UNION ALL，并且递归部分不能包含聚合函数、窗口函数等。 另外，用户可能遇到的问题，比如无限递归，这时候可能需要设置cte_max_recursion_depth参数。比如默认是1000，超过会报错。所以在例子中可能需要提到这一点，或者给出如何修改这个设置的方法。 可能还需要举一个实际数据表的例子，比如员工表，里面有id和manager_id，然后通过递归CTE查询某个员工的所有下属。这时候CTE的种子是选择某个经理，然后递归连接下属员工。 需要确保例子中的SQL语句正确，比如正确的列名，正确的递归条件。比如在员工例子中，种子部分选择id为1的员工，然后递归部分连接员工表，找到manager_id等于当前层id的员工，直到没有更多层级。 此外，用户可能想知道递归CTE的应用场景，比如处理树形数据、生成序列、展开层次结构等。可以结合引用中的例子，如引用2提到的重用子查询，但这里主要是递归应用。 最后，可能需要提醒用户关于版本要求，以及常见错误，比如忘记UNION ALL，或者递归条件不正确导致无限循环。同时，给出如何限制递归深度的方法，避免报错。</think>在MySQL中，使用公共表表达式（CTE）实现递归查询的步骤如下： ### 递归CTE基本结构 ```sql WITH RECURSIVE cte_name AS ( -- 初始查询（种子成员） SELECT ... UNION ALL -- 递归成员（引用CTE自身） SELECT ... FROM cte_name ... ) SELECT * FROM cte_name; ``` ### 示例1：生成数字序列 生成1-5的整数序列： ```sql WITH RECURSIVE numbers AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM numbers WHERE n < 5 ) SELECT * FROM numbers; ``` 结果： ``` n 1 2 3 4 5 ``` 该查询通过递归将初始值1逐次+1，直到满足`n < 5`的条件[^1][^3] ### 示例2：层次结构遍历 假设有员工表`employees`： ```sql CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(20), manager_id INT ); INSERT INTO employees VALUES (1, 'CEO', NULL), (2, 'CTO', 1), (3, 'Dev Lead', 2), (4, 'Senior Dev', 3); ``` 查询CTO（id=2）的下属层级： ```sql WITH RECURSIVE emp_tree AS ( SELECT id, name, manager_id, 0 AS depth FROM employees WHERE id = 2 UNION ALL SELECT e.id, e.name, e.manager_id, et.depth + 1 FROM employees e INNER JOIN emp_tree et ON e.manager_id = et.id ) SELECT * FROM emp_tree; ``` 结果： ``` id | name | manager_id | depth 2 | CTO | 1 | 0 3 | Dev Lead | 2 | 1 4 | Senior Dev| 3 | 2 ``` 通过递归连接实现层级展开，`depth`列记录层级深度[^4] ### 注意事项 1. 必须包含`RECURSIVE`关键字 2. 递归成员不能包含： - 聚合函数 - 窗口函数 - `GROUP BY`/`ORDER BY` - `DISTINCT` 3. 默认递归深度限制为1000次，可通过`SET @@cte_max_recursion_depth = 1000000;`调整[^1]
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值