树查询，递归查询，子节点查询整理

最新推荐文章于 2022-09-17 18:56:48 发布

天海华兮

最新推荐文章于 2022-09-17 18:56:48 发布

阅读量1.4k

点赞数

分类专栏：数据库相关知识文章标签：树查询子节点查询递归查询

原文链接：https://blog.csdn.net/lilizhou2008/article/details/108505353

版权

数据库相关知识专栏收录该内容

35 篇文章 1 订阅

订阅专栏

看到的另一个函数

和下面的原理一样

https://www.cnblogs.com/chywx/p/9420726.html

create table `nodelist` (
    `id` int (11),
    `nodecontent` varchar (300),
    `pid` int (11)
); 

insert into `nodelist` (`id`, `nodecontent`, `pid`) values('1','a',NULL);
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('2','b','1');
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('3','c','1');
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('4','d','2');
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('5','e','3');
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('6','f','3');
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('7','g','5');
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('8','h','7');
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('9','i','8');
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('10','j','8');

DROP FUNCTION IF EXISTS `getChild`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `getChild`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8
BEGIN
        DECLARE ptemp VARCHAR(1000);
        DECLARE ctemp VARCHAR(1000);
               SET ptemp = '#';
               SET ctemp =CAST(rootId AS CHAR);
               WHILE ctemp IS NOT NULL DO
                 SET ptemp = CONCAT(ptemp,',',ctemp);
                SELECT GROUP_CONCAT(id) INTO ctemp FROM nodelist   
                WHERE FIND_IN_SET(pid,ctemp)>0; 
               END WHILE;  
               RETURN ptemp;  
    END$$
DELIMITER ;

SELECT * FROM nodelist WHERE FIND_IN_SET(id, getChild(3))

https://blog.csdn.net/lilizhou2008/article/details/108505353

在用的直接用SQL实现

https://www.cnblogs.com/rainydayfmb/p/8028868.html

select FIND_IN_SET('b','a,b,c,d');  --为2

-- 这三个 都会查询出来
select * from dept where FIND_IN_SET(id,'1000,1001,1002'); 

-- M_Y_S_Q_L
select CONCAT_WS('_','M','Y','S','Q','L') from dual; 

-- GROUP_CONCAT(id)列名，值为：1000,1001,1002
SELECT GROUP_CONCAT(id) FROM dept;

-- 输出5
SELECT IF(500<1000, 5, 10);

@num:=@num+1,:=是赋值的作用，所以，先执行@num+1,然后再赋值给@num，所以能正确实现行号的作用
num=@num+1,此时=是等于的作用，@num不等于@num+1，所以始终返回0，如果改为@num=@num,始终返回1了

-- @pids := CONCAT_WS(',', id) 这个函数呢？

SELECT id,org_name FROM (
	-- t3表 查询出了 id,name
	SELECT t1.id,t1.org_name, 
	--  isChild， 父Id，去pid中查询，如果查到就对pids赋值，否则就是0
	IF(FIND_IN_SET(org_parent_id, @pids), @pids := CONCAT(@pids, ',', id), 0) 
	
	AS ischild 
	
	-- ,@pids := CONCAT(@pids, ',', id) myset

	FROM 
	-- t1 就是查询id,name,parent_id, 并且分组，类似于全表查询
	( SELECT id,org_name,org_parent_id FROM vrv_org_tab t  ORDER BY org_parent_id, id ) t1, 
	
	-- t2表，有赋值对象pids
	(SELECT @pids := 1) t2 

) t3  

WHERE ischild != 0  可以加个 OR id=1


-- 里面的全表查询
 SELECT id,org_name,org_parent_id FROM vrv_org_tab t  ORDER BY org_parent_id, id
-- 这两个数据是需要的
id name 父id
2	2	1
3	3	2
-- 因为已经 排序过了，先执行 2，2的 父ID是1，刚好
FIND_IN_SET(org_parent_id, @pids)
-- 执行拼接。 1,2 。此时pids变成 1,2
@pids := CONCAT(@pids, ',', id)
-- 下次 ID，变成3， id 3的 父类是 2， 2 在PID 1,2 中
-- 下下次 pids变成 1,2,3
-- 但是 并没有符合的，符合的只有ID  2,3


