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).
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:
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.
Adjust the configuration of tmp_table_size and increase the maximum size of the memory temporary table.
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).