MySQL“行30153被GROUP_CONCAT()切断了".错误

问题描述

我有下面列出的功能.当我将LIMIT设置为0.60时，如下所示，它可以正常工作.但是，每当我将LIMIT增加到70或更高，甚至删除LIMIT时，当我调用带有错误的函数时，MySQL都会出现错误:"Row 30153被GROUP_CONCAT()切断了."

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()".

我尝试将varchar值增加到10000，但这无济于事.据我从错误中可以理解的是，在变量内容中，它们似乎没有足够的空间.但是就像我提到的那样，我尝试增加大小，但没有帮助.有任何想法吗??谢谢

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

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 ;

推荐答案

您可以设置变量设置为更大的值.或使用 GROUP_CONCAT(DISTINCT ...) 缩短结果

You could set the group_concat_max_len variable to bigger value. Or perhaps use GROUP_CONCAT(DISTINCT ...) to shorthen the result.

这篇关于MySQL“行30153被GROUP_CONCAT()切断了".错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！