文档章节

mysql递归问题

 阿狼仔
发布于 2016/12/10 16:59
字数 717
阅读 12
收藏 0

有个小坑:

    在创建function时 , 名字写错了, 这里应该是getPartList, 导致下面的sql运行不了, 这个小问题不注意还纠结了一下怎么回事。

---下面是原文:转至 云栖社区

最近遇到了一个问题,在mysql中如何完成节点下的所有节点或节点上的所有父节点的查询?
在Oracle中我们知道有一个Hierarchical Queries可以通过CONNECT BY来查询,但是,在MySQL中还没有对应的函数!!!

下面给出一个function来完成的方法

下面是sql脚本,想要运行的直接赋值粘贴进数据库即可。

创建表treenodes(可以根据需要进行更改)

1
2
3
4
5
6
7
8
9
10
-- ---------------------------- 
-- Table structure for `treenodes` 
-- ---------------------------- 
DROP TABLE IF EXISTS `treenodes`; 
CREATE TABLE `treenodes` ( 
  `id` int(11) NOT NULL, 
  `nodename` varchar(20) DEFAULT NULL, 
  `pid` int(11) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

插入几条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- ---------------------------- 
-- Records of treenodes 
-- ---------------------------- 
INSERT INTO `treenodes` VALUES ('1', 'A', '0'); 
INSERT INTO `treenodes` VALUES ('2', 'B', '1'); 
INSERT INTO `treenodes` VALUES ('3', 'C', '1'); 
INSERT INTO `treenodes` VALUES ('4', 'D', '2'); 
INSERT INTO `treenodes` VALUES ('5', 'E', '2'); 
INSERT INTO `treenodes` VALUES ('6', 'F', '3'); 
INSERT INTO `treenodes` VALUES ('7', 'G', '6'); 
INSERT INTO `treenodes` VALUES ('8', 'H', '0'); 
INSERT INTO `treenodes` VALUES ('9', 'I', '8'); 
INSERT INTO `treenodes` VALUES ('10', 'J', '8'); 
INSERT INTO `treenodes` VALUES ('11', 'K', '8'); 
INSERT INTO `treenodes` VALUES ('12', 'L', '9'); 
INSERT INTO `treenodes` VALUES ('13', 'M', '9'); 
INSERT INTO `treenodes` VALUES ('14', 'N', '12'); 
INSERT INTO `treenodes` VALUES ('15', 'O', '12'); 
INSERT INTO `treenodes` VALUES ('16', 'P', '15'); 
INSERT INTO `treenodes` VALUES ('17', 'Q', '15');

把下面的语句直接粘贴进命令行执行即可(注意修改传入的参数,默认rootId,表明默认treenodes)

根据传入id查询所有父节点的id

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
delimiter // 
CREATE FUNCTION `getParLst`(rootId INT)
RETURNS varchar(1000) 

BEGIN
	DECLARE sTemp VARCHAR(1000);
	DECLARE sTempPar VARCHAR(1000); 
	SET sTemp = ''; 
	SET sTempPar =rootId; 
	
	#循环递归
	WHILE sTempPar is not null DO 
		#判断是否是第一个,不加的话第一个会为空
		IF sTemp != '' THEN
			SET sTemp = concat(sTemp,',',sTempPar);
		ELSE
			SET sTemp = sTempPar;
		END IF;

		SET sTemp = concat(sTemp,',',sTempPar); 
		SELECT group_concat(pid) INTO sTempPar FROM treenodes where pid<>id and FIND_IN_SET(id,sTempPar)>0; 
	END WHILE; 
	
RETURN sTemp; 
END
//

执行命令

select * from treenodes where FIND_IN_SET(id,getParList(15));

结果:

id nodename pid
8 H 0
9 I 8
12 L 9
15 O 12

根据传入id查询所有子节点的id

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
delimiter // 
CREATE FUNCTION `getParLst`(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
//

执行命令

select * from treenodes where FIND_IN_SET(id,getChildList(7));

结果:

id nodename pid
7 G

本文转载自:https://yq.aliyun.com/articles/48885

共有 人打赏支持
粉丝 0
博文 50
码字总数 13158
作品 0
南京
私信 提问
Disqus 评论设计

众所周知,我上个月加入了Disqus公司。对我来说,这是一个很大(真的很大)的改变。他们那里有许多吸引人的问题,另外还有许多愿意一起解决他们的人。 我一直是MySQL的提倡者:不仅仅是因为他...

ifsc01
2013/03/14
4K
15
php调用mysql的递归型存储过程问题

在mysql里可以好好运行我的递归型存储过程 并设置了SET max_sp_recursion_depth=12; 这个可以拿到我想要的返回结果,是没有问题的。 但是在php里调用就报错了。 $result = mysql_query("CAL...

天南海北
2012/12/25
339
1
MySQL递归查找存储过程

依然是SQL问题,是上篇博客(http://my.oschina.net/u/1991646/blog/731996)的升级版需求(上篇是查询3层,此次是查询所有):无可避免的最终还是使用了MySQL存储过程。 先说明业务场景:查...

郁极风
2016/08/16
99
0
MySQL · 新特性分析 · CTE执行过程与实现原理

众所周知,Common table expression(CTE)是在大多数的关系型数据库里都存在的特性,包括ORACLE, SQLSERVER,POSTGRESQL等,唯独开源数据库老大MySQL缺失。CTE作为一个方便用户使用的功能,原本...

阿里云RDS-数据库内核组
2017/02/07
0
0
无限级分类实现思路 (组织树的分级管理)

关于该问题,暂时自己还没有深入研究,在网上找到几种解决方案,各有优缺点。 第一种方案: 使用递归算法,也是使用频率最多的,大部分开源程序也是这么处理,不过一般都只用到四级分类。这种...

bengozhong
2016/09/09
43
0

没有更多内容

加载失败,请刷新页面

加载更多

学习LinkedBlockingDeque源码

之前已经看了实现deque接口的ArrayDeque, ConcurrentLinkedDeque, LinkedList,也不能落下ConcurrentLinkedDeque,但是好像没在项目中用过。 这里看到实现的接口还有BlockingDeque<E>, Block...

woshixin
35分钟前
2
0
实战:用Python实现随机森林

摘要: 随机森林如何实现?为什么要用随机森林?看这篇足够了! 因为有Scikit-Learn这样的库,现在用Python实现任何机器学习算法都非常容易。实际上,我们现在不需要任何潜在的知识来了解模型...

阿里云官方博客
39分钟前
1
0
进程管理利器Supervisor--centos7下安装与配置

目录 概述 环境准备 检查python环境 在线安装 配置Supervisor 启动并验证 运维命令 概述 Supervisor的安装可以有在线安装和离线安装两种方式。安装方式取决于服务器是否联网,联网的话可采用...

java_龙
45分钟前
4
0
spring系列汇总

spring spring-基于可扩展Schema的特性自定义标签 springmvc-同一个访问资源,多种视图解析方式 spring cache 配置使用aspectj模式织入 基于注解的spring缓存,轻松无侵入解决cache问题 spri...

细肉云吞
46分钟前
4
0
vsftpd搭建ftp

12月10日任务 14.4 exportfs命令 14.5 NFS客户端问题 15.1 FTP介绍 15.2/15.3 使用vsftpd搭建ftp exportfs命令 在nfs运行后的一段时间内,如果需要添加共享目录,这时需要修改exports文件,并...

robertt15
48分钟前
6
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部