[MySQL][CakePHP4] GROUP_CONCAT でカラム値を連結したい＆group_concat_max_len システム変数

CakePHP 4 で GROUP_CONCAT したくなり、最終的に MySQL のシステム変数である group_concat_max_len のデフォルト値を踏んだよって話をします。

• sysvar_group_concat_max_len システム変数のMySQL公式

要件

• MySQL 5.7
• CakePHP 4
• 1 対 多 のテーブル構成
• 多 のカラムを連結して扱いたい

前提

管理者とその権限を一覧表示したいとする

テーブル

テーブル名	概要
admin_users	管理者テーブル
roles	権限テーブル
admin_users_roles	中間テーブル

リレーション

• admin_users.id
  • 管理者の一覧を表示する画面
• roles.id
  • roles.name を連結したい
• admin_users_roles.admin_user_id
• admin_users_roles.role_id

データ

テーブルには created, modified カラムがありますが表示は省略しています

admin_users

mysql> select id, email, expired from admin_users\G
*************************** 1. row ***************************
      id: 1
   email: [e-mail]
 expired: 2099-12-31 00:00:00
1 rows in set (0.00 sec)

roles

mysql> select id, name from roles\G
*************************** 1. row ***************************
          id: 1
        name: テスト権限1
*************************** 2. row ***************************
          id: 2
        name: テスト権限2
2 rows in set (0.00 sec)

admin_users_roles

mysql> select id, admin_user_id, role_id from roles_users limit 5\G
*************************** 1. row ***************************
           id: 1
admin_user_id: 1
      role_id: 1
*************************** 2. row ***************************
           id: 2
admin_user_id: 1
      role_id: 2
2 rows in set (0.00 sec)

コード

Controller からクエリを実行するコードです。
以下のファイルがあるものとします。

• Controller
  • AdminUsersController.php
• Model/Table
  • AdminUsersTable.php
  • AdminUsersRolesTable.php
  • RolesTable.php
• Model/Entity
  • AdminUser.php

各Tableファイルにて適切にリレーション（belongsToMany, belongsTo など）していれば、JOINを意識しないコードが書けますが、今回はクエリで表現します。

なお、CakePHP 4 の group_concat 関数は公式には記載がないですがやってみたら動きました。
もしかしたら推奨じゃないかもなので、その際は select 関数内にクエリを書いてください。

$query = $this->AdminUsers->find();
$findAdminUsers = $query->select([
        'AdminUsers.id',
        'AdminUsers.email',
        'AdminUsers.expired',
        'roles_names' => $query->func()->group_concat(['Roles.name' => 'identifier']),
    ])
    ->join([
        'AdminUsersRoles' =>
        [
            'table' => 'admin_users_roles',
            'type' => 'LEFT',
            'conditions' => 'AdminUsers.id = AdminUsersRoles.admin_user_id',
        ],
        'Roles' =>
        [
            'table' => 'roles',
            'type' => 'LEFT',
            'conditions' => 'AdminUsersRoles.role_id = Roles.id',
        ],
    ])
    ->group(['AdminUsersRoles.admin_user_id'])
    ->toArray();

結果

実行されたクエリ

CakePHP 4 のクエリビルダで生成されたクエリです。

SELECT
    AdminUsers.id AS AdminUsers__id,
    AdminUsers.email AS AdminUsers__email,
    AdminUsers.expired AS AdminUsers__expired,
    group_concat(Roles.name order by Roles.name) AS roles_names
FROM
    admin_users AdminUsers
    LEFT JOIN
        admin_users_roles AdminUsersRoles
    ON  AdminUsers.user_id = AdminUsersRoles.user_id
    LEFT JOIN
        roles Roles
    ON  AdminUsersRoles.role_id = Roles.id
GROUP BY
    AdminUsersRoles.user_id

実行結果

*************************** 1. row ***************************
        AdminUsers__id: 1
     AdminUsers__email: [e-mail]
   AdminUsers__expired: 2099-12-31 00:00:00
           roles_names: テスト権限1,テスト権限2

group_concat 関数の実装状況

実装したら動いたんですが…
こちら @ktou先生からのご指摘にもある通り、GitHub をみると実装中で、まだ反映されていないように見えます。
https://github.com/cakephp/cakephp/pull/14620

差分をしっかり読まないと確たることは言えないのですが…
もしかしたら MySQL だけ使えるのかもしれません。手元に MySQL (Aurora) しかないので検証できておりませんが…

group_concat_max_len システム変数

group_concat は MySQL 独自の関数です。
連結できる文字列長は MySQL の group_concat_max_len システム変数 で設定されており、デフォルトは 1024 byte です。
上記のテストでは権限が2つしかありませんでしたが、実際の業務だと数十の権限があったりであっさりデフォルト値を超えます。（超えました）

そこで以下のように適切な値に設定します。

group_concat_max_len システム変数設定

デフォルト値を確認します。

mysql> SHOW GLOBAL VARIABLES LIKE 'group_concat_max_len';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 1024  |
+----------------------+-------+
1 row in set (0.04 sec)

グローバルシステム変数を変更します。
永続化する場合は、my.cnf なり RDS の設定なりに反映させてください。

mysql> SET GLOBAL group_concat_max_len = 4096;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'group_concat_max_len';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 4096  |
+----------------------+-------+
1 row in set (0.00 sec)

設定値を増やすことのリスクと考察

group_concat 関数は文字列を返すため、レコード数が多ければ多いほどメモリを消費します。
そのため昔はメモリ使用量を気にする必要があり、 group_concat_max_len=1024 がデフォルトで設定されていたのだなという推測なんだと思います。

僕が理解したことを重複を恐れずに箇条書きにすると以下です。

• group_concat は文字列が返る
• group_concat は昔からある
• そのままだと無制限なのでデフォルト 1024 で制限している
• 設定値を大きくして大きなパケットをやりとりするとメモリが枯渇して OOM Killer 発動の可能性があった
• 現在のハードウェア事情と利用シチュエーションを考えると OOM Killer は発生しにくい
• それよりも group_concat の結果が途切れる方が問題の場合が多い
• とはいえ max_allowed_packet を max である 1GB まで上げた上に頻発すると危ないかもしれない
• なので無闇に設定値を大きくしないで適切な数値を考えよう

ここはもうつべこべ言わずに @yoku0825先生と @hironomiu先生からいただいた以下のスレッドをみて欲しいです。

他のMAX, SUMなどの集約関数は、整数型カラムならたかだかBIGINTの8バイト、varchar型でも元のカラムのサイズが頭打ち(varchar(50)ならMAXでもなんでも50文字以上はありえない)ですが、GROUP_CONCATとJSON_ARRAYAGG, JSON_OBJECTAGGは「元のカラムが何であろうと文字列(またはJSON)型」が返ります

— yoku0825 (@yoku0825) November 11, 2020

AWS公式による設定値の注意点

安易に大きな値を設定すると、思わぬメモリ消費などに繋がる恐れがあるので、適切な値を計算して設定しましょう。
AWS公式 から引用します。

group_concat_max_len
推奨される設定: デフォルト (1,024 バイト)。ワークロードに必要な場合にだけ、カスタム値を使用します。このパラメータをチューニングする必要があるのは、GROUP_CONCAT() ステートメントの戻り値を変更して、エンジンがより長い列値を返すようにする場合だけです。この値は、応答の最大サイズを決定するため、max_allowed_packet と並行して使用する必要があります。

影響: このパラメータを高く設定しすぎると、メモリ使用量が多くなり、メモリ不足の状態になることがあります。低く設定しすぎると、クエリが失敗します。