mysql严格模式group by,解决Laravel开启数据库严格模式后 使用聚合函数或GROUP BY报错问题...

今天做需求的时候碰到一个很奇怪的问题，同样的代码和sql，在本地跑没有问题，上到测试服务器就报错了，先把报错贴出来

SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause (SQL: select sum(tc.real_total_fee) as sum_real_total_fee, sum(tc.arrival_fee) as sum_arrival_fee from `xxx` as `l` left join `xxx` as `m` on `l`.`legal_uid` = `m`.`legal_uid` left join `xxx`.`xxx` as `t` on `t`.`colour_sn` = `l`.`colour_sn` left join `xxx`.`xxx` as `tc` on `tc`.`colour_sn` = `l`.`colour_sn` left join `xxx`.`xxx` as `p` on `tc`.`payment_uuid` = `p`.`uuid` where ((`time_pay` between 1589696617 and 1590301417 and `l`.`legal_uid` in (xxxx)) and `type` = xxx) order by `tc`.`time_pay` asc)

报错信息

一些表的信息我就隐藏了，主要看报错：Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

原来laravel 5.3以后默认开启mysql严格模式(strict),在mysql在严格模式下, 并且开启了ONLY_FULL_GROUP_BY的情况下,我们使用聚合函数后没有group by或者SELECT的字段没有出现在group by语句中会报错！

那么如何解决呢？网上找了一下方法，大部分答案都说的是关闭了严格模式就不会报错。

如下Demo

public static function orders($phase)

{

return self::select(DB::raw("SUM(orders) as orders"),"type","users_id","name")

->groupBy("type","name")

->where("phase",$phase)

->get();

}

会出现如下错误，当然*号内的内容是不同的

Syntax error or access violation: 1055 '***' isn't in GROUP BY

解决办法

laravel出现上述错误是sql_mode设置导致的问题,修改config/database.php配置文件的strict => false即可关闭了严格模式:

...

'mysql' => [

'driver' => 'mysql',

'host' => env('DB_HOST', 'localhost'),

'port' => env('DB_PORT', '3306'),

'database' => env('DB_DATABASE', 'forge'),

'username' => env('DB_USERNAME', 'forge'),

'password' => env('DB_PASSWORD', ''),

'charset' => 'utf8',

'collation' => 'utf8_unicode_ci',

'prefix' => '',

'strict' => false,

'engine' => null,

]

...

因为需求紧急，并且暂时找不到更好的兼容方法，于是我只能先关闭严格模式，后续再想别的办法看能不能优化了，在此记录一下本次报错的处理方法