-- 这种方法也行
SELECT
	GROUP_CONCAT( id ) 
	
FROM
	(
	SELECT
		t1.id,
	IF
		( FIND_IN_SET( parentid, @pids ) > 0, @pids := CONCAT( @pids, ',', id ), 0 ) AS ischild 
	FROM
		( SELECT id, parentid FROM AREA t WHERE t.LEVEL <= 3 ORDER BY parentid, id ) t1,
		( SELECT @pids := 820000 ) t2 
	) t3 
	
WHERE
	ischild != 0
	
-- 解释	
select id from (
              select t1.id,
							
              if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, 	            ',', id), 0) as ischild
							
              from (
                   select id,parent_id from menu t where t.status = 1 		               order by parent_id, id
                  ) t1,
									
                  (select @pids := 1) t2
									
             ) t3 where ischild != 0
             
1	0
11	1
12	1
13	1
111	11
121	12
122	12
1221	122
1222	122
12211	1222

-- 执行第一个from 后的SQL 会得到这样的值
-- 如id 11， 父ID是1, find_in_set(parent_id, @pids) > 0
-- 为真，
-- 在执行 @pids := concat(@pids,',', id)
-- @pids为 1,11  原来的1，在拼接上 id为11 的

-- 执行到id=12 的时候， 12父ID 为 1, 12 依然会拼接在后面
1	0
11	1,11
12	1,11,12
13	1,11,12,13
111	1,11,12,13,111
121	1,11,12,13,111,121
122	1,11,12,13,111,121,122

mysql树查询，很不错的函数实现

https://blog.csdn.net/qq_34997906/article/details/94007556

关于树结构

此类结构的数据，通常需要表结构中含有id 、parentId等自关联字段，有时为了提高查询效率还可增加更多冗余字段，如index，index的值为所有父级目录的id字符串集合。
关于树结构数据的组装，常见的写法是在程序中通过递归的方式去构建出一颗完整的树，单纯通过sql的方式其实并不常用，下面分别给出两种方式的例子。

MySQL自定义函数的方式

什么是MySQL自定义函数：

聚合函数，日期函数之类的都是MySQL的函数，此处我们定义的函数可同他们一样使用，不过只能在定义的数据库中使用，
自定义函数和存储过程类似，不同的是，函数只会返回一个值，不允许返回一个结果集。

2.1 创建测试数据

