GROUP_CONCAT 返回的長度默認1024

1、今天在做單元測試的時候，發(fā)現一直java.lang.ArrayIndexOutOfBoundsException: Index 5 out of bounds for length 5

經過排查，發(fā)現最后一個數組只有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返回限制，還真有，知識盲區(qū)啊。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;

執(zhí)行 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即永久生效。