Mysql 递归查询
Mysql 递归查询
对于数据库中的树形结构数据,如部门表,有时候,我们需要知道某部门的所有下属部分或者某部分的所有上级部门,这时候就需要用到数据的递归查询!
- oracle实现递归查询的话,就可以使用START WITH … CONNECT BY函数语法从而实现我们的效果!
- Mysql并没有提供类似函数,所以只能通过自定义函数实现!
那么在Mysql中是如何实现递归操作的呢?前提了解一下相关的函数语法!
FIND_IN_SET 函数的语法解释
基本语法结构
FIND_IN_SET(str,strlist)
参数解释
- str:要查询的字符串
- 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函数获取数据
- 具体sql
SELECT * FROM recursion where FIND_IN_SET(id,'2,3,5,6,7');
- 匹配结果
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修改一下,即可成为你自己的脚本!
具体应用
- 具体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)
);
- 匹配结果
以上俩中方法就是对于FIND_IN_SET函数的结果集的具体应用在实际sql脚本中的俩种解决办法!
若有不足之处,请您指正!