edit-icon download-icon

Introduction to the group_concat function in ApsaraDB for RDS MySQL

Last Updated: Jul 13, 2016

 

The group_concat function in ApsaraDB for RDS MySQL is introduced as below. 


1. Length of Returned Results of Group_concat

The length of returned results of the group_concat function is determined by the group_concat_max_len parameter. The default value is 1,024. That is, a result of 1,024 bytes is returned by default.

#
Parameter Name
Default Value
Minimum Value
Maximum Value
Role
1
group_concat_max_len
1,024
4
1844674407370954752
It specifies the maximum length of the returned results of the function group_concat. The unit is byte.

NOTE: This parameter can be configured on Console > Parameter Configuration (globally effective), or in Session Level (effective only for the current session).

group_concat_06.png

 

2. Using Group_concat(distinct) to Remove Invalid Repeating Data

When a large value is set for group_concat_max_len, group_concat and distinct can be used to remove invalid repeating data in the returned results:

group_concat_03.png

As shown in the above figure, the returned results contain multiple repeating values.

The reason is that, when the returned result set of group_concat is too large, data in the result set cannot be wholly stored in the memory temporary table and thus part of data is stored in the disk temporary table; however, when group_concat uses the disk temporary table, a bug is triggered, and thus the repeating data cannot be removed.

group_concat_05.png

Solution:

Adjust the configuration of tmp_table_size and increase the maximum size of the memory temporary table.

group_concat_04.png


NOTE:

1. For information about the bug, refer to: https://bugs.mysql.com/bug.php?id=68145

2. The  tmp_table_size parameter can be configured in Console > Parameter Configuration (globally effective, but invalid for sessions already connected before the parameter is configured) or in Session Level (only effective for the current session).

 

 

If the problem persists, contact Aliyun After-Sales Technical Support.

 

Thank you! We've received your feedback.