【原】MySQL报错“this is incompatible with sql_mode=only_full_group_by”

在新的linux服务器上安装了mysql5.7之后，项目运行时报错“this is incompatible with sql_mode=only_full_group_by”，详细错误信息如下：

java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jwcz.user.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 class path resource [mybatis/mapper/AccountMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT count(0) FROM account ra LEFT JOIN (SELECT * FROM account GROUP BY company_id) a ON a.company_id = ra.company_id LEFT JOIN company c ON c.id = ra.company_id
### Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jwcz.account.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 java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jwcz.account.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447)
	at com.sun.proxy.$Proxy97.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:231)
	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76)

错误提示信息给的很明确，就是在group by的时候的出错了，而且提到了一个ONLY_FULL_GROUP_BY的sqlmode，就是它导致了sql报错，查阅资料后发现，这个是mysql5.7提供的新特性。。。

ONLY_FULL_GROUP_BY是MySQL提供的一个sql_mode，通过这个sql_mode来提供SQL语句GROUP BY合法性的检查，在MySQL的sql_mode是非ONLY_FULL_GROUP_BY语义时。一条select语句，MySQL允许target list中输出的表达式是除聚集函数或group by column以外的表达式，这个表达式的值可能在经过group by操作后变成undefined，而对于语义限制都比较严谨的多家数据库，如SQLServer、Oracle、PostgreSql都不支持select target list中出现语义不明确的列，这样的语句在这些数据库中是会被报错的，所以从MySQL 5.7版本开始修正了这个语义，就是我们所说的ONLY_FULL_GROUP_BY语义。

使用命令查看一下安装的mysql的sql_mode：

select @@GLOBAL.sql_mode;

果然是有一个ONLY_FULL_GROUP_BY：

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

解决方案：

1、sql语句暂时性修改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=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

位置如下图所示，放在[mysqld]下面就行了

推荐使用第2种方式，因为第一种在重启了mysql之后，又会被重置掉！！！

mysql

看完文章，有任何疑问，请加入群聊一起交流！！！

扫码关注本站微信公众号，第一时间获取网站更新动态！

本文地址：https://www.jiweichengzhu.com/article/3b12157fcb3e4419a429c1287fa04954

关于转载：若站内作品和文章得到了你的青睐，欢迎各位朋友分享到个人站长或者朋友圈，但转载请注明文章出处和原文链接！

很赞哦！ ()