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

本文实例讲述了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事务操作技巧汇总》、《mysql存储过程技巧大全》、《mysql数据库锁相关技巧汇总》及《mysql常用函数大汇总》

希望本文所述对大家mysql数据库计有所帮助。