问题描述:
线上数据拷贝到本地数据库代码执行数据查询时,在一个涉及到group_by语句时,报出以下错误:
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'table.a.data' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
重点在于sql_mode=only_full_group_by,在navicat上面执行
-- 查询数据库版本及sql_mode
SELECT VERSION(),@@sql_mode;
结果显示
解决方案
在navicat(或者命令行通过-p-u登陆mysql)执行以下语句:
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
注意⚠️:以上命令虽然是即时生效,但是仅在当前服务器(mysql服务)重启前生效,即:服务器重启后,相关设置失效,需要重新配置
永久解决方案:
在对应环境下的mysql对应配置文件【mysqlId】下添加:
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
注意⚠️:
1、对应环境下的对应配置文件:
mysql.conf
my.cnf
my.ini
2、不同环境下不同版本的mysql(通常为5.7以上的版本)的sql_mode值不同,建议根据自己的版本号查询如何去除only_full_group_by模式
原理
待补充