GROUP_CONCAT 返回的长度默认1024

1、今天在做单元测试的时候，发现一直java.lang.ArrayIndexOutOfBoundsException: Index 5 out of bounds for length 5

经过排查，发现最后一个数组只有5条数据，奇了怪了，而且是莫名其妙的数据

SELECT mrar.item_code,
       pm.jde_description                  as itemName,
       GROUP_CONCAT(CONCAT(mrar.month_order, '-', mrar.wh_dict_id, '-', cd.dict_name, '-', mrar.allocation_ratio, '-',
                           mrar.warehouse_no, '-', mrar.plant)) AS ruleContent
FROM mps_rule_allocation_ratio mrar
         LEFT JOIN common_dict cd ON mrar.wh_dict_id = cd.dict_id AND cd.group_id = 'mps_rule_wh'
         LEFT JOIN product_message pm ON mrar.item_code = pm.jde_code
WHERE mrar.del_status = 0
GROUP BY mrar.item_code,pm.jde_description;

返回的数据

3384820,
OPTIGEAR BM 220 208L,
"1-1-CQ-20.00-CNTS1116-TC,1-2-GZ-30.00-CNCS1466-SDYG,1-3-JN-10.00-CNCS1468-ZHMH,1-4-SY-40.00-CNCS1460-SHDL,1-5-TC-0.00-CNTS1470-ASHH,1-6-TJ-0.00-HKBS1430-SHDL CCL,1-7-WH-0.00-CNCS1116-TC,1-8-XM-0.00-CNTS1466-SDYG,2-1-CQ-20.00-CNTS1116-TC,2-2-GZ-30.00-CNCS1466-SDYG,2-3-JN-10.00-CNCS1468-ZHMH,4-8-XM-0.00-CNTS1466-SDYG,5-1-CQ-20.00-CNTS1116-TC,5-2-GZ-30.00-CNCS1466-SDYG,5-3-JN-10.00-CNCS1468-ZHMH,5-4-SY-40.00-CNCS1460-SHDL,5-5-TC-0.00-CNTS1470-ASHH,5-6-TJ-0.00-HKBS1430-SHDL CCL,5-7-WH-0.00-CNCS1116-TC,5-8-XM-0.00-CNTS1466-SDYG,6-1-CQ-20.00-CNTS1116-TC,6-2-GZ-30.00-CNCS1466-SDYG,6-3-JN-10.00-CNCS1468-ZHMH,6-4-SY-40.00-CNCS1460-SHDL,6-5-TC-0.00-CNTS1470-ASHH,6-6-TJ-0.00-HKBS1430-SHDL CCL,6-7-WH-0.00-CNCS1116-TC,6-8-XM-0.00-CNTS1466-SDYG,2-4-SY-40.00-CNCS1460-SHDL,2-5-TC-0.00-CNTS1470-ASHH,2-6-TJ-0.00-HKBS1430-SHDL CCL,2-7-WH-0.00-CNCS1116-TC,2-8-XM-0.00-CNTS1466-SDYG,3-1-CQ-20.00-CNTS1116-TC,3-2-GZ-30.00-CNCS1466-SDYG,3-3-JN-10.00-CNCS1468-ZHMH,3-4-SY-0.00-CNCS1460-SHDL,3-5-TC-21.00-CNTS1470-ASHH,3-6-TJ-19.00-HKBS1"

加上length 查看下group_concat返回的长度,返现最长 的是1024，有这么巧的么，网上查询了下group_concat返回限制，还真有，知识盲区啊。show VARIABLES like 'group_concat_max_len';

SELECT mrar.item_code,
       pm.jde_description                  as itemName,
       length(GROUP_CONCAT(CONCAT(mrar.month_order, '-', mrar.wh_dict_id, '-', cd.dict_name, '-', mrar.allocation_ratio, '-',
                           mrar.warehouse_no, '-', mrar.plant))) AS ruleContent
FROM mps_rule_allocation_ratio mrar
         LEFT JOIN common_dict cd ON mrar.wh_dict_id = cd.dict_id AND cd.group_id = 'mps_rule_wh'
         LEFT JOIN product_message pm ON mrar.item_code = pm.jde_code
WHERE mrar.del_status = 0
GROUP BY mrar.item_code,pm.jde_description;

执行 show VARIABLES like 'group_concat_max_len';返回结果;好了，破案了

修改：

• 1、通过SET SESSION group_concat_max_len = 10240;临时设置，当前session有效，客户端重启失效，SET global group_concat_max_len = 10240;， MySQL重启失效。

  SET global group_concat_max_len = 10240;
  set session group_concat_max_len  = 10240;
  

• 2、通过修改my.cnf或my.ini文件，找到[mysqld] 在后面添加group_concat_max_len=10240，保存重启mysql即永久生效。