How do I handle the issues related to the group_concat function in an ApsaraDB RDS for MySQL instance? - ApsaraDB RDS

This topic describes how to handle the issues related to the group_concat function in an ApsaraDB RDS for MySQL instance.

Length of the return value of group_concat

The length of the return value of the group_concat function is specified by the group_concat_max_len parameter. The default value of the parameter is 1024, which indicates that the length of the return value is 1,024 bytes.

Parameter	Default value	Value range	Description
group_concat_max_len	1024	4-1844674407370954752	The maximum length of the return value of the group_concat function. Unit: bytes.

Note

You can configure settings for the group_concat_max_len parameter to take effect globally or on specific sessions.

If you want the parameter setting to take effect globally, you must modify the parameter setting on the Parameters page of the ApsaraDB RDS console.

If you want the parameter setting to take effect on specific sessions, you must run the following commands:

set group_concat_max_len=90; -- Set the group_concat_max_len parameter to 90 for the current session.

show variables like 'group_concat_max_len'; -- Query the value of the group_concat_max_len parameter of the current session.

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  -- Query the return value.

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  -- Query the length of the return value.

Solution to the failure of removing duplicate data by using group_concat(distinct)

Causes

The group_concat_max_len parameter is set to a large value. In this case, you may fail to use group_concat(distinct) to remove duplicate data.

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 -- Query the return value.

The return value contains multiple entries of duplicate data. If the group_concat function returns a large result set, the memory temporary table cannot handle all data in the result set. In this case, the disk temporary table is used. When the group_concat function queries the disk temporary table, a bug is triggered and duplicate data cannot be removed.

Solution

Run the following commands to modify the tmp_table_size parameter to increase the maximum size of the memory temporary table:

set tmp_table_size=1*1024*1024 -- Set the tmp_table_size parameter to 1 MB for the current session.
show variables like 'tmp_table_size' -- Query the value of the tmp_table_size parameter of the current session.
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

Note

You can also modify the tmp_table_size parameter on the Parameters page of the ApsaraDB RDS console.

Solution to the issue that an abnormal value is returned when group_concat is used together with concat

Causes

In some cases, if you use the group_concat function together with the concat function, values of the BLOB type are returned. Example:

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;

The concat function returns a value by byte. If parameters of multiple data types are specified for the function, the return value of the function is unpredictable.

Solution

You can use the cast function to convert the return value of the concat function to the STRING type. Example:

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;