CREATE TABLE `tree`  (
  `id` bigint(11) NOT NULL,
  `pid` bigint(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `tree` VALUES (1, 0, '中国');
INSERT INTO `tree` VALUES (2, 1, '四川省');
INSERT INTO `tree` VALUES (3, 2, '成都市');
INSERT INTO `tree` VALUES (4, 3, '武侯区');
INSERT INTO `tree` VALUES (5, 4, '红牌楼');
INSERT INTO `tree` VALUES (6, 1, '广东省');
INSERT INTO `tree` VALUES (7, 1, '浙江省');
INSERT INTO `tree` VALUES (8, 6, '广州市');
————————————————
版权声明：本文为CSDN博主「弦上的梦」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_34997906/article/details/94007556

2.1 获取某节点下所有子节点

CREATE FUNCTION `GET_CHILD_NODE`(rootId varchar(100))   

RETURNS varchar(2000)  

BEGIN 
-- str
DECLARE str varchar(2000);  
-- cid
DECLARE cid varchar(100);   
-- 赋值
SET str = '$';   
-- cid 赋初值，为 rootId 参数的
SET cid = rootId;   
-- 如果cid 不为null，循环
WHILE cid is not null DO   
-- 第一次 $,参数
    SET str = concat(str, ',', cid);
    -- 取出ID，写入cid，条件为 pid，在 cid中 查询到的。
    SELECT group_concat(id) INTO cid FROM tree where FIND_IN_SET(pid, cid);   
END WHILE;   
RETURN str;   
END

	-- FIND_IN_SET(pid, '$,2')>0 更好

    -- 进入循环 就拼接了：$,2
    SET str = concat(str, ',', cid);
	-- 第一次 查询的为 3，3不为null,继续循环
    SELECT group_concat(id) FROM tree where FIND_IN_SET(pid, '$,2');   
    -- 第二次上来拼接是 str= $,2,3,cid为3，3用下面查询，得到的是4，
    -- SELECT group_concat(id) FROM tree where FIND_IN_SET(pid, '3')>0;
    -- 在把4拼接上
    
    -- 所以就构成了 返回：'2,3,4,5'
    -- 在用 FIND_IN_SET(id, '2,3,4,5'); 完美

调用自定义函数
select * from tree where FIND_IN_SET(id, GET_CHILD_NODE(2));

2.2 获取某节点的所有父节点

CREATE FUNCTION `GET_PARENT_NODE`(rootId varchar(100))   

RETURNS varchar(1000)   
BEGIN   

DECLARE fid varchar(100) default '';   
-- 默认为 4
DECLARE str varchar(1000) default rootId;   
-- 循环
WHILE rootId is not null do   
	-- fid 赋值， 第一步先 查询出了 3。 4的pid是3
    SET fid =(SELECT pid FROM tree WHERE id = rootId);  
    -- 如果 fid 不为null
    IF fid is not null THEN
    	-- str 拼接上 3。第一次为 4,3
        SET str = concat(str, ',', fid);   
        -- 设置 rootId = 3
        SET rootId = fid;   
    ELSE
    	-- 没有查到 rootId 为 fid，但是 fid并没有值呀
        SET rootId = fid;   
    END IF;   
END WHILE;   
return str;  
END

-- 第一次为：
select * from tree where FIND_IN_SET(id, "4,3")

调用自定义函数
select * from tree where FIND_IN_SET(id, GET_PARENT_NODE(4));

Oracle数据库的方式
只需要使用start with connect by prior语句即可完成递归的树查询，详情请自己查阅相关资料。
程序代码递归的方式构建树（此方式在项目中最常用，推荐）
这里我就不给出完整代码了，递归的方式很简单，就是先查出所有树节点，然后通过一个TreeNode类中的add方法递归把所有子节点给加进来。核心代码如下：

public class TreeNodeDTO {
private String id;
private String parentId;
private String name;
// 定义 孩子，孩子也是自己
private List<TreeNodeDTO> children = new ArrayList<>();

public void add(TreeNodeDTO node) {
    // 如果 父类 为0
    if ("0".equals(node.parentId)) {
        //加入到 这个节点
        this.children.add(node);
    } else if (node.parentId.equals(this.id)) {
        //如果 节点的 父类，和 ID 相同，就加入
        this.children.add(node);
    } else {
     	//递归调用add()添加子节点
        for (TreeNodeDTO tmp_node : children) {
            tmp_node.add(node);
        }
    }
}
}

参考文章：
https://blog.csdn.net/yangsen159/article/details/85164199
https://post.smzdm.com/p/amm0kekv/?send_by=2623461198
————————————————
版权声明：本文为CSDN博主「弦上的梦」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_34997906/article/details/94007556

自连接

SELECT t1.id,t1.nodecontent,t2.nodecontent parentnodecontent FROM nodelist t1
LEFT JOIN nodelist t2
ON t1.pid = t2.id

我自己的话

树查询：

1，oracle 使用 start with connect by prior 
下递归，包含自己：
select * from dept start with id='1001' connet by prior id=pid;
如改成上递归，只需要 prior后面互换：pid=id;
如向下递归，不包含自己只需 把id改成pid

2，mysql使用 普通sql 或 自对应函数

一，sql查1下的所有子节点
select id from (
              select t1.id,					
              if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids,',', id), 0) as ischild
              from (
                   select id,parent_id from menu t where t.status = 1 order by parent_id, id
                  ) t1,							
                  (select @pids := 1) t2							
             ) t3 where ischild != 0 
-- 如果包含子节点，sql后加入 or id=1

