ApsaraDB RDS for MySQL インスタンスにおける group_concat 関数の問題への対処方法 - ApsaraDB RDS

GROUP_CONCAT() は、複数の行を、区切り文字で区切られた 1 つの文字列に集約します。ApsaraDB RDS for MySQL では、一般的に 3 つの問題が発生します。具体的には、DISTINCT を使用した場合の出力の切り捨てと重複排除の失敗、および CONCAT() と組み合わせた場合の予期しない BLOB 型の戻り値です。これら 3 つはすべて group_concat_max_len パラメーターに起因します。

戻り値の長さ

group_concat_max_len パラメーターは、GROUP_CONCAT() によって返される文字列の最大長を制御します。デフォルト値は 1024 バイトで、この値を超えると結果は通知なく切り捨てられます。

パラメーター	デフォルト値	値の範囲	説明
`group_concat_max_len`	1024	4-1844674407370954752	`GROUP_CONCAT()` の戻り値の最大長。単位：バイト。

このパラメーターは、グローバルまたはセッションごとに設定できます：

グローバル：ApsaraDB RDS コンソールの [パラメーター] ページでパラメーターを変更します。
セッションごと：次の SQL コマンドを実行します。

-- 現在のセッションの group_concat_max_len を 90 に設定
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) による重複排除の失敗

原因

group_concat_max_len が大きい値に設定されている場合、GROUP_CONCAT(DISTINCT ...) は重複した行を返すことがあります。原因は次のとおりです：

group_concat_max_len が大きいと、大規模な結果セットが生成されます。
結果セットがメモリ一時テーブルの容量を超えると、MySQL はディスク一時テーブルに書き出します。
ディスク一時テーブルのコードパスにある MySQL のバグ (ID 68145) が原因で、DISTINCT による重複排除が機能しなくなります。

ソリューション

tmp_table_size を増やすことで、一時テーブルをメモリ内に保持し、このバグを回避します。

-- 現在のセッションの tmp_table_size を 1 MB に設定
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

変更をグローバルに適用するには、ApsaraDB RDS コンソールの [パラメーター] ページで tmp_table_size パラメーターを変更します。

CONCAT() 内で GROUP_CONCAT() を使用した場合の BLOB 型の戻り値

原因

CONCAT() はバイト単位で動作します。GROUP_CONCAT() によって返される BLOB 型を含む、引数のデータ型が混在している場合、CONCAT() の戻り値の型は不定となり、多くの場合 BLOB 型になります。

この動作を引き起こすクエリの例：

SELECT concat('{', group_concat(concat('"payMoney', t.signature, '":', ifnull(t.money, 0))), '}') AS payType
FROM my_money t
WHERE cash_id = '989898989898998898'
GROUP BY cash_id;

ソリューション

CAST(... AS CHAR) を使用して、GROUP_CONCAT() の結果を CHAR 型に変換してから CONCAT() に渡します。

SELECT concat('{', cast(group_concat(concat('"payMoney', t.signature, '":', ifnull(t.money, 0))) AS CHAR), '}') AS payType
FROM my_money y t
WHERE cash_id = '989898989898998898'
GROUP BY cash_id;