Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column

"Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'workflow.s.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

今天偶然间出现了上面的一个错误,提示说查询的字段不在group by的子句中,因为sql_mode是only_full_group_by。

查看mysql本地的sql_mode:show variables like 'sql_mode';

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column

确实第一个是only_full_group_by,这个模式说明对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么将认为这个SQL是不合法的,因为列不在GROUP BY从句中。

查看mybatis的xml文件,找到对应sql

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column

问题就是出在查询的s.*这个地方,仔细一想,如果group by content,一个content对应多条数据,那么这个select s.*到底是要展示哪一条的数据呢?所以说就不允许select的列没有出现在group by的子句中。

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column

这样就不会报错了,哎呀,粗心!