1，t2是@pids赋值，
2，t1是全表查，根据 父id和子id排序（目的是 使有序的循环）
如：t1 前的sql 会执行出这样的结果。第一列是id，第二类父id
1	0
11	1
12	1
111	11
3，find_in_set(parent_id, @pids) > 0 ，第一次执行 pids=1，id为1的 父id为0，0不再1中，执行为 假。
第二次 pids =1，id为 11的 父id为1，1在1中，执行为真。
4，使用 id为 11，执行下列，变成：
if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids,',', id), 0)
pids= 有原来的1，拼接成 1，11 （这次执行的id）
5， 执行到 id= 12，12的 父id为1，依然拼接到后面， ischild 的结果为
1	0
11	1,11
12	1,11,12
111	1,11,12,111
6，此时判断 ischild != 0 即可得到 子节点。

二，使用函数

CREATE FUNCTION `GET_CHILD_NODE`(rootId varchar(100))   
RETURNS varchar(2000)  

BEGIN 
DECLARE str varchar(2000);  
DECLARE cid varchar(100);   

SET str = '$';   
SET cid = rootId;   
WHILE cid is not null DO   
    SET str = concat(str, ',', cid);
    SELECT group_concat(id) INTO cid FROM tree where FIND_IN_SET(pid, cid);   
END WHILE;   

RETURN str;   
END

使用：select * from tree where FIND_IN_SET(id, GET_CHILD_NODE(2))
总体思路为：GET_CHILD_NODE 函数，得到 当前和 所有子节点
在 FIND_IN_SET(id, 当前 和 所有子节点)，所有需要的数据即取出来。
函数说明： 
1，str = '$' 初值，无意义， 最终返回的是 str
2，cid 先赋值 参数传递的，如例子是2
3，进入循环 str = concat(str, ',', cid); ，str先把 cid 的2 给拼接上
4，执行 SELECT group_concat(id) FROM tree where FIND_IN_SET(pid, '2')
得到：所有 父id是2 的数据，取id，写入 INTO cid
5，只要得到的不是null，循环继续，如此时得到的是3，cid为3
6，在执行str 拼接，str已经是 $,2 拼接3 为 $,2,3
7，此时在执行 SELECT FIND_IN_SET(pid, '3')，如4的pid是4, 4会筛序出来。 
8，str会继续拼接4，$,2,3,4 最终返回。
9，使用这个函数 FIND_IN_SET(id,'$,2,3,4') ，id是2,3,4 的都会查出来，2是自己输入的参数。

mysql 开启自定义函数

Mysql 从5.6升级到8.0，拷贝表结构、存储过程和函数，WTF? 表和存储过程都可以拷贝，函数不行，网上说在启动日志操作后对函数的操作需要设定参数值否则无法使用.

解决：

SET GLOBAL log_bin_trust_function_creators = 1;

重新导入即可。

使用简单的函数

/*
标题：SQL SERVER 2000中查询指定节点及其所有父节点的函数(表格形式显示)
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：2008-05-12
地点：广东深圳
*/

create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null  , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go

--查询指定节点及其所有父节点的函数
create function f_pid(@id varchar(3)) returns @t_level table(id varchar(3))
as
begin
  insert into @t_level select @id
  select @id = pid from tb where id = @id and pid is not null
  while @@ROWCOUNT > 0
  begin
    insert into @t_level select @id 
    select @id = pid from tb where id = @id and pid is not null
  end
  return
end
go

--调用函数查询002(广州市)及其所有父节点
select a.* from tb a , f_pid('002') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
002  001  广州市

（所影响的行数为 2 行）
*/

--调用函数查询003(深圳市)及其所有父节点
select a.* from tb a , f_pid('003') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
003  001  深圳市

（所影响的行数为 2 行）
*/

--调用函数查询008(西乡镇)及其所有父节点
select a.* from tb a , f_pid('008') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
003  001  深圳市
007  003  宝安区
008  007  西乡镇

（所影响的行数为 4 行）
*/

drop table tb
drop function f_pid

oracle

Oracle 自带有递归查询的功能
Oracle 递归查询，
- start with connect by prior 用法
find_in_set 函数
concat，concat_ws，group_concat 函数
MySQL 自定义函数
手动实现 MySQL 递归查询

Oracle 递归查询

 start with connect by prior

prior 关键字在子节点端还是父节点端，以及是否包含当前查询的节点，
共分为四种情况。

