MySQLのgroup_concatの結果が短い気がするのは環境変数で制限されているから。

2013年8月9日 in MySQL

少し前のことですが、「IDの一覧出したくてgroup_concatしたのだけど、結果がおかしいです」と聞かれまして、たぶんみんな一回は経験することのような気がするので書いておきます。

MySQLのgroup_concatというのは集約関数の一つで、複数のレコードの値を、区切り文字を入れながら文字列結合するというものです。

スクリーンショット 2013-08-09 19.42.25

group_concatの結果が短い気がする

こんなクエリ書く人いないかもしれませんが、「全ユーザーIDをランダムな順番でコンマ区切りで取得したい」という狙いで以下のクエリを実行しました。

mysql> select group_concat(id order by rand()) as user_ids from user\G
*************************** 1. row ***************************
user_ids: 12345,25830,367,44056,24514,43735,44223,2239,31453,8273,
# ...(なんか短い気がする)...
39970,22347,44261,42362,41776,32490,42273,10639,42108,2869,38042,36303,2
1 row in set, 1 warning (0.05 sec)

すると結果がかなり短いような気がする。全ユーザーのIDを出しているはずなので、もっと膨大なはずなのに。よく見ると、最後の「2」がどうもそれ以降切り取られてしまっているような痕跡にも感じる･･･。

警告が出ているので見る

「1 warning」と書いてあるので警告を表示させてみます。

mysql> show warnings;
+---------+------+-----------------------------------+
| Level   | Code | Message                           |
+---------+------+-----------------------------------+
| Warning | 1260 | Row 182 was cut by GROUP_CONCAT() |
+---------+------+-----------------------------------+
1 row in set (0.00 sec)

182行目で打ち切られてるいるっぽいです。ユーザーはもっとたくさんいるのに･･･。

group_concatの結果の最大長は環境変数で決まっている

実はMySQLのgroup_concatの結果の最大長は、環境変数で決まっています。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 4.2.3 システム変数

何かというと、group_concat_max_lenという変数なのですが、これを出力させてみると、1024文字で打ち切られてしまうことが分かります。

mysql> show variables like 'group_concat_max_len';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 1024  |
+----------------------+-------+
1 row in set (0.02 sec)

group_concat_max_lenを書き換えて再実行

そういうわけで、このgroup_concat_max_len変数を大きくしてやれば途中で打ち切られなくなります。今回は乱暴ですが、1,000万くらいにしてしまいます。

mysql> set group_concat_max_len = 10000000;
Query OK, 0 rows affected (0.00 sec)

そして、同じクエリを実行してみると、果てしなく長い結果が得られ、warningもなくなりました。

mysql> select group_concat(id order by rand()) as user_ids from user\G
*************************** 1. row ***************************
user_ids: 26789,17039,34773,35980,949,36023,40079,29978,34962,35090,
# ...(果てしなく長い)...
37383,40794,27224,33786,24423,26349,14315,26277,44123
1 row in set (0.13 sec)