Mysql 递归查询

Mysql 递归查询

对于数据库中的树形结构数据，如部门表，有时候，我们需要知道某部门的所有下属部分或者某部分的所有上级部门，这时候就需要用到数据的递归查询！

1. oracle实现递归查询的话，就可以使用START WITH … CONNECT BY函数语法从而实现我们的效果！
2. Mysql并没有提供类似函数，所以只能通过自定义函数实现！

那么在Mysql中是如何实现递归操作的呢？前提了解一下相关的函数语法！

FIND_IN_SET 函数的语法解释

基本语法结构

FIND_IN_SET(str,strlist)

参数解释

1. str:要查询的字符串
2. strList:被查询的字符串,格式是:(2,3,5,6,7)

匹配解释

str字段匹配strList中的字符串,匹配到:1,无匹配到:0

1.匹配到案例

SELECT FIND_IN_SET('a','a,b,c,d,e,f,g,h,i')  RESULT FROM DUAL;

匹配结果：

2.无匹配到案例

SELECT FIND_IN_SET('aa','a,b,c,d,e,f,g,h,i')  RESULT FROM DUAL;

匹配结果：

简单应用

当前表名是：recursion ，数据如下图所示，进行简单的函数应用操作
需求：
获取当前parent_id=1或2的返回值id！

SELECT group_concat(id)  FROM recursion where FIND_IN_SET(parent_id,'1,2');

匹配结果：

通过函数的简单应用，符合我们预期的期望值！

递归函数实现

前面我们了解了FIND_IN_SET函数的基本语法，接下来我们简单应用此函数！

案例:
通过父级id获取当前父级id的全部子级id!

基础表结构

CREATE TABLE `recursion` (
  `id` int DEFAULT NULL COMMENT '当前id',
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '节点名称',
  `parent_id` int DEFAULT NULL COMMENT '父级id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表数据

INSERT INTO `recursion`(`id`, `name`, `parent_id`) VALUES (1, '数据中心平台', 0);
INSERT INTO `recursion`(`id`, `name`, `parent_id`) VALUES (2, '系统信息维护', 1);
INSERT INTO `recursion`(`id`, `name`, `parent_id`) VALUES (3, '用户管理', 2);
INSERT INTO `recursion`(`id`, `name`, `parent_id`) VALUES (4, '系统业务监控', 1);
INSERT INTO `recursion`(`id`, `name`, `parent_id`) VALUES (5, '菜单管理', 2);
INSERT INTO `recursion`(`id`, `name`, `parent_id`) VALUES (6, '角色管理', 2);
INSERT INTO `recursion`(`id`, `name`, `parent_id`) VALUES (7, '新增用户', 3);

表数据逻辑

存储函数书写

DROP FUNCTION IF EXISTS queryChildrenAreaInfo;

CREATE FUNCTION queryChildrenAreaInfo(pid VARCHAR(50))
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
DECLARE num int;

SET num = 1;
SET sTemp = '';
SET sTempChd = pid;
 
WHILE sTempChd IS NOT NULL DO
   IF num = 1 then 
      SET sTemp = CONCAT(sTemp,'',sTempChd);
   ELSE 
      SET sTemp = CONCAT(sTemp,',',sTempChd);
   END IF;
   set num = num +1;
   SELECT group_concat(id) INTO sTempChd FROM recursion where FIND_IN_SET(parent_id,sTempChd)>0;
END WHILE;
return sTemp;
END;

测试存储函数

select queryChildrenAreaInfo('2');

查看结果：

符合我们期望的递归获取当前id下的全部菜单id!

灵活应用

以上我们通过存储函数获取当前父级id下的全部子级id结果集，针对此结果集灵活应用！上述返回结果是：2,3,5,6,7

FIND_IN_SET函数获取数据

1. 具体sql

SELECT * FROM recursion where FIND_IN_SET(id,'2,3,5,6,7');

2. 匹配结果
   

IN获取数据

需求：
将结果集通过逗号分开，放在IN的结果集中查询即可实现我们的需求！

利用help_topic表

help_topic表就是以字符拆分，一行转多行，最终实现行转列功能！

具体sql语句：

 SELECT
    substring_index(substring_index(('2,3,5,6,7'),',', b.help_topic_id + 1), ',', -1) result
FROM
 mysql.help_topic b
where
 b.help_topic_id < (LENGTH(('2,3,5,6,7')) - LENGTH(REPLACE(('2,3,5,6,7'), ',', '')) + 1);

行转列结果：

将以上的sql修改一下，即可成为你自己的脚本！

具体应用

1. 具体sql

 SELECT * FROM recursion where id IN (
  SELECT
    substring_index(substring_index(('2,3,5,6,7'),',', b.help_topic_id + 1), ',', -1) result
FROM
 mysql.help_topic b
where
 b.help_topic_id < (LENGTH(('2,3,5,6,7')) - LENGTH(REPLACE(('2,3,5,6,7'), ',', '')) + 1)
 );

2. 匹配结果
   
   以上俩中方法就是对于FIND_IN_SET函数的结果集的具体应用在实际sql脚本中的俩种解决办法！

若有不足之处，请您指正！