MySQL Workbench で ONLY_FULL_GROUP_BY を無効にする方法

MySQL の ONLY_FULL_GROUP_BY を無効にする方法

このモードは、データの整合性を保つために導入されました。しかし、場合によっては、このモードが原因でエラーが発生することがあります。

ONLY_FULL_GROUP_BY を無効にする方法はいくつかあります。

SET GLOBAL sql_mode オプションを使用する

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

このコマンドは、ONLY_FULL_GROUP_BY を含むすべての SQL モードを無効にします。

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

このコマンドは、現在のセッションのみで ONLY_FULL_GROUP_BY を無効にします。

my.cnf ファイルを設定する

[mysqld]
sql-mode=""

クエリに SQL_NO_ONLY_FULL_GROUP_BY オプションを使用する

SELECT *
FROM table
GROUP BY column
ORDER BY column
SQL_NO_ONLY_FULL_GROUP_BY;

注意事項

ONLY_FULL_GROUP_BY を無効にすることで、データの整合性が損なわれる可能性があります。無効にする前に、その影響をよく理解する必要があります。

テーブル schema

CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `age` INT NOT NULL,
  `gender` ENUM('male', 'female') NOT NULL,
  PRIMARY KEY (`id`)
);

データ

INSERT INTO `users` (`name`, `age`, `gender`) VALUES
  ('John Doe', 30, 'male'),
  ('Jane Doe', 25, 'female'),
  ('John Smith', 40, 'male'),
  ('Jane Smith', 35, 'female');

ONLY_FULL_GROUP_BY を有効にした場合

SELECT
  gender,
  COUNT(*) AS count
FROM users
GROUP BY gender;

このクエリは、エラーが発生します。

ERROR 1055 (HY000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'users.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

SELECT
  gender,
  COUNT(*) AS count
FROM users
GROUP BY gender;

このクエリは、以下の結果を出力します。

gender | count
------- | --------
male   | 2
female | 2

この例では、ONLY_FULL_GROUP_BY を無効にすることで、GROUP BY 句で指定されていない列 name を SELECT 句で使用できるようになりました。

ONLY_FULL_GROUP_BY を無効にする他の方法

SQLyog を使用する

SQLyog は、MySQL の GUI 管理ツールです。このツールを使用して、ONLY_FULL_GROUP_BY を無効にすることができます。

手順

1. SQLyog を起動し、MySQL サーバーに接続します。
2. 左側のツリービューで、データベースを選択します。
3. 右側のペインで、"Server" タブを選択します。
4. "SQL Mode" セクションで、"ONLY_FULL_GROUP_BY" オプションのチェックボックスをオフにします。
5. "Apply" ボタンをクリックします。

1. "Server Configuration" セクションで、"SQL Mode" オプションの "Edit" ボタンをクリックします。

1. 以下のコマンドを実行します。

mysqldump --defaults-extra-file=/etc/my.cnf.d/my.cnf database_name > database.sql

my.cnf.d/my.cnf ファイルには、以下の内容を追加します。

[mysqld]
sql-mode=""

mysqld を再起動することで、ONLY_FULL_GROUP_BY 設定を更新することができます。

sudo service mysql restart