MySQL中的only_full_group_by模式

一、背景

ERROR 1055 (42000): Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘postscan.verifyDelayLog.auditor’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

以上报错信息为写 SQL 时,使用了 GROUP BY 导致报错,这是因为在 MySQL 5.7 版本中,默认开启了 only_full_group_by 模式,这也导致很多代码在本地跑得好好的,一上线就报错

对于 GROUP BY 聚合操作,如果在 SELECT 中的列,没有在 GROUP BY 中出现,那么这个 SQL 是不合法的,因为列不在 GROUP BY 句中,所以对于设置了这个 mode 的数据库,在使用 GROUP BY 的时候,就要用 MAX(),SUM(),ANT_VALUE() 这种聚合函数,才能完成 GROUP BY 的聚合操作。

二、解决方法

  • 1、查看数据库的 sql_mode
select @@global.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

我们去掉 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

但是这样只能解决后面新增的数据库有效,已经存在的数据库无效

还需要在已经存在的每个库中执行:

set sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
  • 2、修改 MySQL 的配置文件 /etc/my.cnf,添加如下配置
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

注意:必须添加在 [mysqld] 下面,添加到文件的最后一行无效

然后重启 MySQL 即可

如您在阅读中发现不足,欢迎留言!!!

©️2020 CSDN 皮肤主题: 终极编程指南 设计师:CSDN官方博客 返回首页