this is incompatible with sql_mode=only_full_group_by MySql报错

喜欢ヅ旅行 2022-03-26 02:14 347阅读 0赞

昨天在项目中有一个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 进行字段重命名， 否则会报错！！！