MySQL 5.7以降、ONLY_FULL_GROUP_BYは初期状態で有効であるため、これを除去するのみ。
確認方法は以下のコマンド。
SELECT @@global.sql_mode;
// 結果
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
my.cnfにて直接設定する場合、以下の通りで、「ONLY_FULL_GROUP_BY」を抜くだけ。
[mysqld]
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
laravelにて変更する場合、「config/database.php」にて、「modes」項目を追加する。
追加箇所は、「mysql」に追加となる。
「ONLY_FULL_GROUP_BY」をあえてコメントアウトしているため、この項目が除外されたことがわかる。
'modes' => [
// 'ONLY_FULL_GROUP_BY',
'STRICT_TRANS_TABLES',
'NO_ZERO_IN_DATE',
'NO_ZERO_DATE',
'ERROR_FOR_DIVISION_BY_ZERO',
'NO_ENGINE_SUBSTITUTION'
],
なお、MySQLとMariaDBでは若干設定内容が変わり、MariaDBでは「NO_AUTO_CREATE_USER」も追加可能。MySQLではこのオプションは廃止されたため、MariaDBのみとなる。
さて、こうした事象はどのような場合にて起こるかを次のSQLで試すこととする。
以下の例では表題のエラーとなるが、これはidが複数存在するため、グループ化してしまうとどのidを表示すれば良いかわからず、エラーとなる。
「ONLY_FULL_GROUP_BY」を設定した場合、グループ化された中での適当な値が返されるため、問題ないが、設定しない場合は集約された値を設定する必要があり、idではなくcntカラムを指定しなければエラーとなる。
select
id,
name,
count(*) as cnt
from
user
group by
name
ONLY_FULL_GROUP_BYの使い所としては、上記のようなnameなどでグループ化を行いたい場合や、集計した値のマックス値を取りたい場合などに活用が可能となる。
以下がその例となる。
select
id,
name,
MAX(total) as tmax
from
user
group by
name
この例では、同一名で一番高い値を取得することが可能。