技术日志 - Laravel 中 sql 查询使用 group by 报错问题。

Marrigan 的个人博客 / 3 / 1 / 创建于 4年前 / 更新于 4年前

今天在使用 laravel DB 查询的时候报错错误如下：

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

Sql 如下：

  $data = DB::table('*********')->groupBy(DB::raw("date_format(order_addtime,'%Y-%m-%d')"))->get();

报错原因：

在 laravel 项目中 config\database.php 配置文件夹 "mysql" 数组中
默认 'strict' => true，启用了严格模式。将该参数改为 false即可。

解决

     'strict' => false,

TODO

~~~ 1. 了解什么是严格模式，以及严格模式下为什么 group 会报错，如何使用才不会触发错误。2020-05-25 ~~~

2020-06-03 补充内容解决上面 OTDO

关于 mysql 严格模式

简单说明下 mysql 下一些 SQL MODE

ONLY_FULL_GROUP_BY：

这就是上文报错的原因，在select、having、group by列表里引用的列必须在group by列表中，否则报错。

NO_AUTO_VALUE_ON_ZERO：

STRICT_TRANS_TABLES：

    严格模式控制 MySQL 如何处理非法或丢失的输入值的 SQL 。有几种原因可以使一个值为非法。例如，数据类型错误或超出范围。当新插入的行不包含某列的没有显示定义DEFAULT子句的值，则该值被丢失。

    对于事务表，当启用 STRICT_ALL_TABLES 或 STRICT_TRANS_TABLES 模式时，如果语句中有非法或丢失值，则会出现错误。SQL语句被回滚。

    对于非事务表，STRICT_TRANS_TABLES，MySQL 将非法值转换为最接近该列的合法值并插入调整后的值。如果值丢失，MySQL 在列中插入隐式 默认值。在任何情况下，MySQL都会生成警告而不是给出错误并继续执行语句。

    如果你不使用严格模式(即不启用STRICT_TRANS_TABLES或STRICT_ALL_TABLES模式)，对于非法或丢失的值，MySQL将插入调整后的值并给出警告。在严格模式，你可以通过 INSERT IGNORE 或 UPDATE IGNORE 来实现。

NO_ZERO_IN_DATE：

    在严格模式，不接受月或日部分为0的日期，对年不限制。如果使用IGNORE选项，我们为类似的日期插入’0000-00-00’。在非严格模式，可以接受该日期，但会生成警告。

NO_ZERO_DATE：

    在严格模式，不要将’0000-00-00’做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告。

ERROR_FOR_DIVISION_BY_ZERO：

    在严格模式，在 INSERT 或 UPDATE 过程中，如果被零除(或[MOD(X，0)](https://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_mod))，则产生错误(否则为警告)。如果未给出该模式，被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被零除警告，但操作结果为NULL。

NO_AUTO_CREATE_USER：

    在严格模式下，防止GRANT自动创建新用户，除非还指定了密码。

NO_ENGINE_SUBSTITUTION：

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

PIPES_AS_CONCAT：

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

ANSI_QUOTES：

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

mysql的模式整理到这里，那 Laeravel 的 'strict' => true 具体操作了哪些值呢？

可以看到在 MysqlConnector 中的 striceMethon 中设置了严格模式下的具体 sql mode

技术日志 - Laravel 中 sql 查询使用 group by 报错问题。

set session sql_mode='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'

// 8.0.11 之后
ONLY_FULL_GROUP_BY,
STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_ENGINE_SUBSTITUTION
// 8.0.11 以前
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'