文档章节

MySQL递归查找存储过程

郁极风
 郁极风
发布于 2016/08/16 16:36
字数 867
阅读 131
收藏 1
点赞 1
评论 0

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

先说明业务场景:查询出某个节点中的所有下属节点。

表结构和数据为:

id                        superior_id               
57adca6415a414043183326f                            
57adcbb915a4140431833277  57adca6415a414043183326f  
57aef2c115a48464e09f45cb  57adcbb915a4140431833277  
57af13a315a48464e09f45df  57aef2c115a48464e09f45cb  
57af541a15a44d6d4e5a54cf                            
57b014a515a44d6d4e5a54d1                            
57b13b2b15a4535f619d4a13                            
57b1f31f15a49fa67174a623                            
57b28e4e15a49fa67174a627                            
test2                     57adcbb915a4140431833277  
test3                     57adca6415a414043183326f  

其中id为主键,superior_id为上级的id。

除去没用的数据,关系如下:

57adca6415a414043183326f
   |-57adcbb915a4140431833277
     |-57aef2c115a48464e09f45cb
        |-57af13a315a48464e09f45df
     |-test2
   |-test3

实现业务场景,和以前一样,也可以使用Java代码实现,一个考虑到性能,另一个考虑到应用服务器的压力,此次使用存储过程实现。

存储过程实现思路:

1. 需要存在一个循环递归条件(由于存在递归,这里通过返回结果来定义)

2. 递归时实时获取新id作为新查询条件(需要使用到游标)

3. 使用临时表存储数据,临时表通过存储过程删除和建立(需要存在两个存储过程,一个负责数据的递归,另一个负责临时表的建立和调用数据递归的存储过程)

思路搞定,开干!!

1. 定义数据递归存储过程

-- 定义数据递归存储过程:selectChildDisList
DELIMITER $$ -- MySQL分隔符定义,默认为; 如果默认为; 在创建存储过程的时候会出错,这里应该成对存在
CREATE PROCEDURE selectChildDisList(IN id VARCHAR(1000))
BEGIN
  DECLARE cache_id VARCHAR(1000) DEFAULT '';
  DECLARE done INTEGER DEFAULT 0;
  DECLARE cursor_dis CURSOR FOR SELECT d.id
                           FROM
                             t_distribut d
                           WHERE
                             d.superior_id = id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; -- 含义是:若没有数据返回,程序继续,并将变量done设为1
  SET @@max_sp_recursion_depth = 10;  
  INSERT INTO tmp_dis_table VALUES (id); -- 插入数据到临时表,其中临时表tmp_dis_table为存储过程selectDisList中定义 
  OPEN cursor_dis; -- 打开游标 
  FETCH cursor_dis INTO cache_id; -- 获取游标当前记录并赋值给 cache_id 变量(FETCH 获取游标当前指针的记录)
  WHILE (done =0)
  DO
    CALL selectChildDisList(cache_id); -- 递归调用自己进行查询
    FETCH cursor_dis INTO cache_id;
  END WHILE;
  CLOSE cursor_dis; -- 关闭游标
END$$ -- 这个符号一定要写这里,不然就会出语法错误,具体问题还不知道
DELIMITER ;

2. 定义调用数据递归存储过程的存储过程

-- 定义调用数据递归存储过程的存储过程:selectDisList
DELIMITER $$
CREATE PROCEDURE selectDisList(IN id VARCHAR(1000))
BEGIN
  -- 这里通过临时表来获取得到的结果集数据
  DROP TEMPORARY TABLE IF EXISTS tmp_dis_table; 
  CREATE TEMPORARY TABLE tmp_dis_table(
	  disId VARCHAR(1000)
	);
  DELETE FROM tmp_dis_table;
  CALL selectChildDisList(id);
  SELECT DISTINCT disId
FROM
  tmp_dis_table ORDER BY disId;
END$$ 
DELIMITER ;

3. 存储过程的调用

CALL selectDisList('id');

数据测试:

根据上述的数据进行测试,首先测试查找id为'57adca6415a414043183326f'下的数据,结果为:

再测试查找id为'57adca6415a414043183326f'下的数据,结果为:

经过对照,测试结果没有问题,到此为止,一切OK。

仍然存在不理解的地方:

1. DELIMITER 中定义分隔符一定要写在  END后

2. SET @@max_sp_recursion_depth = 10; 此处的含义不是很理解。如果没有还会抛出

参考博文:http://blog.csdn.net/u012501459/article/details/12945267

                   http://www.cnblogs.com/sk-net/archive/2011/09/07/2170224.html

纯手打,欢迎拍砖,也希望大哥大姐们能解决我上述不理解的疑惑,感谢~

转载请指明出处:http://my.oschina.net/u/1991646/blog/733531

© 著作权归作者所有

共有 人打赏支持
郁极风
粉丝 8
博文 30
码字总数 8787
作品 0
长沙
程序员
使用mysql 游标,快速删除子节点及其附属节点

上篇,我写了如何使用html展示数据库中存储的树形结构, 本篇我将说一下如何通过存储过程,快速删除一个树节点及其附属节点。网上已经有非常多的关于mysql 游标使用的文章了, 为什么我还要写...

AdaAda
2017/06/06
0
0
MySQL · 新特性分析 · CTE执行过程与实现原理

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

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

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

bengozhong
2016/09/09
43
0
SQL的递归查找操作

