mysql报错 this is incompatible with sql_mode=only_full_group_by

日常问题记录-问题1

项目在执行GROUP BY时报错

Error querying database. Cause: java.sql.SQLSyntaxErrorException: Expression #3 of SELECT list is not in GROUP 
BY clause and contains nonaggregated column 'hzh.t.dept_id' which is not functionally dependent on columns in 
GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ### The error may exist in 
cc/jz/cloud/hzh/enrollment/mapper/InsuranceMapper.java (best guess) ### The error may involve 
defaultParameterMap ### The error occurred while setting parameters ### SQL: SELECT 
sum(enrollment_members) AS totalNumber, sum(enrollment_amount) AS totalAmount, dept_id AS deptId, 
any_value(ensure_type) AS ensureType FROM enrollment_insurance t WHERE t.delete_mark = 0 AND (t.status = ? 
AND t.effective_date BETWEEN ? AND ? AND t.bind_dept_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) GROUP BY 
t.bind_dept_id, t.ensure_type ### Cause: java.sql.SQLSyntaxErrorException: Expression #3 of SELECT list is not in 
GROUP BY clause and contains nonaggregated column 'hzh.t.dept_id' which is not functionally dependent on 
columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ; bad SQL grammar []; nested 
exception is

问题原因

版本特性：该问题出现在 MySQL 5.7.5 版本及以上，此版本默认的 sql 配置为 sql_mode=“only_full_group_by”，严格执行 “sql92 标准”。
SQL 原理：在开启 only_full_group_by 设置后，若 select 的字段不在 group by 中，且未使用聚合函数（sum、avg、max、min 等），则这条 sql 查询会被 MySQL 认为非法。

临时解决方案

第一步 进入navicat 或者其他连接工具

第二步 执行以下语句

# 修改全局配置 sql_mode 
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
# 修改session配置 sql_mode 
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

永久解决方案

第一步 找到mysql 配置文件 my.cnf （linux系统）或者 my.ini (windows)系统

linux 系统常见路径：
/etc/my.cnf、/etc/mysql/my.cnf、/usr/local/etc/my.cnf、~/.my.cnf
windows 系统常见路径：
C:\Program Files\MySQL\MySQL Server [版本号]\my.ini或C:\Program Files\MySQL\MySQL Server [版本号]\my.cnf

第二步 加入配置在[mysqld]下方

sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’

加入后如下图所示：

[mysqld]
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
datadir=/var/lib/mysql
symbolic-links=0
max_connections=10000
lower_case_table_names='1'
default_authentication_plugin=mysql_native_password

第三步 重启mysql

linux 重启mysql

sudo service mysql restart

windows 重启mysql
1、使用服务管理器

按下 “Win + R” 键，打开 “运行” 对话框。
输入 “services.msc” 并按回车键，打开服务管理器。
在服务列表中找到 “MySQL” 或 “MySQL57” 等具体 MySQL 服务名称（取决于 MySQL 版本）。
右键点击服务名称，然后选择 “重启” 即可。

2、使用命令行

打开命令提示符（CMD），可以以管理员身份运行。
输入 “net stop MySQL” 命令来停止 MySQL 服务。
输入 “net start MySQL” 命令来启动 MySQL 服务。