mysql递归查询

find_in_set 函数

函数语法:find_in_set(str,strlist)

str 代表要查询的字符串 , strlist 是一个以逗号分隔的字符串,如 (‘a,b,c‘)。

此函数用于查找 str 字符串在字符串 strlist 中的位置,返回结果为 1 ~ n 。若没有找到,则返回0。

mysql递归查询

 

  concat:它用于连接N个字符串

mysql递归查询

 

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

mysql递归查询

 

 group_concat:可以分组的同时,把字段以特定分隔符拼接成字符串

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

mysql递归查询

 

 

先开启自定义函数的功能:

mysql递归查询

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

(8)return ids; 用于把 ids 作为函数返回值返回。

(9)函数体结束以后,记得用结束符 $$ 来结束整个逻辑,并执行。

(10)最后别忘了,把结束符重新设置为默认的结束符分号 。

以上是向下递归查询所有子节点的,并且包括了当前节点.

mysql递归查询

 

 

向上递归:

向上递归时,每一层递归一个子节点只对应一个父节点

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 ; 

  mysql递归查询

 

 用到了 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;

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

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

  5. 使用 group_concat 函数还有一个限制,就是不能同时使用 limit 
  6. 如果需要的话,可以通过子查询来实现
    select group_concat(id) from (select * from dept limit 5) d;
    

      

mysql递归查询

上一篇:ORACLE分析函数


下一篇:基本配置(日志、异常处理、数据库)