在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中用存储过程比较简单的实现方法。
行了！！！！！！！！！！！！！采纳吧！！！！！！！！！！！！！
2011年10月10日 17:19
添加评论
关注(0)
相关推荐
在Java递归树型结构通用数据库中，提供了部门信息查询接口，包括根据部门ID查询部门信息、根据部门名称查询部门信息等接口。这些接口使用Java语言实现，具有良好的可扩展性和可维护性。 5. 数据库设计 在Java递归...
java 递归读取文件夹 读取文件 写文件java 递归读取文件夹 读取文件 写文件java 递归读取文件夹 读取文件 写文件java 递归读取文件夹 读取文件 写文件java 递归读取文件夹 读取文件 写文件java 递归读取文件夹 读取...
Java 递归算法构造 JSON 树形结构 Java 递归算法构造 JSON 树形结构是指通过 Java 语言使用递归算法将数据库中的菜单表构建成树形的 JSON 格式发送给第三方。这种方法可以将复杂的树形结构数据转换成易于理解和处理...
通过学习和理解这些代码，你可以深入理解如何使用Java递归处理无限层级的树结构，这对于开发涉及树形数据的系统非常有用。 总之，使用Java递归实现无限层级树的关键在于定义好树节点类，明确生成新节点的条件，以及...
Java SE程序 递归Java SE程序 递归Java SE程序 递归Java SE程序 递归Java SE程序 递归Java SE程序 递归Java SE程序 递归Java SE程序 递归Java SE程序 递归Java SE程序 递归Java SE程序 递归Java SE程序 递归Java SE...
在本示例中，"mybatis递归查询菜单树"是一个基于SpringBoot架构的应用，它演示了如何利用MyBatis进行递归查询来构建菜单树结构。此外，这个项目还集成了JTA（Java Transaction API）以支持分布式事务处理，并使用了...
通过阅读“java 递归问题文档”，你可以期待学习到如何定义和使用递归函数，如何设计和分析递归算法的效率，以及如何避免常见的递归陷阱。同时，提供的练习代码将让你有机会动手实践，巩固理论知识。 总结来说，...
java递归算法,java递归算法,java递归算法
在这个特定的项目中，我们有一个Java程序，它使用递归算法来解决查询某市地铁的最短路径的问题。递归算法是一种强大的工具，它通过将大问题分解为更小的相似子问题来解决复杂的问题。 首先，我们要理解什么是递归。...
Java递归算法是一种重要的编程技巧，它通过函数自身调用自身来解决问题。在Java中，递归主要用于解决那些可以通过简化规模来逐步逼近解决方案的问题。这种技术在数据结构（如树和图）、排序算法（如快速排序和归并...
用递归的方法实现九宫格的初始化,可加深对二维数组传递参数的理解.
Java递归将List转为树形结构 博客地址：https://blog.csdn.net/weixin_38500202/article/details/110456363
Java 递归例子 Java 递归是指在 Java 编程语言中，使用递归函数来解决问题的方法。递归函数是指在函数体中调用自身的函数。Java 递归例子中提供了三个经典的递归例子：汉诺塔问题、斐波那契级数和最大公约数。 1. ...
Java递归是编程中一种强大的技术，主要用于解决那些可以分解为相同或类似子问题的问题。在Java中，递归主要应用于排序和查找算法，这两者都是数据处理的基础操作。本资料包聚焦于如何使用递归来实现这些算法，下面将...
一个简单的小例子递归实现list按照index排序的树
java递归小例子，供初学者学习使用。九九递归
Java递归是编程中的一个重要概念，它是指在函数或方法的定义中调用自身的过程。在Java中，递归通常用于解决那些可以被简化为规模更小的相同问题的复杂问题，例如遍历数据结构（如树和图）、计算阶乘、搜索算法等。...
Java 递归算法浅谈 Java 递归算法是 Java 编程中的一种常见算法，通过自调用函数实现复杂问题的解决。下面是 Java 递归算法的相关知识点。 一、递归函数的定义 递归函数是指在函数体内直接或间接地调用自己，即...
本文将详细解析标题为“Java递归获取匹配后缀的文件列表”的程序，它演示了如何使用Java来递归地查找指定路径下具有特定后缀的文件，并将它们的路径或名称存储在列表中。 首先，让我们了解递归的基本概念。递归是指...
总结起来，"Java写的递归下降分析程序" 是一个基于Java实现的编译器前端组件，用于解析输入的源代码，将其转换成抽象语法树。它利用递归函数模拟上下文无关文法的推导过程，但可能处于未完成或存在错误的状态。通过...