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..

Answer 1

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;

Answer 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.

Answer 3

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.