在关系型数据库中,经常会设计一种上级下级关系数据,即数据中保存这上级的ID。如下所示: 数据库一般设计为id和superior_id(父级id)的设计方式,举例如下,数据中的结构数据如下所示: 此...

郁极风
2016/08/12
57
2
二分搜寻法 ( 搜寻原则的代表)

二分搜寻法 ( 搜寻原则的代表) 1.二分查找又称折半查找,它是一种效率较高的查找方法。 2.二分查找要求:(1)必须采用顺序存储结构 (2).必须按关键字大小有序排列 3.原理:将数组分为三...

郑加威
2017/04/18
0
0
算法-数据结构

时间复杂度 O(log n) 意味着什么? 写给小白的时间复杂度指南 查找算法的 Java 实现 查找算法的 Java 实现 两个有序数组合并成一个有序数组 用拉链法和线性探测法解决哈希冲突 用拉链法和线性...

掘金官方
2017/12/14
0
0
菜鸡吃米之链表(未更新完)

链表反转感觉说不出什么算法来,反转就是需要仔细分析各种情况,仔细检查各个部分以避免出现bug就可以了。 下面是两个反转链表的,一个是整体反转,一个是反转第m到n个。 链表问题还有许多其...

跑得比谁都慢
2017/11/13
0
0
数据结构(python)

数据结构学会更有思路,效率,节约开销 算法实现的语言并不重要,重要的是思想,算法是独立存在的一种解决问题的方法和思想 算法的五大特性 输入: 算法具有0个或多个输入 输出: 算法至少有1...

sinat_23880167
2017/10/31
0
0
hibernate调用mysql存储过程

 在mysql中创建两个存储过程,如下: 1、根据id查找某条数据: 1 CREATE PROCEDURE (IN id INTEGER(11))2 begin3 select * from emp where empId=id;4 end; 2、根据id查找某个字段,并返回 ...

开源中国-首席码农
2016/03/22
2
0
Oracle to Mysql

这几天领导要求将应用从oracle搬到mysql,倒腾一阵子,数据本身除了blob和number有些问题外,其他都能正常迁移。倒是函数存储过程是个大难题,研究了一下,感觉都是要重写的样子,但是这样工...

厦门王五
2014/05/30
502
6

没有更多内容

加载失败,请刷新页面

加载更多

下一页

回想过往,分析当下,着眼未来

好久没有真正的在纸质笔记本上写过东西了,感觉都快不会写字了,笔画都不知道怎么写了。接下来就说说咱们的正事。 2018年7月22日,我做了一个决定,那就是去参加安全培训(可能是我职业生涯中...

yeahlife
7分钟前
0
0
关于工作中的人际交往

关于工作中的人际交往 Intro 写了篇发泄情绪的博客,但不会发布出来。 大概就是,要么忍,要么滚。 以及一些不那么符合社会主义核心价值观,不满于大资本家与小资本家剥削的废话。

uniqptr
13分钟前
0
0
springMVC的流程

1.用户发送请求至前端控制器DispatcherServlet 2.DispatcherServlet收到请求调用HandlerMapping处理器映射器。 3.处理器映射器根据请求url找到具体的处理器,生成处理器对象及处理器拦截器(...

JavaSon712
28分钟前
0
0
大数据教程(3.2):Linux系统软件安装之自动化脚本

博主前面文章有介绍过软件的安装,可以帮助IT人员顺利的完成功能软件安装;但是,对于我们运维人员或者需要管理软件安装的项目经理来说,有些应用一次行需要搭建很多台相同的软件环境(如tom...

em_aaron
46分钟前
0
0
Spring Boot 2.0.3 JDBC整合Oracle 12

整合步骤 1. Oracle驱动引入 Oracle驱动一般不能通过maven仓库直接下载得到,需自行下载并导入到项目的lib目录下,建议通过如下pom依赖引入下载的Oracle驱动 <!-- Oracle 驱动 -->...

OSC_fly
55分钟前
0
0
java 8 并行流 - 1

下面创建一个并行流,与顺序流 //顺序流Stream.iterate(0L, i -> i + 1) .limit(Integer.MAX_VALUE) .reduce(0L, Long::sum);//并行流Stream.iterate(0L, i -> i......

Canaan_
今天
0
0
数据结构与算法5

二分法采用向下取整的方法 使用有序数组的好处是查找的速度比无序数组快的多,不好的方面是因为要将所有靠后的数据移开,所以速度较慢,有序数组和无序数组的删除操作都很慢。 有序数组在查找...

沉迷于编程的小菜菜
昨天
1
1
SpringBoot | 第十一章:Redis的集成和简单使用

前言 上几节讲了利用Mybatis-Plus这个第三方的ORM框架进行数据库访问,在实际工作中,在存储一些非结构化或者缓存一些临时数据及热点数据时,一般上都会用上mongodb和redis进行这方面的需求。...

oKong
昨天
5
0
对基于深度神经网络的Auto Encoder用于异常检测的一些思考

一、前言 现实中,大部分数据都是无标签的,人和动物多数情况下都是通过无监督学习获取概念,故而无监督学习拥有广阔的业务场景。举几个场景:网络流量是正常流量还是攻击流量、视频中的人的...

冷血狂魔
昨天
0
0
并发设计之A系统调用B系统

A-->B A在发送请求之前,用乐观锁,减少对B的重复调用,这样一定程度上是幂等性。 比如A系统支付功能,要调用B系统进行支付操作,但是前端对"支付"按钮不进行控制,即用户会不断多次点击支付...

汉斯-冯-拉特
昨天
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部