RDS MySQL函數group_concat相關問題 - ApsaraDB 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在全域生效或會話層級生效：

全域生效：在控制台的參數設定頁面修改。

會話層級生效：

set group_concat_max_len=90; -- 設定當前會話 group_concat_max_len 為 90 位元組

show variables like 'group_concat_max_len'; -- 查看當前會話的 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參數設定，增大記憶體暫存資料表的最大尺寸，命令如下：

set tmp_table_size=1*1024*1024 -- 設定當前會話 tmp_table_size 為 1 MB
show variables like 'tmp_table_size' -- 查看當前會話 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結合使用某些情況下會出現返回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;

返回BLOB欄位

這是由於函數concat按位元組返回結果，如果concat的輸入有多種類型，其結果是不可預期的。

解決方案

通過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;