MySQL GROUP_CONCAT doesn't return all results

Question

i've used GROUP_CONCAT to get results splitted by comma (,), but when i saw, the GRUP_CONCAT returned only 205 splitted numbers, but in the database there's 2448 results (different aid). Here is my query:

SELECT GROUP_CONCAT(`aid`) As favoriti 
FROM `z_web_favoriti` 
WHERE `kup_id`='1' AND `pos_id`='571'

When i execute:

SELECT DISTINCT `aid` 
FROM `z_web_favoriti` 
WHERE `kup_id`='1' AND `pos_id`='571'

I get the following result: Showing rows 0 - 29 (2448 total,..)

Anyone has some solution why it isn't working? I've searched on stackoverflow for similar problem, but i couldn't find it..

score 10 · Accepted Answer · answered Feb 28 '17 at 12:09

10

Probably you have exceeded GROUP_CONCAT maximum length.

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:

SET [GLOBAL | SESSION] group_concat_max_len = val;

answered Feb 28 '17 at 12:09

McNets

10,352
3
32
61

I'm glad to be able to help. – McNets Feb 28 '17 at 12:16
Thanks, this works for me when I used as multiplies **1024** with any number Ex: I used **1024*100=102400** `SET GLOBAL group_concat_max_len = 102400` – Goshika Mahesh Aug 04 '20 at 10:48

score 2 · Answer 2 · answered Feb 28 '17 at 12:08

2

Please try below code.

SET GLOBAL group_concat_max_len=15000;
SELECT GROUP_CONCAT(`aid`) As favoriti 
FROM `z_web_favoriti` 
WHERE `kup_id`='1' AND `pos_id`='571'

Hope this will helps.

answered Feb 28 '17 at 12:08

Sagar Gangwal

7,544
3
24
38

`#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation ` - can this be changed through `ini` files? – Ultrazz008 Feb 28 '17 at 12:10
@Ultrazz008 just try with `SET group_concat_max_len=15000;` – Sagar Gangwal Feb 28 '17 at 12:12
Successfull executed, but the same result, even if i setted to 30000. – Ultrazz008 Feb 28 '17 at 12:14

score 1 · Answer 3 · answered Feb 28 '17 at 12:07

1

By default, the maximum length for group_concat() is 1,024.

You can change this to a larger value by changing the value of the system variable group_concat_max_len.

The documentation explains this.

answered Feb 28 '17 at 12:07

Gordon Linoff

1,242,037
58
646
786

MySQL GROUP_CONCAT doesn't return all results

3 Answers3