全部产品

RDS MySQL实例中group_concat函数相关问题

概述

本文主要介绍RDS MySQL中group_concat函数的相关问题。

详细信息

阿里云提醒您:

  • 如果您对实例或数据有修改、变更等风险操作,务必注意实例的容灾、容错能力,确保数据安全。
  • 如果您对实例(包括但不限于ECS、RDS)等进行配置与数据修改,建议提前创建快照或开启RDS日志备份等功能。
  • 如果您在阿里云平台授权或者提交过登录账号、密码等安全信息,建议您及时修改。

以下是RDS MySQL中group_concat函数相关问题的详细内容。

group_concat返回结果的长度

group_concat函数返回结果的长度受参数group_concat_max_len控制,默认值为1024,即默认返回1024字节长度的结果。具体参数值请参考如下:

  • 参数名称:group_concat_max_len
  • 默认值:1024
  • 取值范围:4-1844674407370954752
  • 说明:group_concat函数返回结果的最大长度,单位为Byte。

说明可以设置参数group_concat_max_len在全局生效或会话级别生效,以下是全局生效和会话级别生效的详细内容:

  • 全局生效:在控制台的参数设置页面修改。
  • 会话级别生效命令依次如下。其中第一个SQL语句是设置当前会话group_concat_max_len为90字节。第二个SQL语句是查看当前会话的group_concat_max_len值。第三个SQL语句是group_conca返回结果。第四个SQL语句是group_concat返回结果的长度。
    set group_concat_max_len=90;
    show variables like 'group_concat_max_len'; 
    select group_concat(distinct concat_ws(' ', t1.col0, t1.col2, t1.col3, t1.col4) separator "---")
    from grp_con_test t1,
         grp_con_test t2 \G
    select length(group_concat(distinct concat_ws(' ', t1.col0, t1.col2, t1.col3, t1.col4) separator "---"))
    from grp_con_test t1,
         grp_con_test t2 \G
    系统显示类似如下。
    当前会话生效

group_concat(distinct)去除重复数据失效的处理

以下是去重复数据失效的原因和解决方法。

失效原因

当设置group_concat_max_len为较大值时,使用group_concat(distinct)相关命令去除结果中的重复数据,会出现失效的情况,如下所示。
select group_concat(distinct concat_ws(' ', t1.col0, t1.col2, t1.col3, t1.col4) separator "---")
from grp_con_test t1,
   grp_con_test t2 \G
系统显示类似如下,结果中出现了多个重复值。出现这个问题的原因是当group_concat返回结果集比较大,会出现内存临时表无法承载全部结果集数据,进而会使用磁盘临时表。而group_concat在使用磁盘临时表时会触发Bug导致无法去除重复数据。
数据重复

解决方法

调整tmp_table_size参数设置,增大内存临时表的最大尺寸,依次执行如下命令。其中第一个SQL语句是设置当前会话tmp_table_size为1MB。第二个SQL语句是查看当前会话tmp_table_size的设置。
set tmp_table_size=1*1024*1024
show variables like 'tmp_table_size'
select group_concat(distinct concat_ws(' ', t1.col0, t1.col2, t1.col3, t1.col4) separator "---")
from grp_con_test t1,
     grp_con_test t2 \G
说明:也可以在控制台的参数设置页面修改tmp_table_size参数。

group_concat和concat结合使用返回异常

以下是group_concat和concat结合使用返回异常的原因和解决方法。

异常原因

group_concat和concat结合使用某些情况下会出现返回BLOB字段类型的情况,如下所示。
select concat('{', group_concat(concat('\"payMoney', t.signature, '\":', ifnull(t.money, 0))), '}') payType
from my_money t
where cash_id='989898989898998898'
group by cash_id;
系统显示类似如下,这是由于concat按字节返回结果,如果concat的输入有多种类型,其结果是不可预期的。返回BLOB字段

解决方法

通过cast函数进行约束,concat返回结果为字符串类型,将上一节的例子修改为如下内容。
select concat('{', cast(group_concat(concat('\"payMoney', t.signature, '\":', IFNULL(t.money, 0))) as char), '}') payType
from my_money y t
where cash_id='989898989898998898'
group by cash_id;
系统显示类似如下。

适用于

  • 云数据库RDS MySQL版