昨天在项目中有一个sql查询语句使用到了group by ,在开发环境和测试环境都没有问题,结果到了线上接口就宕掉了,查看了一下报了 this is incompatible with sql_mode=only_full_group_by这个错误。
这个错误对于我来说还算是比较熟悉了,因为这是第二次踩坑了,第一次在半年之前了,当时是修改的mysql的配置,然后就可以了,但是这次是线上的,领导不允许修改线上的环境配置,就只能修改sql语句了。
mysql 5.7中使用group by 不做处理会报如下错误: this is incompatible with sql_mode=only_full_group_by,这是因为mysql 默认启用了 ONLY_FULL_GROUP_BY。可查看sql_model配置参数。SELECT @@GLOBAL.sql_mode;
解决方案:
1.关闭 ONLY_FULL_GROUP_BY,可通过客户端连接工具使用:set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 进行关闭,但是本人在客户端使用该命令关闭后连接工具是可以使用group by 语句,但是项目中仍不可用。
2.修改mysql配置文件 my.ini (如果你们mysql 没有这个文件,就把my-default.ini 改成my.ini),
在 [mysqld]和[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';
3.利用ANY_VALUE()这个函数,例如:
@Select("<script>SELECT " +
" any_value(gmt_create) as gmt_create,source_name,any_value(source_id) as source_id ,any_value(SUM(count_sum)) as count_sum " +
" FROM " +
" inf_statistic_month " +
" WHERE 1=1 " +
"<if test='sourceId !=null'> " +
" and source_id = #{sourceId} "+
"</if>" +
" And is_delete = 0 " +
"<if test='startDate != null and endDate != null'>" +
" AND gmt_create BETWEEN #{startDate} AND #{endDate} " +
"</if>" +
" GROUP BY source_name , curr_year"+
"<if test='orderBy !=null'> " +
" ORDER BY ${orderBy} "+
"</if>" +
" limit #{offset},#{limit}</script>")
请注意any_value 的用法: 对不参与分组的查询出来的字段需要使用any_value()函数,
" any_value(gmt_create) as gmt_create,source_name,any_value(source_id) as source_id ,any_value(SUM(count_sum)) as count_sum "
使用any_value() 后面要是用 as 进行字段重命名, 否则会报错!!!