mysql之使用find_in_set和group_concat组合递归函数时数据不全

最新推荐文章于 2025-11-12 11:03:15 发布

原创最新推荐文章于 2025-11-12 11:03:15 发布 · 6.2k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #group_concat

数据库同时被 2 个专栏收录

16 篇文章

订阅专栏

mysql

5 篇文章

订阅专栏

本文介绍了一个MySQL中的自定义递归函数实现方法，用于获取层级结构数据，并解决了因Group Concat默认长度限制导致的数据不完整问题。

mysql中没提供现成的递归函数，于是网上找了下，发现几乎全是这类型

CREATE DEFINER=`root`@`%` FUNCTION `getChildLst`(rootId VARCHAR(40)) RETURNS varchar(20000) CHARSET utf8
    READS SQL DATA
BEGIN 
       DECLARE sTemp VARCHAR(20000); 
       DECLARE sTempChd VARCHAR(20000); 
     
       SET sTemp = '$'; 
       SET sTempChd = rootId; 
    
      WHILE sTempChd is not null DO 
         SET sTemp = concat(sTemp,',',sTempChd); 
         SELECT group_concat(unit_id) INTO sTempChd FROM base_unit where FIND_IN_SET(parent_unitid,sTempChd) > 0; 
       END WHILE; 
      RETURN sTemp; 
     END

查询结果sql

select * from base_unit u where FIND_IN_SET(unit_id,getChildLst('2f566d9b-0b64-4bba-82b2-88e134efcd52')) ;

刚开始测试时，发现的确达到了我要的目的，也就不注意了。但是用了一段时间后，测试MM说，数据不齐全。赶紧进行跟踪，最终发现group_concat里面的结果居然是不完整的。网上查了下为何group_concat数据不全，也不报错。原来group_concat是有长度限制的。解决方法是修改group_concat的长度。

-- 查看 group_concat_max_len 
show variables like 'group_concat_max_len'

如果不方便重启数据库，那可以

-- SET GLOBAL group_concat_max_len = 400000

但是这处理，如果数据库重启了，它又会回复会原值。所以也可以在配置文件修改my.ini

在[mysqld]下新增配置：group_concat_max_len = 102400

处理好后，端口navicat(或者其他客户端）重新连接数据库，再次查询，这次数据齐全了