MySQL“第 30153 行被 GROUP_CONCAT() 剪切”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7208773/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL "Row 30153 was cut by GROUP_CONCAT()" error
提问by Paolo Broccardo
I have a function listed below. When I call it with the LIMIT set at 0,60 as seen below, it works fine. However, whenever I increase that LIMIT to 70 or higher, or even remove the LIMIT, MySQL errors when I call the function with the error: "Row 30153 was cut by GROUP_CONCAT()".
我有下面列出的功能。当我将 LIMIT 设置为 0,60 时调用它，如下所示，它工作正常。但是，每当我将该 LIMIT 增加到 70 或更高，甚至删除 LIMIT 时，当我调用带有错误的函数时，MySQL 都会出错：“第 30153 行被 GROUP_CONCAT() 剪切”。
I have tried increasing the varchar values to 10 000 but that does not help. As far as I can understand from the error, their doesn't seem to be enough space i nthe variable for the contents. But like I mentioned, I have tried increasing the size but it doesn't help. Any ideas?? Thanks
我曾尝试将 varchar 值增加到 10 000，但这无济于事。据我从错误中了解到，它们在变量中似乎没有足够的空间来容纳内容。但就像我提到的那样，我尝试增加大小，但无济于事。有任何想法吗？？谢谢
DELIMITER $$
DROP FUNCTION IF EXISTS `fnAlbumGetPhotoList` $$
CREATE DEFINER=`root`@`%` FUNCTION `fnAlbumGetPhotoList`(_albumId int) RETURNS varchar(2048) CHARSET utf8
BEGIN
DECLARE _outPhotoList VARCHAR(2048);
SET _outPhotoList = (
SELECT (CAST(GROUP_CONCAT(CONCAT(photoId, '|', photoFileName) separator '~') AS CHAR(10000) CHARACTER SET utf8)) AS recentPhotoList
FROM
(
SELECT photoId, photoFileName
FROM photo
WHERE photoAlbumId = _albumId
AND photoIsDisabled = 0
AND photoIsActive = 1
ORDER BY photoId DESC
LIMIT 0,60
) as subQuery
);
RETURN _outPhotoList;
END $$
DELIMITER ;
回答by ain
You could set the group_concat_max_lenvariable to bigger value. Or perhaps use GROUP_CONCAT(DISTINCT ...)to shorthen the result.
您可以将group_concat_max_len变量设置为更大的值。或者可能用于GROUP_CONCAT(DISTINCT ...)缩短结果。
回答by SherylHohman
1) Increase the limit on the number of characters from the resultant query
1) 增加对结果查询字符数的限制
SET global group_concat_max_len=15000;
ORSET session group_concat_max_len=15000;
SET global group_concat_max_len=15000;
或者SET session group_concat_max_len=15000;
Use the former if you want the setting to be the new global default (sticky).
Use the latter if you want to use this setting during the current session only.
(Note also that some have reported trouble when using the globaloption. In that case, try leaving it off, as in SET group_concat_max_len=15000;.)
如果您希望设置成为新的全局默认值（粘性），请使用前者。
如果您只想在当前会话期间使用此设置，请使用后者。
（另请注意，有些人在使用该global选项时报告了问题。在这种情况下，请尝试将其关闭，如 中所示SET group_concat_max_len=15000;。）
2) Then add DISTINCTas first param to GROUP_CONCAT()to remove duplicates from the result query. GROUP_CONCAT(DISTINCT ..).
2）然后添加DISTINCT作为第一个参数以GROUP_CONCAT()从结果查询中删除重复项。GROUP_CONCAT(DISTINCT ..).
Your query will look more like this:
您的查询将更像这样：
SET session group_concat_max_len=15000;
...
... GROUP_CONCAT(DISTINCT CONCAT(photoId, ...)
...
)
Function Group Concat, from MySQL docs:
SET [GLOBAL | SESSION] group_concat_max_len = val;
SET [GLOBAL | SESSION] group_concat_max_len = val;
In MySQL, you can get the concatenated values of expression combinations. To eliminate duplicate values, use the DISTINCT clause.
...
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;
The return value is a nonbinary or binary string, depending on whether the arguments are nonbinary or binary strings. The result type is TEXT or BLOB unless group_concat_max_len is less than or equal to 512, in which case the result type is VARCHAR or VARBINARY.
在 MySQL 中，您可以获取表达式组合的串联值。要消除重复值，请使用 DISTINCT 子句。
...
结果被截断为 group_concat_max_len 系统变量给出的最大长度，其默认值为 1024。该值可以设置得更高，尽管返回值的有效最大长度受 max_allowed_packet 值的限制。在运行时更改 group_concat_max_len 值的语法如下，其中 val 是一个无符号整数：
设置 [全球 | SESSION] group_concat_max_len = val;
返回值是非二进制或二进制字符串，具体取决于参数是非二进制字符串还是二进制字符串。结果类型为 TEXT 或 BLOB，除非 group_concat_max_len 小于或等于 512，在这种情况下，结果类型为 VARCHAR 或 VARBINARY。
Presumably not specifying an optional alternative (GLOBALor SESSION) will default to the first listed alternative (GLOBALin this case), though I could not find this explicitly stated in the documentation.
大概没有指定可选的替代方案（GLOBAL或SESSION）将默认为第一个列出的替代方案（GLOBAL在这种情况下），尽管我在文档中找不到明确说明的这一点。
About syntax used in the MySQL docs:
When a syntax element consists of a number of alternatives, the alternatives are separated by vertical bars (“|”).
When one member from a set of choices maybe chosen, the alternatives are listed within square brackets (“[” and “]”):
When one member from a set of choices mustbe chosen, the alternatives are listed within braces (“{” and “}”)
当一个语法元素由多个备选方案组成时，备选方案用竖线（“|”）分隔。
当从一组的一个选择部件可以被选择，则将替换方括号内所列的（“[”和“]”）：
当必须从一组选项中选择一个成员时，选项列在大括号内（“{”和“}”）
