`
高级java工程师
  • 浏览: 409034 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

mysql中递归树状结构

阅读更多

在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中用存储过程比较简单的实现方法。




分享到:
评论

相关推荐

    mysql 树形结构查询

    在 MySQL 中,树形结构查询可以使用递归存储过程来实现。递归存储过程是一种特殊的存储过程,可以递归调用自身,以实现树形结构的查询。递归存储过程可以根据需要设置递归深度,以控制查询的深度。 在上面的例子中...

    MySQL多种递归查询方法.docx

    此语句允许用户按照树状结构来检索数据。 ##### 1. `START WITH CONNECT BY PRIOR`用法详解 **基本语法**: ```sql SELECT * FROM table_name START WITH condition CONNECT BY PRIOR child_column = parent_...

    PHP MYSQL 用递归写的留言本核心程序

    如果留言可以有多个层级的回复,比如一级回复、二级回复等,递归则可以有效地遍历所有层级,展示出完整的留言树状结构。通过在PHP中定义一个递归函数,可以反复调用自身来处理每个级别的回复,直到没有更多的子回复...

    mysql递归调用获取树节点(子树)

    在MySQL数据库中,处理层级关系数据,如组织结构、菜单系统或分类树等,常常需要进行递归查询来获取树状结构的子节点。这里我们将深入探讨如何在MySQL中使用存储过程来实现这一功能。 首先,为了理解这个过程,我们...

    递归查询菜单树,支持mysql,oracle

    在菜单树场景中,每个菜单项可能有子菜单项,形成一种树状结构。通过递归查询,我们可以获取到所有级别的菜单项,包括它们的父级和子级。 在MySQL中,我们可以使用自连接来实现递归查询。假设我们有一个名为`menus`...

    MySQL递归查询树状表的子节点、父节点具体实现

    如果树状结构非常深或数据量大,考虑使用预计算的路径或者邻接列表等其他数据模型,或者升级到支持递归查询的MySQL版本可能更有效。 总结来说,要在MySQL中处理树状数据,尤其是在不支持递归查询的版本中,需要使用...

    使用递归删除树形结构的所有子节点(java和mysql实现)

    在树形结构中删除某个父节点时,需要递归删除其所有子节点,以避免遗留冗余数据。下面将为大家介绍使用 Java 和 MySQL 实现递归删除树形结构的所有子节点的方法。 一、业务场景 在树形结构中,删除某个父节点时,...

    php数组生成树

    此函数会遍历给定的扁平数组,每次找到一个父ID匹配当前根节点的元素,就将其添加到树结构中,并递归地为其添加子节点。这样,我们可以得到一个无限深度的树结构。 使用这个函数,我们可以轻松地将`$flatArray`转换...

    教你如何使用MySQL8递归的方法

    在这个结构中,`cte_name`是你定义的CTE的名称,第一个`SELECT`语句用于返回初始的结果集,而第二个`SELECT`语句则是递归的部分,它使用前面结果集的数据进行再次查询,直到没有更多的数据返回。`UNION ALL`或`UNION...

    MySQL实现树状所有子节点查询的方法

    以下将详细介绍几种在MySQL中实现树状结构子节点查询的方法。 **方法一:利用函数获取所有子节点号** 1. **创建自定义函数`getChildLst`**: 在MySQL中,可以创建一个名为`getChildLst`的存储函数,它接收一个根...

    利用java+mysql递归实现拼接树形JSON列表的方法示例

    本篇文章将详细讲解如何利用Java和MySQL递归地实现拼接树形JSON列表的方法。 首先,我们需要理解问题的整体思路。在数据库中,我们可以将每个分类(或节点)存储为一个记录,包含ID、父ID(PID)以及名称等字段。...

    php+mysql不用递归实现的无限级分类实例(非递归)

    它通过`substr_count`函数来计算`path`字段中逗号的数量,以此来确定当前分类的层级,并据此增加相应的空格,从而实现树状结构的缩进效果。 2. 通过另一种方式展示无限级分类: ```php $conn = mysql_connect('...

    MySQL通过自定义函数实现递归查询父级ID或者子级ID

    在MySQL中,递归查询通常用于处理层次结构的数据,如组织结构、菜单系统或类别树等。当数据的层级关系无法预知或者可能无限深时,传统的JOIN操作可能无法满足需求,此时就需要自定义函数来实现递归查询。本文将详细...

    MySQL存储过程完整版使用代码示例

    资源包中囊括了MySQL数据库中的存储过程的使用包含的基本结构及日常所使用到的基本函数的使用【包括java端调用存储过程,创建临时表,动态执行sql语句,过程的递归调用,指针循环取数,批量创建表删除表,树状结构的...

    PHP递归写入MySQL实现无限级分类数据操作示例

    在PHP程序设计中,实现无限级分类...实际应用中,这些操作对于构建复杂的树状数据结构非常有效,并且通过递归函数的运用,可以简化操作流程,提高代码的可读性和可维护性。希望这些内容能够帮助到正在学习PHP的朋友们。

    自己写的php 递归无限分类 附有sql文件

    在无限分类中,递归常被用来遍历层级关系,例如在数据库中的分类表中,每个类别可能有多个子类别,而这些子类别又可以有各自的子类,形成一种树状结构。PHP的递归函数可以遍历这种结构,展示所有层次。 接着,我们...

    分层数据管理和树状结构

    ### 分层数据管理和树状结构 #### 一、引言 在数据库管理中,分层数据的处理是一项常见但又充满挑战的任务。由于关系数据库的基本结构是平面化的表,因此如何有效地存储和查询分层数据成为了数据库设计者们关注的...

    Java中的无限层级递归树前后端操作解决方案.docx

    在本解决方案中,我们将探讨 Java 中的无限层级递归树前后端操作解决方案,解决方案涵盖前端 Vue 无限层级树实现技术大纲、Java 无限递归层级树方案、前端数据结构、MySQL 数据库设计、后端树状接口业务领域模型 DTO...

Global site tag (gtag.js) - Google Analytics