欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-12-21 08:26:34
本文实例讲述了mysql实现树状所有子节点查询的方法。分享给大家供大家参考,具体如下: 在oracle 中我们知道有一个 hierarchical queries 通过c...

本文实例讲述了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数据库计有所帮助。

上一篇:

下一篇: