文档章节

MySQL中进行树状所有子节点的查询

微笑出品
 微笑出品
发布于 2017/02/04 17:15
字数 2340
阅读 43
收藏 1

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 的递归调用。

 

我自己改的方法一  可以解决返回值最大长度限制问题

----------------------------------------------------------------------

DELIMITER //
CREATE PROCEDURE getChildList(rootId INT)
BEGIN
    DECLARE sTemp VARCHAR(100000);
    DECLARE sTempChd VARCHAR(10000);
    
    SET sTemp='$';
    SET sTempChd=CAST(rootId AS CHAR);
    
    WHILE sTempChd IS NOT NULL DO    
        SET sTemp=CONCAT(sTemp,',',sTempChd);
        SELECT GROUP_CONCAT(member_id) INTO sTempChd FROM member_info 
        WHERE FIND_IN_SET(parent_id,sTempChd)>0;
    END WHILE;
    SELECT member_id AS memberId,parent_id AS parentId FROM member_info WHERE FIND_IN_SET(member_id,sTemp);
END
//
DELIMITER ;

CALL getChildList(142182);

----------------------------------------------------------------------

 

方法二:利用临时表和过程递归

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

 

 

=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

--表结构

CREATE TABLE `address` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code_value` varchar(32) DEFAULT NULL COMMENT '区域编码',
`name` varchar(128) DEFAULT NULL COMMENT '区域名称',
`remark` varchar(128) DEFAULT NULL COMMENT '说明',
`pid` varchar(32) DEFAULT NULL COMMENT 'pid是code_value',
PRIMARY KEY (`id`),
KEY `ix_name` (`name`,`code_value`,`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=1033 DEFAULT CHARSET=utf8 COMMENT='行政区域表';

 


--mysql 实现树结构查询
--方法一

CREATE PROCEDURE sp_showChildLst(IN rootId varchar(20))
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst
(sno int primary key auto_increment,code_value VARCHAR(20),depth int);
DELETE FROM tmpLst;

CALL sp_createChildLst(rootId,0);

select tmpLst.*,address.* from tmpLst,address where tmpLst.code_value=address.code_value order by tmpLst.sno;
END

 

CREATE PROCEDURE sp_createChildLst(IN rootId varchar(20),IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b VARCHAR(20);
DECLARE cur1 CURSOR FOR SELECT code_value FROM address where pid=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

insert into tmpLst values (null,rootId,nDepth);

SET @@max_sp_recursion_depth = 10;
OPEN cur1;

FETCH cur1 INTO b;
WHILE done=0 DO
CALL sp_createChildLst(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;

CLOSE cur1;
END

--方法二(此方法有线程安全问题)

CREATE PROCEDURE sp_getAddressChild_list(in idd varchar(36))
begin
declare lev int;
set lev=1;
drop table if exists tmp1;
CREATE TABLE tmp1(code_value VARCHAR(36),`name` varchar(50),pid varchar(36) ,levv INT);
INSERT tmp1 SELECT code_value,`name`,pid,1 FROM address WHERE pid=idd;
while row_count()>0
do
set lev=lev+1;
INSERT tmp1 SELECT t.code_value,t.`name`,t.pid,lev from address t join tmp1 a on t.pid=a.code_value AND levv=lev-1;
end while ;
INSERT tmp1 SELECT code_value,`name`,pid,0 FROM address WHERE code_value=idd;
SELECT * FROM tmp1;
end

--方法三

CREATE FUNCTION fn_getAddress_ChildList_test(rootId INT) RETURNS varchar(1000) CHARSET utf8 #rootId为你要查询的节点
BEGIN

#声明两个临时变量
DECLARE temp VARCHAR(1000);
DECLARE tempChd VARCHAR(1000);
SET temp = '$';
SET tempChd=CAST(rootId AS CHAR);#把rootId强制转换为字符

WHILE tempChd is not null DO
SET temp = CONCAT(temp,',',tempChd);#循环把所有节点连接成字符串。
SELECT GROUP_CONCAT(code_value) INTO tempChd FROM address where FIND_IN_SET(pid,tempChd)>0;
END WHILE;
RETURN temp;

END

--方法四

CREATE PROCEDURE sp_findAddressChild(iid varchar(50),layer bigint(20))
BEGIN 
/*创建接受查询的临时表 */ 
create temporary table if not exists tmp_table(id varchar(50),code_value varchar(50),name varchar(50),pid VARCHAR(50)) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
/*最高允许递归数*/ 
SET @@max_sp_recursion_depth = 10 ; 
call sp_iterativeAddress(iid,layer);/*核心数据收集*/ 
select * from tmp_table ;/* 展现 */ 
drop temporary table if exists tmp_table ;/*删除临时表*/ 
END


CREATE PROCEDURE sp_iterativeAddress(iid varchar(50),layer bigint(20))
BEGIN 
DECLARE t_id INT;
declare t_codeValue varchar(50) default iid ; 
declare t_name varchar(50) character set utf8; 
declare t_pid varchar(50) character set utf8; 

/* 游标定义 */ 
declare cur1 CURSOR FOR select id,code_value,`name`,pid from address where pid=iid ; 
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET t_codeValue = null; 

/* 允许递归深度 */ 
if layer>0 then 
OPEN cur1 ; 
FETCH cur1 INTO t_id,t_codeValue,t_name,t_pid ; 
WHILE ( t_codeValue is not null ) 
DO 
/* 核心数据收集 */ 
insert into tmp_table values(t_id,t_codeValue,t_name,t_pid); 
call sp_iterativeAddress(t_codeValue,layer-1); 
FETCH cur1 INTO t_id,t_codeValue,t_name,t_pid ; 
END WHILE; 
end if; 
END

--方法五 SQL实现

SELECT `name`,code_value AS code_value,pid AS 父ID ,levels AS 父到子之间级数, paths AS 父到子路径 FROM (
SELECT `name`,code_value,pid,
@le:= IF (pid = 0 ,0, 
IF( LOCATE( CONCAT('|',pid,':'),@pathlevel) > 0 , 
SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',pid,':'),-1),'|',1) +1
,@le+1) ) levels
, @pathlevel:= CONCAT(@pathlevel,'|',code_value,':', @le ,'|') pathlevel
, @pathnodes:= IF( pid =0,',0', 
CONCAT_WS(',',
IF( LOCATE( CONCAT('|',pid,':'),@pathall) > 0 , 
SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',pid,':'),-1),'|',1)
,@pathnodes ) ,pid ) )paths
,@pathall:=CONCAT(@pathall,'|',code_value,':', @pathnodes ,'|') pathall 
FROM address, 
(SELECT @le:=0,@pathlevel:='', @pathall:='',@pathnodes:='') vv
ORDER BY pid,code_value
) src
ORDER BY pid

--方法6  存储过程(SQL实现)

create procedure query_all_add_children(in inPid varchar(50))
begin
select id,code_value,name,remark,pid,p2id,p3id,p4id,p5id 
from(
select a1.id,a1.code_value,a1.name,a1.remark,
a1.pid,a2.pid p2id,a3.pid p3id,a4.pid p4id,a5.pid p5id 
from 
address a1 left join address a2
on(a1.pid=a2.code_value)
left join address a3
on(a2.pid=a3.code_value)
left join address a4
on(a3.pid=a4.code_value)
left join address a5
on(a4.pid=a5.code_value)
) al
where 
(pid=inPid
or p2id=inPid
or p3id=inPid
or p4id=inPid
or p5id=inPid
);
end

 

个人的一些理解:我是用的方法一:取出所有节点利用MySql函数截取所需要的字符串,然后在SQL中字段IN(调用此方法)来进行查询,这样效率比较高,方法6效率也较高,其他方法都有效率问题。 

本文转载自:http://blog.csdn.net/ACMAIN_CHM/article/details/4142971#comments

上一篇: IDEA
微笑出品
粉丝 1
博文 57
码字总数 5691
作品 0
大连
私信 提问
左右值无限分类实现算法

一、引言 产 品分类,多级的树状结构的论坛,邮件列表等许多地方我们都会遇到这样的问题:如何存储多级结构的数据?在PHP的应用中,提供后台数据存储的通常是关系型 数据库,它能够保存大量的...

bengozhong
2016/09/09
116
0
【转】左右值无限分类实现算法

一、引言 产品分类,多级的树状结构的论坛,邮件列表等许多地方我们都会遇到这样的问题:如何存储多级结构的数据?在PHP的应用中,提供后台数据存储的通常是关系型数据库,它能够保存大量的数...

王二铁
2011/11/01
196
0
oracle中用START WITH...CONNECT BY PRIOR子句实现递归查询

今天发现在oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询,connect by 是结构化查询中用到的,其基本语法是: select ... from tablename start with cond1 con...

zhengjunbo
2013/06/04
90
0
PHP+Mysql树型结构(无限分类)数据库设计的2种方式实例

我们经常需要在关系型数据库中保存一些树状结构数据,比如分类、菜单、论坛帖子树状回复等。常用的方法有两种: 1. 领接表的方式; 2. 预排序遍历树方式; 假设树状结构如下图: 领接表方式 ...

我心中有猛狗
2016/10/29
378
0
求教如何优化这条SQL语句?

SQL语句如下: SELECT USER_NAME, PASSWORD, NICK_NAME FROM T_APP_USERS WHERE FIND_IN_SET(MERCHANT_ID, getChildList(getParentRoot(1))); 情况是这样的:我有一张商户表T_MERCHANT,每个......

liujiduo
2015/02/13
558
6

没有更多内容

加载失败,请刷新页面

加载更多

Spring使用ThreadPoolTaskExecutor自定义线程池及实现异步调用

多线程一直是工作或面试过程中的高频知识点,今天给大家分享一下使用 ThreadPoolTaskExecutor 来自定义线程池和实现异步调用多线程。 一、ThreadPoolTaskExecutor 本文采用 Executors 的工厂...

CREATE_17
今天
5
0
CSS盒子模型

CSS盒子模型 组成: content --> padding --> border --> margin 像现实生活中的快递: 物品 --> 填充物 --> 包装盒 --> 盒子与盒子之间的间距 content :width、height组成的 内容区域 padd......

studywin
今天
7
0
修复Win10下开始菜单、设置等系统软件无法打开的问题

因为各种各样的原因导致系统文件丢失、损坏、被修改,而造成win10的开始菜单、设置等系统软件无法打开的情况,可以尝试如下方法解决 此方法只在部分情况下有效,但值得一试 用Windows键+R打开...

locbytes
昨天
8
0
jquery 添加和删除节点

本文转载于:专业的前端网站➺jquery 添加和删除节点 // 增加一个三和一节点function addPanel() { // var newPanel = $('.my-panel').clone(true) var newPanel = $(".triple-panel-con......

前端老手
昨天
8
0
一、Django基础

一、web框架分类和wsgiref模块使用介绍 web框架的本质 socket服务端 与 浏览器的通信 socket服务端功能划分: 负责与浏览器收发消息(socket通信) --> wsgiref/uWsgi/gunicorn... 根据用户访问...

ZeroBit
昨天
10
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部