Mysql 报错this is incompatible with sql_mode=only_full_group_by

一、前言

该问题出现于mysql5.7.5版本及以后

• Mysql文档中是这样描述的：HAVING条件或ORDER BY列表引用GROUP BY未在该子句中命名且在功能上不依赖于（由其唯一确定） GROUP BY列的非聚合列,拒绝查询

ONLY_FULL_GROUP_BY

Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

A MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. Before MySQL 5.7.5, enabling ONLY_FULL_GROUP_BY disables this extension, thus requiring the HAVING clause to be written using unaliased expressions. As of MySQL 5.7.5, this restriction is lifted so that the HAVING clause can refer to aliases regardless of whether ONLY_FULL_GROUP_BY is enabled.

For additional discussion and examples, see Section 12.20.3, “MySQL Handling of GROUP BY”.

• 原理层面

这个错误发生在mysql 5.7 版本及以上版本会出现的问题：

mysql 5.7版本默认的sql配置是:sql_mode=“ONLY_FULL_GROUP_BY”，这个配置严格执行了"SQL92标准"。

很多从5.6升级到5.7时，为了语法兼容，大部分都会选择调整sql_mode，使其保持跟5.6一致，为了尽量兼容程序。

• sql层面

在sql执行时，出现该原因：

简单来说就是：输出的结果是叫target list，就是select后面跟着的字段，还有一个地方group by column，就是

group by后面跟着的字段。由于开启了ONLY_FULL_GROUP_BY的设置，所以如果一个字段没有在target list

和group by字段中同时出现，或者是聚合函数的值的话，那么这条sql查询是被mysql认为非法的，会报错误。

前言参考博客：5分钟学会MySQL-this is incompatible with sql_mode=only_full_group_by错误解决方案

二、解决方案

1.执行sql语句修改sql_mode（不推荐，仅暂时修改，mysql服务重启后失效）

• 查看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

• 修改sql_mode后需重启java项目（若出现执行后不生效问题，排查sql_mode=后需要加上引号！！！）

set @@GLOBAL.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配置文件，手动配置sql_mode（推荐，一劳永逸）

• Windows 操作系统中在mysql安装的根目录下为 my.ini 文件（如果没有这个文件，就把my-default.ini 改成my.ini）
  在[mysqld]下面添加sql_mode：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

• Linux 操作系统中配置文件是my.cnf，一般会放在/etc/my.cnf，/etc/mysql/my.cnf

vi /etc/my.cnf

在[mysqld]下面添加sql_mode：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

• Docker部署的mysql请移步至：https://blog.csdn.net/llwutong/article/details/115768306?spm=1001.2014.3001.5501