, , , ,

Laravel等にて、Syntax error or access violation: 1055 ‘database.table.id’ isn’t in GROUP BY〜というエラーが出た場合の対処

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

この例では、同一名で一番高い値を取得することが可能。