# Mysql GROUP BY エラー回避するために sql_mode 変更

Type: mysqli_sql_exception

Message: Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.d.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT list is not in GROUP BY clause and contains nonaggregated column 'user.admin_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by on

GROUP BY の作業 SQL で上記のエラーが発生しました。sql_mode=only_full_group_byについて調べましたが、5.7 以降ではデフォルトで sql_mode=ONLY_FULL_GROUP_BYが設定され、group by 指定していない項目を select するとエラーになります。

MySQL データ変更ステートメントで無効な値や欠落した値を抑えるために厳密な sql mode 指定されています。

sql_mode のデフォルト

# sql_mode 確認方法

show variables

show variables like 'sql_mode';

結果

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Global 変数を確認

select @@global.sql_mode;

結果

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

セッション変数を確認

select @@session.sql_mode;

結果

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# sql_mode=only_full_group_by 解除

set sql_mode ステートメント変更

set global sql_mode='NO_ENGINE_SUBSTITUTION';

# my.cnf 変更

my.cnf 場所を調べる

mysql --help | grep my.cnf

結果
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /opt/homebrew/etc/my.cnf ~/.my.cnf

/opt/homebrew/etc/my.cnf

my.cnf の sql_mode 変更

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

# Docker コンテナ起動時に MySQL sql_mode 設定

docker run --rm -e MYSQL_ROOT_PASSWORD=root  mysql:5.7 --sql-mode=NO_ENGINE_SUBSTITUTION

docker-compose.yml ファイルでコマンド設定

version: "3.7"
services:
  db:
    image: mysql:5.7
    command:
      - --sql-mode=NO_ENGINE_SUBSTITUTION
    environment:
      MYSQL_ROOT_PASSWORD: root

# Mysql バージョンの話

2021 年 2 月 5 日に Amazon RDS は、MySQL メジャーバージョン 5.6 をサポート終了しました。
2023 年 10 月に Mysql5.7 はサポート終了予定で、5.7 と比較して 2 倍高いパフォーマンスのバージョン 8.0 が勧められています。