关于[this is incompatible with sql_mode=only_full_group_by]的解决方案

先看报的SQL错误

在经过一次MySQL数据库版本更换,项目的SQL出现了问题

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

上面错误提示:
非聚合列 ‘XXX’ 在功能上不依赖于GROUP BY子句中的列;这与sql_mode=only_full_group_by不兼容

WHAT?only_full_group_by模式是什么?

在了解这个模式之前,先看下报错的SQL是如何写的(表名为测试):

SELECT
 t1.corp_id AS corpId,
 t1.org_id AS orgId,
 t2.sku_id AS skuId
FROM
 test1 t1,
 test2 t2
WHERE
 t1.yn = 1
AND t2.yn = 1
AND t1.bc = sku.obc
AND t1.ts BETWEEN '2019-11-24'
AND '2019-12-24'
GROUP BY
 t2.sku_id,
 t1.org_id

从上面的SQL可以看出,在GROUP BY下有sku_id和org_id两个字段,但是上面SELECT查询列表中还有corp_id这个字段,这样的语句在ORACLE里肯定是不行的,之前测试过,但是在MySQL执行一切正常,是可以执行的
此时,我的数据库版本为5.5.38-log

经过测试,查询一切正常:

那么当换为5.7.20的数据库版本呢?

报错了!

[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause 
and contains nonaggregated column 't1.corp_id' which is not functionally 
dependent on columns in GROUP BY clause; this is incompatible 
with sql_mode=only_full_group_by

版本不同影响结果???

来看看 sql_mode=only_full_group_by

上面提到 sql_mode=only_full_group_by 这个错误提示,猜测应该是这个字段影响了最终结果
首先查看MySQL 5.5.38版本这个字段的值:
这个值的Value为空

那么MySQL 5.7.20版本呢:
这个值的Value中很明显出现了 ONLY_FULL_GROUP_BY

在 ONLY_FULL_GROUP_BY 模式下编程,编写查询时是不允许查询列出现非group by的字段的,这使我们编写SQL时更加严谨,但是在一些情况下,我们的确需要这样查询
举个例子:额外的列与group by中存在的某一列存在绝对对应关系,就可以直接带出来.

这时,如果我们想这样查询,有两种解决方案:

从MySQL5.7 开始, 默认开启ONLY_FULL_GROUP_BY 模式

方案一

我们把这个模式手动取消:

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

方案二(推荐)

从Mysql 5.7开始，可以使用ANY_VALUE（column）函数来改进查询
将上面SQL语句改造为:

SELECT
 ANY_VALUE(t1.corp_id) AS corpId,
 t1.org_id AS orgId,
 t2.sku_id AS skuId
FROM
 test1 t1,
 test2 t2
WHERE
 t1.yn = 1
AND t2.yn = 1
AND t1.bc = sku.obc
AND t1.ts BETWEEN '2019-11-24'
AND '2019-12-24'
GROUP BY
 t2.sku_id,
 t1.org_id

查询成功!

至此,这个错误解决