MySQLでgroup byなしでcountとorder byを同時に使うとエラー
MySQLとLaravelの話です。
MySQLのSQL_MODE
にONLY_FULL_GROUP_BY
が指定されていると、group by無しでcountとorder byを同時に使うと以下のエラーが発生します。
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
MySQLのバージョン
Server version: 5.6.36 MySQL Community Server (GPL)
5.6.36
以前は確認していません5.7.17
では発生しませんでした
再現
-- サンプルのテーブル mysql> desc foo; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) -- sql_modeを一旦未指定にする mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) -- count と order byを同時に使っても問題なし mysql> select count(*) from foo order by id; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) -- sql_modeにonly_full_group_byを指定 mysql> set session sql_mode='only_full_group_by'; Query OK, 0 rows affected (0.00 sec) mysql> select @@session.sql_mode; +--------------------+ | @@session.sql_mode | +--------------------+ | ONLY_FULL_GROUP_BY | +--------------------+ 1 row in set (0.00 sec) -- count と order byを同時に使うとエラー mysql> select count(*) from foo order by id; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
通常はそんなSQL書かないでしょうが、今回Laravelを使っていてちょっとハマりました。
クエリオブジェクトを使いまわしたかった
以下のような感じで検索条件を構築したクエリオブジェクトに対して、count
と実際の検索を別々に発行したいケースです。
<?php public function something() { // \DB::table('foo')->where()...的な感じでクエリオブジェクトを作成 $query = $this->createQuery(); // 総件数を取得 $count = $query->count(); // 実際のレコードを取得 $records = $query->get(); }
実際のレコードを取得
するために$query
にはすでにorder_by
の指定が入っています。
そのため上記のエラーが発生してしまいました。
ハマったのはログに出力されたクエリを直接MySQLに実行しても通るのに、PHP経由だとエラーになったためです。
(MySQL側ではsql_modeにonly_full_group_byを付けていなかったのでエラーにならない)
config/database.phpのstrict => true
config/database.php
にはstrict
という項目があり、Laravel5.3以降ではtrueがデフォルト値のようです。
<?php 'mysql' => [ // (略) 'strict' => true, // (略) ],
これがtrueだと、MySQL接続時に以下のSQLが走ってsql_modeが設定されます。 https://github.com/laravel/framework/blob/5.4/src/Illuminate/Database/Connectors/MySqlConnector.php#L178
set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
ということで、MySQL自体のsql_modeは上書きされます。
対応策
同じようなIssueではstrict => false
にすることが提案されています。
5.3 Query Builder count() Sql error - PR proposal
それが嫌な場合はcountを実行してからorder byを指定するとかが回避策になると思います。