prior 在子节点端（向下递归）

第一种情况：

start with 子节点id = ’ 查询节点 ’ connect by prior 子节点id = 父节点id

select * from dept start with id='1001' connet by prior id=pid;

这里，按照条件 id=‘1001’ 对当前节点以及它的子节点递归查询。查询结果包含自己及所有子节点。

第二种情况：

start with 父节点id= ’ 查询节点 ’ connect by prior 子节点id = 父节点 id

select * from dept start with pid='1001' connect by prior id=pid;

这里，按照条件 pid=‘1001’ 对当前节点的所有子节点递归查询。查询结果只包含它的所有子节点，不包含自己。

prior 在父节点端（向上递归）

第三种情况：

start with 子节点id= ’ 查询节点 ’ connect by prior 父节点id = 子节点id

select * from dept start with id='1001' connect by prior pid=id;

这里按照条件 id=‘1001’ ，对当前节点及其父节点递归查询。查询结果包括自己及其所有父节点。

第四种情况：

start with 父节点id= ’ 查询节点 ’ connect by prior 父节点id = 子节点id

select * from dept start with pid='1001' connect by prior pid=id;

这里按照条件 pid=‘1001’，对当前节点的第一代子节点以及它的父节点递归查询。查询结果包括自己的第一代子节点以及所有父节点。（包括自己）

其实这种情况也好理解，因为查询开始条件是以 父节点为根节点，且向上递归，自然需要把当前父节点的第一层子节点包括在内。

以上四种情况初看可能会让人迷惑，容易记混乱，其实不然。

我们只需要记住 prior 的位置在子节点端，就向下递归，在父节点端就向上递归。

开始条件若是子节点的话，自然包括它本身的节点。
开始条件若是父节点的话，则向下递归时，自然不包括当前节点。而向上递归，需要包括当前节点及其第一代子节点。

