MySQL GROUP_CONCAT不会返回所有结果。

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:

我使用GROUP_CONCAT以逗号(，)分割结果，但是当我看到时，GRUP_CONCAT只返回205个分割数字，但是在数据库中有2448个结果(不同的辅助)。这是我的查询:

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

我得到以下结果:显示第0 - 29行(总共2448行，..)

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

有人知道为什么它不能工作吗?我在*上搜索了类似的问题，但是没有找到。

3 个解决方案

#1

Probably you have exceeded GROUP_CONCAT maximum length.

可能您已经超过GROUP_CONCAT最大长度。

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:

结果被截断为group_concat_max_len系统变量提供的最大长度，该变量的默认值为1024。虽然返回值的有效最大长度受到max_allowed_packet值的限制，但是可以将值设置得更高。group_concat_max_len在运行时更改值的语法如下，其中val是无符号整数:

SET [GLOBAL | SESSION] group_concat_max_len = val;

#2

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

默认情况下，group_concat()的最大长度是1024。

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

通过更改系统变量group_concat_max_len的值，可以将其更改为更大的值。

The documentation explains this.

文档解释了这一点。

#3

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.

希望这将帮助。

#1