MySQL5.7のONLY_FULL_GROUP_BYをOFFせずに重複レコードを除外する方法
目的:
ONLY_FULL_GROUP_BYをOFFにせずに、重複レコードを除外してレコードを取得する。
一行まとめ:
group byしないcolumnをselectに含めるには、MAX()もしくはMIN()を使う。
MySQLのバージョンを5.6から5.7に変更して、既存のソースをテストしていたら、 ONLY_FULL_GROUP_BYがONになっていることが原因のエラーが発生。
同様の問題について、ぐぐったら結構ヒットしたんだけど、my.cnfでONLY_FULL_GROUP_BYをOFFにすればOK的なこと書いてあるのがほとんど。
今後のことも考えて、ONLY_FULL_GROUP_BYをOFFにせずに、既存コードと同様に動くようにソースコードを修正してみます。
例として、以下のようなテーブルを考えます。
テーブル名 hoge id : int primary autoincrement name : varchar(255)
MySQL5.7において以下のようなクエリーを発行すると、
SELECT id FROM hoge GROUP BY name;
こんなエラーが返って来ます。
SELECT list is not in GROUP BY clause and contains nonaggregated column 'hoge.id' which is not functionally dependent on columns in GROUP BY clause;
で、こちらが修正後のクエリー
SELECT (MAX(id)) AS `id` FROM hoge GROUP BY name;
ここでポイントはMAX(id)。
ONLY_FULL_GROUP_BYがONになっていると、 MySQLはGroupByしていないカラム(今回はID)をSelectするときに、 どのIDを取得したらよいかMySQLがわからないためエラーとなっています(たぶん)。
重複時にどの値を使うかをMySQLに伝えてあげればいいので、状況に応じて、MAX()やMIN()を使い分ければ良いと思います。
あとはこれをサブクエリに使うなり、PHPの配列に入れてWhere INするなりすれば既存のコードを置き換えられます。
お疲れ様でした。