MySQL 递归查询

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `pid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
 
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1000', '总公司', NULL);
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1001', '北京分公司', '1000');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1002', '上海分公司', '1000');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1003', '北京研发部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1004', '北京财务部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1005', '北京市场部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1006', '北京研发一部', '1003');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1007', '北京研发二部', '1003');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1008', '北京研发一部一小组', '1006');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1009', '北京研发一部二小组', '1006');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1010', '北京研发二部一小组', '1007');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1011', '北京研发二部二小组', '1007');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1012', '北京市场一部', '1005');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1013', '上海研发部', '1002');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1014', '上海研发一部', '1013');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1015', '上海研发二部', '1013');

find_in_set 函数

函数语法：find_in_set(str,strlist)

str 代表要查询的字符串， strlist 是一个以逗号分隔的字符串，如 (‘a,b,c’)。

此函数用于查找 str 字符串在字符串 strlist 中的位置，返回结果为 1 ~ n 。若没有找到，则返回0。

举个栗子：

select FIND_IN_SET('b','a,b,c,d');

结果返回 2 。因为 b 所在位置为第二个子串位置。

此外，在对表数据进行查询时，它还有一种用法，如下：

：

select * from dept where FIND_IN_SET(id,'1000,1001,1002');

结果返回所有 id 在 strlist 中的记录，即 id = ‘1000’ ，id = ‘1001’ ，id = ‘1002’ 三条记录。

以向下递归查询所有子节点为例。我想，是不是可以

找到一个包含当前节点和所有子节点的以逗号拼接的字符串 strlist，
传进 find_in_set 函数。就可以查询出所有需要的递归数据了。

那么，现在问题就转化为怎样构造这样的一个字符串 strlist 。

这就需要用到以下字符串拼接函数了。

concat，concat_ws，group_concat 函数

一、字符串拼接函数中，最基本的就是 concat 了。它用于连接N个字符串，如，

select CONCAT('M','Y','S','Q','L') from dual;

结果为 ‘MYSQL’ 字符串。

二、concat 是以逗号为默认的分隔符，而 concat_ws 则可以指定分隔符，第一个参数传入分隔符，如以下划线分隔。

三、group_concat 函数更强大，可以分组的同时，把字段以特定分隔符拼接成字符串。

用法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )

可以看到有可选参数，可以对将要拼接的字段值去重，也可以排序，指定分隔符。若没有指定，默认以逗号分隔。

对于 dept 表，我们可以把表中的所有 id 以逗号拼接。（这里没有用到 group by 分组字段，则可以认为只有一组）

MySQL 自定义函数，实现递归查询

可以发现以上已经把字符串拼接的问题也解决了。

那么，问题就变成怎样构造有递归关系的字符串了。

我们可以自定义一个函数，通过传入根节点id，找到它的所有子节点。

以向下递归为例。 （讲解自定义函数写法的同时，讲解递归逻辑）

delimiter $$ 
-- 如果存在函数，就删除
drop function if exists get_child_list$$ 

-- 创建一个函数，返回的是 varchar 1000，参数是：in_id
-- 并且参数传入一个根节点的子节点id
create function get_child_list(in_id varchar(10)) returns varchar(1000) 

-- 开始
begin 

 -- 定义 ids varchar 1000
 declare ids varchar(1000) default ''; 
 -- 定义 tempids
 declare tempids varchar(1000); 
 
 -- tempids 重新赋值，默认为根节点
 set tempids = in_id; 
 
 -- 循环 tempids 当它 不为null
 while tempids is not null do 
 
  -- ids 和 tempids 拼接在一起。  1,2,3 重复也拼接
  set ids = CONCAT_WS(',',ids,tempids); 
  
  -- 查ID 拼接的， 来自 tempids 表：dept，条件为：
  -- pid在tempids查找，并且>0 并且查到了。
  select GROUP_CONCAT(id) into tempids from dept where FIND_IN_SET(pid,tempids)>0;  
  
 -- 结束循环 
 end while; 
 -- 返回ids
 return ids; 
-- 结束
end  

$$ 
delimiter ; 


然后以 FIND_IN_SET(pid,tempids)>0 为条件，
遍历在 tempids 中的所有 pid ，寻找以此为父节点的所有子节点 id ，
并且通过 GROUP_CONCAT(id) into tempids 
把这些子节点 id 都用逗号拼接起来，并覆盖更新 tempids 。

（1） delimiter

用于定义结束符。我们知道MySQL默认的结束符为分号，表明指令结束并执行。
但是在函数体中，有时我们希望遇到分号不结束，因此需要暂时把结束符改为一个随意的其他值。我这里设置为

，意思是遇到 $$ 才结束，并执行当前语句。

（2）drop function if exists get_child_list

。若函数getchildlist已经存在了，则先删除它。注意这里需要用当前自定义的结束符。

来结束并执行语句。因为，这里需要数和下边的函体单独区分开来执行。

（3）create function get_child_list 创建函数。并且参数传入一个根节点的子节点id，需要注意一定要注明参数的类型和长度，如这里是 varchar(10)。returns varchar(1000) 用来定义返回值参数类型。

（4）begin 和 end 中间包围的就是函数体。用来写具体的逻辑。

（5）declare 用来声明变量，并且可以用 default 设置默认值。

这里定义的 ids 即作为整个函数的返回值，是用来拼接成最终我们需要的以逗号分隔的递归串的。

而 tempids 是为了记录下边 while 循环中临时生成的所有子节点以逗号拼接成的字符串。

（6） set 用来给变量赋值。此处把传进来的根节点赋值给 tempids 。

（7） while do … end while; 循环语句，循环逻辑包含在内。注意，end while 末尾需要加上分号。

循环体内，先用 CONCAT_WS 函数把最终结果 ids 和临时生成的 tempids 用逗号拼接起来。

然后以 FIND_IN_SET(pid,tempids)>0 为条件，遍历在 tempids 中的所有 pid ，寻找以此为父节点的所有子节点 id ，并且通过 GROUP_CONCAT(id) into tempids 把这些子节点 id 都用逗号拼接起来，并覆盖更新 tempids 。

等下次循环进来时，就会再次拼接 ids ，并再次查找所有子节点的所有子节点。循环往复，一层一层的向下递归遍历子节点。直到判断 tempids 为空，说明所有子节点都已经遍历完了，就结束整个循环。

这里，用 ‘1000’ 来举例，即是：（参看图1的表数据关系）

第一次循环：



  tempids=1000 ids=1000 tempids=1001,1002 (1000的所有子节点)



第二次循环：



  tempids=1001,1002  ids=1000,1001,1002  tempids=1003,1004,1005,1013 (1001和1002的所有子节点)



第三次循环：



  tempids=1003,1004,1005,1013 



  ids=1000,1001,1002,1003,1004,1005,1013 



  tempids=1003和1004和1005及1013的所有子节点



...



最后一次循环，因找不到子节点，tempids=null，就结束循环。

（8）return ids; 用于把 ids 作为函数返回值返回。

（9）函数体结束以后，记得用结束符 $$ 来结束整个逻辑，并执行。

（10）最后别忘了，把结束符重新设置为默认的结束符分号。

自定义函数做好之后，我们就可以用它来递归查询我们需要的数据了。如，我查询北京研发部的所有子节点。

以上是向下递归查询所有子节点的，并且包括了当前节点，也可以修改逻辑为不包含当前节点，我就不演示了。

手动实现递归查询（向上递归）

相对于向下递归来说，向上递归比较简单。

因为向下递归时，每一层递归一个父节点都对应多个子节点。

而向上递归时，每一层递归一个子节点只对应一个父节点，关系比较单一。

同样的，我们可以定义一个函数 get_parent_list 来获取根节点的所有父节点。


delimiter $$ 
drop function if exists get_parent_list$$ 

create function get_parent_list(in_id varchar(10)) returns varchar(1000) 

begin 
 declare ids varchar(1000); 
 declare tempid varchar(10); 
  
-- 参数ID = tempId
 set tempid = in_id; 

-- 循环 tempid 不为null
 while tempid is not null do 

  -- ids 等于 ids 原来的值 拼接到 tempid
  set ids = CONCAT_WS(',',ids,tempid); 

  -- 查询pid，  条件为 id = tempid，第一次查询出参数的pid，第二次查询出 pid的pid
  select pid into tempid from dept where id=tempid; 
 end while; 
 
 --返回 ids
 return ids; 
end 
$$ 
delimiter ;

查找北京研发二部一小组，以及它的递归父节点，如下：

注意事项

我们用到了 group_concat 函数来拼接字符串。但是，需要注意它是有长度限制的，默认为 1024 字节。可以通过 show variables like "group_concat_max_len"; 来查看。

注意，单位是字节，不是字符。在 MySQL 中，单个字母占1个字节，而我们平时用的 utf-8下，一个汉字占3个字节。

这个对于递归查询还是非常致命的。因为一般递归的话，关系层级都比较深，很有可能超过最大长度。（尽管一般拼接的都是数字字符串，即单字节）

所以，我们有两种方法解决这个问题：

修改 MySQL 配置文件 my.cnf ，增加 group_concat_max_len = 102400 #你要的最大长度 。
执行以下任意一个语句。SET GLOBAL group_concat_max_len=102400; 或者 SET SESSION group_concat_max_len=102400;

他们的区别在于，global是全局的，任意打开一个新的会话都会生效，但是注意，已经打开的当前会话并不会生效。而 session 是只会在当前会话生效，其他会话不生效。

共同点是，它们都会在 MySQL 重启之后失效，以配置文件中的配置为准。所以，建议直接修改配置文件。102400 的长度一般也够用了。假设一个id的长度为10个字节，也能拼上一万个id了。

除此之外，使用 group_concat 函数还有一个限制，就是不能同时使用 limit 。如，

本来只想查5条数据来拼接，现在不生效了。

不过，如果需要的话，可以通过子查询来实现，

若本文对你有用，欢迎关注我，给我点赞吧 ~

天海华兮

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
树查询，递归查询，子节点查询整理

看到的另一个函数和下面的原理一样https://www.cnblogs.com/chywx/p/9420726.htmlcreate table `nodelist` ( `id` int (11), `nodecontent` varchar (300), `pid` int (11)); insert into `nodelist` (`id`, `nodecontent`, `pid`) values('1','a',NULL);insert into `
复制链接

扫一扫