GROUP_CONCAT 返回的長(zhǎng)度默認(rèn)1024
1繁莹、今天在做單元測(cè)試的時(shí)候纵穿，發(fā)現(xiàn)一直java.lang.ArrayIndexOutOfBoundsException: Index 5 out of bounds for length 5
經(jīng)過排查，發(fā)現(xiàn)最后一個(gè)數(shù)組只有5條數(shù)據(jù)收毫，奇了怪了攻走，而且是莫名其妙的數(shù)據(jù)
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;
返回的數(shù)據(jù)
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返回的長(zhǎng)度,返現(xiàn)最長(zhǎng) 的是1024殷勘，有這么巧的么，網(wǎng)上查詢了下group_concat返回限制陋气，還真有劳吠，知識(shí)盲區(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';返回結(jié)果;好了巩趁，破案了
| Variable_name | Value |
|---|---|
| group_concat_max_len | 1024 |
修改：
-
1痒玩、通過SET SESSION group_concat_max_len = 10240;臨時(shí)設(shè)置，當(dāng)前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即永久生效堕战。
