Laravel中使用group by报错的问题

问题

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

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

原来是开发者在5.3版本后增加一个数据库的strict模式.

这个模式您必须： 

1 - 添加要查询的所有字段到group by后。 

2 - 将无法使用日期，例如 0000-00-00 00:00:00 

3 - 字段如果是boolean类型，但是传入一个非boolean如「1」将会抛出一个致命错误。在非strict模式下会自动转换成true并保存

4 - 如果将字段除以 0（或另一个值为 0 的字段），则会出现错误。

解决办法

  • 1、「full group by」

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

  • 2、增加「strict」键为false

在/config/database.php增加

'connections' => [
    'mysql' => [
            ...
        'strict' => false,//增加此行,如果是true,会在查询时添加 ONLY_FULL_GROUP_BY
    ],
],
  • 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',
        ],
    ],
],

注意，在以前版本的 Laravel 中，在创建时间戳时，迁移将默认值 0000-00-00 00:00:00 添加到字段中，因此请记住检查是否在从旧版本升级后启用此模式。

以上模式说明

ONLY_FULL_GROUP_BY：

对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

NO_AUTO_VALUE_ON_ZERO：

该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

STRICT_TRANS_TABLES：

在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制

NO_ZERO_IN_DATE：

在严格模式下，不允许日期和月份为零

NO_ZERO_DATE：

设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

ERROR_FOR_DIVISION_BY_ZERO：

在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL

NO_AUTO_CREATE_USER：

禁止GRANT创建密码为空的用户

NO_ENGINE_SUBSTITUTION：

如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

PIPES_AS_CONCAT：

将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似

ANSI_QUOTES：

启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符