Laravel Group By 异常记录

前段时间用Laravel 做项目，使用Group By时，总是会遇到的一个错误，具体异常信息如下：

Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column ‘database.table.id’; this is incompatible with sql_mode=only_full_group_by

这个错误很眼熟，其原因是对于聚合操作（如：sum、max、min等），如果在SELECT 中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中。

简而言之，就是SELECT 后面接的列必须被GROUP BY 后面接的列所包含。

可是同样的SQL，在数据库中是可以正常执行的，为什么在Laravel 下却总是会报错呢？

当时也是因为项目紧急，没具体深究其原因。

今天无意间看到一篇文章——Laravel使用group by报错的问题，正好把这个问题给讲明白了。

原来是因为开发者在Laravel 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.
说真的，我也忘了具体的细节了，可能是因为数据丢失排序的问题。

Laravel 8.x 版本，默认会启用该模式，启用时，会造成以下影响：

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. 最简单的方案，关闭该模式即可：

编辑/config/database.php：

1
2
3
4
5
6

'connections' => [
    'mysql' => [
        // ...
        'strict' => false,
    ],
],

2. 配置modes

编辑/config/database.php：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

'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',
        ],
    ],
],

不知为何，验证此方案时总是未生效。

3. full group by
如果不想关闭该模式，那就只有两种选择了：

1. Full Group By：SELECT 查询的字段包含所有的Group By 的字段中
2. ANY_VALUE：这是一个Mysql 的函数，使用它可以临时跳过一些错误

但需要注意的是，这种方案并不会“一劳永逸”，以后的每一次Group By 还是会面临相同的问题。

参考链接

• Laravel使用group by报错的问题
• query that worked in Laravel 5.2 gives me error in Laravel 5.3