mysql group_concat不带全部数据 [英] mysql group_concat not bringing entire data

问题描述

我正在使用以下查询并利用group_concat函数.但是，有时answers列中的数据会被截断.表示我无法获取全部数据，最后只是被砍掉了.

I am using the following query and utilizing the group_concat function. However, at times the data in the answers column is being cut off; meaning I don't get the entire data, in the end it is just chopped off.

我怀疑它可能与数据类型有关....可以将其转换为更大的数据类型吗?当前Other1数据类型为text

I suspect it might have something to do with the datatype....can it be casted to a bigger datatype? Currently the Other1 datatype is text

 select SiteName, 
case 
when group_concat(Other1) is not null 
  then  group_concat( cast(Other1 AS BLOB)) 
when group_concat(Other1) is null
  then  'No Response provided'
end
 'answers'
from disparities_community_partnerships
where QuarterId=2
group by SiteName

推荐答案

结果被截断为由group_concat_max_len系统变量给定的最大长度，该默认值的默认值为1024.尽管返回值的有效最大长度受到值的限制，但可以将其设置为更高的值. max_allowed_pa​​cket.在运行时更改group_concat_max_len的值的语法如下，其中val是一个无符号整数

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;

这篇关于mysql group_concat不带全部数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！