MySQL 递归查询,创建一个存储过程实现,查询子节点，父节点只需要调用这个存储过程即可。

写在前面

众所周知，java中递归查询，需要和数据库进行多次交互，不论是向上查询还是向下查询，所以不如进行一次交互就完成查询。据我了解，Oracle实现递归查询非常的方便，但mysql不行，需要自定义函数来完成。

创建表(Dept)

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `pid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1000', '总公司', NULL);
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1001', '北京分公司', '1000');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1002', '上海分公司', '1000');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1003', '北京研发部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1004', '北京财务部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1005', '北京市场部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1006', '北京研发一部', '1003');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1007', '北京研发二部', '1003');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1008', '北京研发一部一小组', '1006');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1009', '北京研发一部二小组', '1006');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1010', '北京研发二部一小组', '1007');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1011', '北京研发二部二小组', '1007');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1012', '北京市场一部', '1005');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1013', '上海研发部', '1002');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1014', '上海研发一部', '1013');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1015', '上海研发二部', '1013');

创建完成后入下图

首先熟悉一下，mysql find_in_set函数。

find_in_set 函数

函数语法：find_in_set(str,strlist)

str 代表要查询的字符串， strlist 是一个以逗号分隔的字符串，如 (‘a,b,c’)。
此函数用于查找 str 字符串在字符串 strlist 中的位置，返回结果为 1 ~ n 。若没有找到，则返回0。

举个例子：

select FIND_IN_SET('b','a,b,c,d');

此外，在对表数据进行查询时，它还有一种用法，如下：

select * from dept where FIND_IN_SET(id,'1000,1001,1002');

以向下递归查询所有子节点为例,可以找到一个包含当前节点和所有子节点的以逗号拼接的字符串 strlist，传进 find_in_set 函数。就可以查询出所有需要的递归数据了。

那么，现在问题就转化为怎样构造这样的一个字符串 strlist。

### concat concat_ws group_concat函数

一、字符串拼接函数中，最基本的就是 concat 了。它用于连接N个字符串，如，

select CONCAT('M','Y','S','Q','L') from dual;

结果为 ‘MYSQL’ 字符串。

二、concat 是以逗号为默认的分隔符，而 concat_ws 则可以指定分隔符，第一个参数传入分隔符，如以下划线分隔。

select concat_ws('_','M','Y','S','Q','L') from dual;

三、group_concat 函数更强大，可以分组的同时，把字段以特定分隔符拼接成字符串。

用法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )

可以看到有可选参数，可以对将要拼接的字段值去重，也可以排序，指定分隔符。若没有指定，默认以逗号分隔。

对于 dept 表，我们可以把表中的所有 id 以逗号拼接。（这里没有用到 group by 分组字段，则可以认为只有一组）

select group_concat(id) from dept;

MySQL 自定义函数实现递归查询

可以发现以上已经把字符串拼接的问题也解决了。那么，问题就变成怎样构造有递归关系的字符串了。

我们可以自定义一个函数，通过传入根节点id，找到它的所有子节点。

向下递归。

创建自定义函数

create function get_child_list(in_id varchar(10)) returns varchar(1000) 
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 dept where FIND_IN_SET(pid,tempids)>0;
end while;
return ids;
end

create function get_child_list 创建函数。并且参数传入一个根节点的子节点id，需要注意一定要注明参数的类型和长度，如这里是 varchar(10)。returns varchar(1000) 用来定义返回值参数类型。
begin 和 end 中间包围的就是函数体。用来写具体的逻辑。
declare 用来声明变量，并且可以用 default 设置默认值。这里定义的 ids 即作为整个函数的返回值，是用来拼接成最终我们需要的以逗号分隔的递归串的。而 tempids 是为了记录下边 while 循环中临时生成的所有子节点以逗号拼接成的字符串。
set 用来给变量赋值。此处把传进来的根节点赋值给 tempids 。
while do … end while; 循环语句，循环逻辑包含在内。注意，end while 末尾需要加上分号。
循环体内，先用 CONCAT_WS 函数把最终结果 ids 和临时生成的 tempids 用逗号拼接起来。然后以FIND_IN_SET(pid,tempids)>0 为条件，遍历在 tempids 中的所有 pid ，寻找以此为父节点的所有子节点 id ，并且通过 GROUP_CONCAT(id) into tempids 把这些子节点 id 都用逗号拼接起来，并覆盖更新 tempids 。等下次循环进来时，就会再次拼接 ids ，并再次查找所有子节点的所有子节点。循环往复，一层一层的向下递归遍历子节点。直到判断 tempids 为空，说明所有子节点都已经遍历完了，就结束整个循环。
return ids; 用于把 ids 作为函数返回值返回。

自定义函数做好之后，我们就可以用它来递归查询我们需要的数据了。如，我查询北京研发部的所有子节点。

向上递归

创建自定义函数

create function get_parent_list(in_id varchar(10)) returns varchar(1000) 
begin 
 declare ids varchar(1000); 
 declare tempid varchar(10); 

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

查找北京研发二部一小组，以及它的递归父节点，如下：

注意事项

我们用到了 group_concat 函数来拼接字符串。但是，需要注意它是有长度限制的，默认为 1024 字节。可以通过 show variables like “group_concat_max_len”; 来查看。

注意，单位是字节，不是字符。在 MySQL 中，单个字母占1个字节，而我们平时用的 utf-8下，一个汉字占3个字节。

这个对于递归查询还是非常致命的。因为一般递归的话，关系层级都比较深，很有可能超过最大长度。（尽管一般拼接的都是数字字符串，即单字节）

所以，我们有两种方法解决这个问题：

修改 MySQL 配置文件 my.cnf ，增加 group_concat_max_len = 102400 #你要的最大长度。
执行以下任意一个语句。SET GLOBAL group_concat_max_len=102400; 或者 SET SESSION group_concat_max_len=102400;

他们的区别在于，global是全局的，任意打开一个新的会话都会生效，但是注意，已经打开的当前会话并不会生效。而 session 是只会在当前会话生效，其他会话不生效。

共同点是，它们都会在 MySQL 重启之后失效，以配置文件中的配置为准。所以，建议直接修改配置文件。102400 的长度一般也够用了。假设一个id的长度为10个字节，也能拼上一万个id了。

除此之外，使用 group_concat 函数还有一个限制，就是不能同时使用 limit 。如，

本来只想查5条数据来拼接，现在不生效了。

不过，如果需要的话，可以通过子查询来实现:

转载于：IT牧场