Overview
This article describes issues related to the group_concat function in RDS for MySQL.
Description
Alibaba Cloud reminds you that:
- Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
- You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
- If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.
The following describes the issues related to the group_concat function in RDS for MySQL.
The length of the returned group_concat file.
The length of the result returned by the group_concat function is controlled by the group_concat_max_len parameter. The default value is 1024, that is, 1024 bytes in length is returned by default. The parameter values are as follows:
- Parameter: group_concat_max_len
- Default value: 1024
- Value range: 4-1844674407370954752
- Note: The maximum length of the result returned by the group_concat function, in bytes.
(: can reconfigure parameters the in the RDS group_concat_max_len global entry into force or session-level entry into force of the, the following is the Global Entry into force and session-level entry into force of the details:
- Global settings: you can modify the parameters on the parameter settings page in the console.
- The session-level effective commands are as follows. The first SQL statement sets the group_concat_max_len the current session to 90 bytes. The second SQL statement is to view the group_concat_max_len value of the current session. The third SQL statement is the result returned by group_conca. The fourth SQL statement is the length of the result returned by group_concat.
The following command output is returned.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) removes duplicate data failures
The following lists the causes and solutions for duplicate data failures.
Cause of failure
group_concat(distinct)
command to remove duplicate data in the result.
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
Fixes
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
group_concat and concat returns an exception
The following describes the causes for and solutions to errors that are returned when group_concat and group_concat are used in combination.
Cause
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;
Fixes
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;
Application scope
- ApsaraDB RDS for MySQL