mysql 递归查询函数大数据量返回数据不全

mysql的递归查询一般是借助自定义函数和find_in_set实现的。

向下查找的函数定义为：

CREATE DEFINER=`user`@`%` FUNCTION `cms_bom_find_child`(i_item_id bigint) RETURNS varchar(10240) CHARSET utf8mb4
    DETERMINISTIC
begin
  declare v_ids varchar(10240);
  declare v_temp_id varchar(10240);
  declare err_var_null condition for sqlstate '23502';

  if i_item_id is null then
    signal err_var_null set message_text='i_item_id不可为空!';
  end if;

  set v_ids = '$';
  set v_temp_id = convert(i_item_id,char);
  while v_temp_id is not null do
    set v_ids = concat(v_ids,',',v_temp_id);
    select group_concat(item_id) into v_temp_id from cms_bom where find_in_set(parent_item_id,v_temp_id)>0;
  end while;
  return v_ids;
end

在代码中查询的语句为：

SELECT
    * 
FROM
    cms_bom 
WHERE
    find_in_set( item_id, cms_bom_find_child ( 1696431777739777 ) ) > 0 
ORDER BY
    tenant_id,
    parent_item_id,
    item_id;

其中1696431777739777这个是从哪个节点开始查找。

但是某次在项目中发现，一棵树定义的特别大的时候，查出来的数据缺失了一部分，最终定位发现是这个查询这边出问题了。

划重点，根本原因就是group_concat这个函数，它的返回长度默认1024，超过1024就会丢失掉。所以我们得设置下这个返回的长度，设置的参数项是 group_concat_max_len

设置的办法有两种：

SET SESSION group_concat_max_len = 102400;

SET GLOBAL group_concat_max_len = 102400;

第一种只是针对当前的session有效，第二种对全局永久生效，根据自己需要修改即可。修改后无需重启数据库服务。但是要刷新一下系统所建立的session连接才能生效（换句话就是重启客户端服务）。