异常收集( 六 ) com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:

最新推荐文章于 2024-05-30 13:38:56 发布

原创最新推荐文章于 2024-05-30 13:38:56 发布 · 681 阅读

0 ·

CC 4.0 BY-SA版权

异常同时被 2 个专栏收录

7 篇文章

订阅专栏

MySQL

7 篇文章

订阅专栏

本文详细解析了在MySQL 5.7.5及更高版本中遇到的ONLY_FULL_GROUP_BY异常，通过调整SQL语句中的GROUP BY子句，成功解决了线上环境中因默认设置引发的数据查询问题。

首先,这个异常是数据SQL语法异常

先上异常截图:

{"msg":"服务器出现未知错误，请联系管理员！","data":"org.springframework.jdbc.BadSqlGrammarException: 
\n### Error querying database.  Cause: 

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 

Expression 
#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cmis.sys_clock_in.id_' which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by\n### 
The error may exist in class path resource [mybatis/SysClockInMapper.xml]\n### The error may involve com.sinosoft.sys.mapper.SysClockInMapper.getMonitorList-Inline\n### 
The error occurred while setting parameters\n### SQL: 
select count(0) from (select         id_ as 'id',         user_id_ as 'userId',         user_name_ as 'userName',         org_id_ as 'orgId',         org_name_ as 'orgName',         clock_time_ as 'clockTime',         clock_type_ as 'clockType'         from sys_clock_in         where         TO_DAYS( clock_time_ ) = TO_DAYS( NOW( ) )                             GROUP BY user_id_,org_id_         HAVING count( user_id_ ) = 1) tmp_count

\n### Cause: 
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
Expression
 #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cmis.sys_clock_in.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 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
 Expression
 #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cmis.sys_clock_in.id_' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by\n\tat org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93)\n\tat org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)\n\tat org.springframework.jdbc.support.AbstractFallbackSQLException

出现这个异常是以为今天同事提出的, 本地执行SQL没有问题,但是在线上就报错,查了mysql版本都是5.7的.

sql语句如下

SELECT
	count( 0 ) 
FROM
	(
	SELECT
		id_ AS 'id',
		user_id_ AS 'userId',
		user_name_ AS 'userName',
		org_id_ AS 'orgId',
		org_name_ AS 'orgName',
		clock_time_ AS 'clockTime',
		clock_type_ AS 'clockType' 
	FROM
		sys_clock_in 
	WHERE
		TO_DAYS( clock_time_ ) = TO_DAYS( NOW( ) ) 
	GROUP BY
		user_id_,
		org_id_ 
	HAVING
	count( user_id_ ) = 1 
	) tmp_count

经过漫长的排查后发现在MySQL的5.7.5之后有个设置是ONLY_FULL_GROUP_BY默认是开启状态.

默认的sql-mode里的ONLY_FULL_GROUP_BY字段导致不能直接查询group_by包裹的之外的字段.

类似上面的语句聚合函数默认的参数是主键column, 这里是id_. 只需要在group by 后添加参数id_ 就行了

SELECT
	count( 0 ) 
FROM
	(
	SELECT
		id_ AS 'id',
		user_id_ AS 'userId',
		user_name_ AS 'userName',
		org_id_ AS 'orgId',
		org_name_ AS 'orgName',
		clock_time_ AS 'clockTime',
		clock_type_ AS 'clockType' 
	FROM
		sys_clock_in 
	WHERE
		TO_DAYS( clock_time_ ) = TO_DAYS( NOW( ) ) 
	GROUP BY
        <!-- 在此处添加主键字段 -->
        id_,
		user_id_,
		org_id_ 
	HAVING
	count( user_id_ ) = 1 
	) tmp_count