问题

最近在用Laravel5.4做项目，使用Eloquent ORM中group by对数据进行分组查询时会报错。报错如下：

SQLSTATE[42000]: Syntax error or access violation: 1055 'field' isn't in GROUP BY

原来是开发者在5.3版本后增加一个数据库的strict模式，其中一个开发者对于增加这个模式的看法很有意思：

Adam14Four ：
To be completely honest, I don't remember exactly what the details were, but it was some sort of data-loss problem.
说真的，我也忘了具体的细节了，可能是因为数据丢失排序的问题。

这个模式还会出现以下问题：

fernandobandeira：
1 - Add all columns to group by.
group by需要所有的列。
2 - Won't be able to use date's such as 0000-00-00 00:00:00.
时间不能使用0000-00-00 00:00:00的数据。
3 - Fields like boolean will throw fatal if you pass something that isn't a boolean value, like 1, before it would convert it to true and save, with strict it fails.
字段如果是boolean类型，但是传入一个非boolean如「1」将会抛出一个致命错误。在非strict模式下会自动转换成true并保存。
4 - You'll get an error if you divide a field by 0 (or another field that has 0 as value).
如果一个字段除以0将会得到一个错误（或者其他有0值的字段）

解决办法

1.「full group by」

顾名思义：select查询的字段包含所有的group by的字段

2. 增加「strict」键为false

在/config/database.php增加

'connections' => [
    'mysql' => [
            ...
        'strict' => false,//增加此行
    ],
],

3 /config/database.php 配置modes

'connections' => [
    'mysql' => [
            ...
            ...
            ...
        'modes' => [
            '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',
        ],
    ],
],

PS:modes中英文对照

参考

https://github.com/laravel/framework/issues/14997#issuecomment-242129087
https://*.com/questions/25800411/mysql-isnt-in-group-by#answer-25800677
http://xstarcd.github.io/wiki/MySQL/MySQL-sql-mode.html