Mysql 递归查询解决方案

关于Mysql递归查询，归纳有以下几种方式：

1、递归CTE

如果是Mysql 8.0 以上的版本，支持递归CTE（公用表表达式），它是一个命名的临时结果集，仅在单个SQL语句(例如SELECT，INSERT，UPDATE或DELETE)的执行范围内存在。与派生表类似，CTE不作为对象存储，仅在查询执行期间持续。 与派生表不同，CTE可以是自引用(递归CTE)，也可以在同一查询中多次引用。 此外，与派生表相比，CTE提供了更好的可读性和性能。大多数的数据库都支持CTE。

语法：

WITH cte_name (column_list) AS (
    query
) 
SELECT * FROM cte_name;

1.cte_name :CTE表达式的名称。

2.column_list:列名列表。

3.query:定义CTE结果集的Select查询语句

注：查询中的列数必须与column_list中的列数相同。 如果省略column_list，CTE将使用定义CTE的查询的列列表,上面的语法只能在 MySQL8.0 以上版本才支持。

2、自定义函数

而在Mysql 8.0以下的版本中，可以使用 自定义函数 来实现递归查询

以向下递归为例 ，讲解自定义函数写法的同时，讲解递归

delimiter $$ 
drop function if exists get_child_list$$ 
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  
$$ 
delimiter ;

（1） delimiter $$ ，用于定义结束符。我们知道 MySQL 默认的结束符为分号，表明指令结束并执行。但是在函数体中，有时我们希望遇到分号不结束，因此需要暂时把结束符改为一个随意的其他值。我这里设置为 $$，意思是遇到 $$ 才结束，并执行当前语句。

（2）drop function if exists get_child_list$$ 。若函数 get_child_list 已经存在了，则先删除它。注意这里需要用 当前自定义的结束符 $$ 来结束并执行语句。 因为，这里需要和下边的函数体单独区分开来。

（3）create function get_child_list 创建函数。并且参数传入一个根节点的子节点id，需要注意一定要注明参数的类型和长度，如这里是 varchar(10)。returns varchar(1000) 用来定义返回值参数类型。

（4）begin 和 end 中间包围的就是函数体。用来写具体的逻辑。

（5）declare 用来声明变量，并且可以用 default 设置默认值。

这里定义的 ids 即作为整个函数的返回值，是用来拼接成最终我们需要的以逗号分隔的递归串的。

而 tempids 是为了记录下边 while 循环中临时生成的所有子节点以逗号拼接成的字符串。

（6） set 用来给变量赋值。此处把传进来的根节点赋值给 tempids 。

（7） 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 为空，说明所有子节点都已经遍历完了，就结束整个循环。

这里，用 ‘1000’ 来举例，即是：（参看图1的表数据关系）

第一次循环：
  tempids=1000  ids=1000    tempids=1001,1002 (1000的所有子节点)
第二次循环：
  tempids=1001,1002  ids=1000,1001,1002  tempids=1003,1004,1005,1013 (1001和1002的所有子节点)
第三次循环：
  tempids=1003,1004,1005,1013 
  ids=1000,1001,1002,1003,1004,1005,1013 
  tempids=1003和1004和1005及1013的所有子节点
...
最后一次循环，因找不到子节点，tempids=null，就结束循环。

（8）return ids; 用于把 ids 作为函数返回值返回。

（9）函数体结束以后，记得用结束符 $$ 来结束整个逻辑，并执行。

（10）最后别忘了，把结束符重新设置为默认的结束符分号 。

手动实现递归查询（向上递归）

相对于向下递归来说，向上递归比较简单。

因为向下递归时，每一层递归一个父节点都对应多个子节点。

而向上递归时，每一层递归一个子节点只对应一个父节点，关系比较单一。

同样的，我们可以定义一个函数 get_parent_list 来获取根节点的所有父节点。

delimiter $$ 
drop function if exists get_parent_list$$ 
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 
$$ 
delimiter ;

注意事项

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

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

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

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

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

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

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

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

3、SQL语句

另外。还有一种方式，思路与第二种类似，也是使用 GROUP_CONCAT 和 FIND_IN_SET 函数，看起来更简单，但是有点难以理解。

查询当前单位的所有下级单位

SELECT DATA.uid FROM(
      SELECT
      @ids AS _ids,
      (
      SELECT @ids := GROUP_CONCAT(uid)
      FROM corpinfo
      WHERE FIND_IN_SET(ubelong, @ids)
      ) AS cids,
      @l := @l+1 AS level
      FROM corpinfo, (SELECT @ids := (参数) , @l := 0 ) b
      WHERE @ids IS NOT NULL
      ) ID, corpinfo DATA
WHERE FIND_IN_SET(DATA.uid, ID._ids)
ORDER BY level,uid

查询所有上级单位

SELECT uid FROM(
        SELECT
        @id AS _id,
        ( SELECT @id := ubelong
        FROM corpinfo
        WHERE uid = @id
        ) AS _pid,
        @l := @l+1 as level
        FROM corpinfo,
        (SELECT @id := (参数), @l := 0 ) b
        WHERE @id > 0
        ) ID, corpinfo DATA
        WHERE ID._id = DATA.uid
   ORDER BY level DESC

4、代码实现