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

今天在使用 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_TABLESSTRICT_TRANS_TABLES 模式时,如果语句中有非法或丢失值,则会出现错误。SQL语句被回滚。
    
        对于非事务表,STRICT_TRANS_TABLES,MySQL 将非法值转换为最接近该列的合法值并插入调整后的值。如果值丢失,MySQL 在列中插入隐式 默认值。在任何情况下,MySQL都会生成警告而不是给出错误并继续执行语句。
    
        如果你不使用严格模式(即不启用STRICT_TRANS_TABLESSTRICT_ALL_TABLES模式),对于非法或丢失的值,MySQL将插入调整后的值并给出警告。在严格模式,你可以通过 INSERT IGNOREUPDATE IGNORE 来实现。
  • NO_ZERO_IN_DATE:

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

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

        在严格模式,在 INSERTUPDATE 过程中,如果被零除([MOD(X0)](https://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_mod)),则产生错误(否则为警告)。如果未给出该模式,被零除时MySQL返回NULL。如果用到INSERT IGNOREUPDATE 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'

可以看到 laravel 的 严格模式并不等于 sql mode 的 STRICT_TRANS_TABLES ,他是sql mode的一个集合。

严格模式下,对于兼容也好,代码检测也好,代码规范都有一定好处,所以尽量使用严格模式吧。

TODO:尝试使用严格模式 2020-06-03

文章参考 :my.oschina.net/u/3023401/blog/1794...
blog.51cto.com/lee90/1826180

本作品采用《CC 协议》,转载必须注明作者和本文链接

talk is cheap,show me the code!

Marrigan
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
讨论数量: 1

select 后面的查询字段必须在group by后面。

1个月前 评论
Marrigan (楼主) 6天前

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!