MySQL道普請便り

第142回　MySQLのgroup byについて

MySQL, group by, SQLモード

この記事を読むのに必要な時間：およそ 6 分

集計をする上で必要となるgroup byですが，MySQLではちょっと変わった形の（SQL標準とは挙動が異なる）実行することもできます。MySQL 5.6以前のMySQLを利用した方の中には，お世話になった人も現在奮闘されている方も多いのではないかなと思います。

今回はMySQLの拡張されたgroup byについて説明していきたいと思います。

検証環境

今回は第125回　phpMyAdminでDockerで建てたMySQLにアクセスするで記載したdocker-composeを利用して作成します。手元で簡単に試せるように，GitHubのわたしのレポジトリにサンプルコードとして置いてあるので，気軽に試したい方はgit cloneして試してみてください。試すにはdockerとdocker-composeが必要です。

group byのMySQL拡張

今回の検証はMySQLの独自拡張を利用することになるため，第18回　MySQL5.7のデフォルトのSQLモードを確認してみるで紹介したONLY_FULL_GROUP_BYを無効にする必要があります。

まずは今回紹介したいgroup by拡張ですが，以下のようなクエリを実行することができます。

select * from zipcode group by zip_code;

SQLモードを設定せずに上記のSQLを現行のMySQLで実行すると，以下のようにエラーとなります。

mysql> select * from zipcode group by zip_code;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'zipcode.zipcode.code' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECTのリストの中でGROUP BYの中に含まれていないカラムと集約関数を通してないカラムが含まれているためエラーになっていて，これはsql_mode=only_full_group_byと非互換であると書かれています。

では，sql_modeを空っぽにしてみましょう。

SQLモードを指定する方法は，第60回　SQLモードについて［その1］でglobalセッションに指定する方法と，第31回　MySQLのオプションファイル my.cnfの豆知識［その1］で，my.cnfを指定する方法を紹介してきました。

手元で試してみたい場合は，globalセッションで登録してしまうと良いかもしれません。今回は，my.cnfをdocker-composeで指定する方法を紹介します。

docker-composeでmy.cnfを設定する

まずはじめに，現状のSQLモードについて確認してみましょう。

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

以下のように，mycnfディレクトリに設定したいmy.cnfを配置しました。

$ tree
.
├── Dockerfile
├── README.md
├── docker-compose.yml
├── docker-entrypoint-initdb.d
│   ├── 1_ddl.sql
│   ├── 2_load_data_infile.sql
│   └── KEN_ALL_UTF8.CSV
└── mycnf
    └── my.cnf

今回のmy.cnfの内容は，以下の通りになります。

[mysqld]
sql_mode=""

docker-compose.ymlのvolumesで，上記のマウントの設定を追加します。

version: '3'
services:
  mysql:
    build: .
    volumes:
      - ./docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
      - ./mycnf/my.cnf:/etc/mysql/my.cnf # my.cnfをマウントする
    environment:
      MYSQL_ROOT_PASSWORD: password
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --secure-file-priv="/docker-entrypoint-initdb.d"

docker-composeを再起動して，sqlモードを確認してみましょう。

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+

ということで，空にすることができました。

MySQL, group by, SQLモード

著者プロフィール

木村浩一郎（きむらこういちろう）

株式会社オプティム 技術統括本部のエンジニア。最近はミドルウェア・インフラ周りのことも少しずつ学習しています。趣味は将棋。好きな戦法は四間飛車。

Twitter：@kk2170