mysql递归查询

在做项目的时候碰到了可以用到递归查询的业务场景，又刚好在网上看到了一篇关于递归查询的文章，觉得有必要记录一下学习过程。

例如，在一个表里，多条数据存在父子级关系的，id为1的数据是id为15 16的父级数据，

CREATE TABLE `test_number` (
  `id` int NOT NULL AUTO_INCREMENT,
  `number` decimal(10,3) DEFAULT NULL,
  `pid` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

这些数据里是有一个主键id和一个父级id，父级id标记了这一条数据和哪一条是父子关系，通过pid可以延伸出多个层级的关系数据，所以当业务场景下需要查询到某个子级数据往下或者往上的所有数据时，就需要我们一级一级的往上或者往下查询。

写一个自定义函数来获取所有有对应关系的id，这里是向下递归查询的逻辑，

CREATE DEFINER=`root`@`localhost` FUNCTION `get_child_list`(in_id varchar(10)) RETURNS varchar(1000) CHARSET utf8mb4
    READS SQL DATA
begin 
 declare ids varchar(1000) default ''; 
 declare tempids varchar(1000); 

set tempids = in_id;
while tempids is not null do
set ids = CONCAT_WS(',',ids,tempids);
select GROUP_CONCAT(id) into tempids from test_number where FIND_IN_SET(pid,tempids)>0;
end while;
return ids;
end

定义一个ids来保存所有有对应关系的id，再声明一个tempids保存新查询到的有对应关系的id

使用while循环当判断条件tempids为空时不执行

CONCAT_WS("符号",str1,str2,....)函数可以把多个数据以指定的符号进行拼接，这里每次循环将原有的id和新查询到的id以逗号分隔进行拼接

而GROUP_CONCAT(columns)函数可以把查询到的所有id自动以逗号拼接，最终效果跟上面的CONCAT_WS("符号",str1,str2)函数是一样的

FIND_IN_SET(columns,strlist)函数可以找到指定字段值在给定字符串里的位置并获取，strlist必须是一串有逗号分隔的字符串

每次通过新查询到的id再次查询是否还有对应关系的id，如果有就拼接到ids中，没有的话tempids为空，停止循环

返回最终结果ids

这时候就可以通过自定义的函数获取到有对应关系的所有带逗号分隔的id

 使用下面的sql语句查询

select * from test_number where FIND_IN_SET(id,get_child_list("15"))

查询到父级id为15的包括其子级数据

 上面是向上递归的查询，这里是向下递归的查询

CREATE DEFINER=`root`@`localhost` FUNCTION `get_child_list`(in_id varchar(10)) RETURNS varchar(1000) CHARSET utf8mb4
    READS SQL DATA
begin 
 declare ids varchar(1000) default ''; 
 declare tempids varchar(1000); 

set tempids = in_id;
while tempids is not null do
set ids = CONCAT_WS(',',ids,tempids);
select pid into tempids from test_number where id = tempids;
end while;
return ids;
end