Group by问题：this is incompatible with sql_mode=only_full_group_by

一、问题描述：

今天遇到个关于sql语句 group by的坑，这应该是第三次遇到，频率算高，便做个记录。

先看执行的sql：

SELECT
   cargo_id AS cargoId,
   cargo_name AS cargoName,
   SUM(left_count) AS allLeftCount,
   unit AS unit,
   SUM(count) AS allCount,
   weight_unit AS weightUnit
FROM cs_inventory_in
WHERE is_deleted = 0
   <if test="roomId != null">
      AND room_id = #{roomId}
   </if>
      AND status = 1 AND in_status = 1 AND left_count > 0
GROUP BY cargo_name
ORDER BY time_in ASC

执行时，报下边的错误：

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cs_inventory_in.cargo_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

---

二、问题解析

产生问题的原因：

系统启用的mysql数据库sql配置是：

<!-- 这个配置严格执行了"SQL92标准" -->
<!-- mysql 5.7及以上版本默认有这个配置 -->
sql_mode="ONLY_FULL_GROUP_BY......"

数据库版本查看，执行sql：

select version();

可以看到版本信息：

那怎么查看当前系统使用的数据库配置，是否包含“ONLY_FULL_GROUP_BY”呢？有两个方法：

1、执行查询语句：

SELECT @@SESSION.sql_mode;

如下所示，看到包含“ONLY_FULL_GROUP_BY”描述：

2、查看mysql安装目录中的my.ini文件：

看到文件中的sql_mode有相关描述;

这个配置为什么会有这个问题：

如果开启了ONLY_FULL_GROUP_BY的设置，字段没有在select后和group by字段中同时出现，或者不是聚合函数的值的话。那么这条sql查询是被mysql认为非法的，会报错误。

---

三、解决办法：

解决的办法也有两个，一个是全局修改，另一个是局部修改，二者的区别和设置方法是什么？接着往下看：

1、全局修改：

全局修改设置后，以后任何在该数据库下执行的sql，涉及到group by都不会产生这个问题。全局修改的方法有二：

（1）、配置文件修改，打开上述mysql安装目录下的my.ini文件，把sql_mode中的“ONLY_FULL_GROUP_BY”去掉，留下剩下的部分即可。

（2）、执行如下sql：

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

按上述的其中一个方法修改即可，修改后，需要重启数据库。重启后，再次执行下述sql，查看是否修改成功：

SELECT @@SESSION.sql_mode;

2、局部设置：

局部设置后，修改的sql语句不会有问题，但其它没有设置的sql会产生这个问题。从开法规范来说，比较推荐局部修改的方法。

具体的设置方式，给非group by字段、非聚合函数值设置any_value(）：

SELECT
   any_value(cargo_id) AS cargoId,
   cargo_name AS cargoName,
<!-- 类似下述的聚合函数值，不需要加any_value -->
   SUM(left_count) AS allLeftCount,
   <!-- 类似unit这样的字段，加上any_value后记得重命名，不然接口直接取用unit会取不到 -->
   any_value(unit) AS unit,
   SUM(count) AS allCount,
   any_value(weight_unit) AS weightUnit
FROM cs_inventory_in
WHERE is_deleted = 0
   <if test="roomId != null">
      AND room_id = #{roomId}
   </if>
      AND status = 1 AND in_status = 1 AND left_count > 0
GROUP BY cargo_name
<!-- 千万别忘了，如果在order by中的字段与group by中的字段不一致，那也需要加any_value -->
ORDER BY any_value(time_in) ASC

---

四、总结

既然mysql数据库版本5.7及以上就默认开启了“ONLY_FULL_GROUP_BY”配置，那就有它的道理。一个人的“觉悟”和人家整个团队比起来，这种经得起这么多年考验的团队结果，还是比较值得学习借鉴的。所以遇到这个问题，更推荐局部处理的方式。当然，如果个人造诣已有一定的深度，那当我没说，按届时的个人判断处理，没有任何贬义的说：自信即巅峰！