本文实例讲述了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数据库计有所帮助。
如对本文有疑问， 点击进行留言回复！！
服务器Centos部署MySql并连接Navicat过程详解
网友评论