首先,这个异常是数据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