MySQL 5.7.5 分组查询不再兼容

种豆南山下

背景

什么是 ONLY_FULL_GROUP_BY ？再分析 MySQL 分组查询不兼容的特性之前，我们先看2组SQL笔试题。
笔试题 1

使用一段SQL查询每个月用户的支付次数大于3次且每月累计充值金额大于10000元的用户交易记录。下面是一张记录用户每天的交易流水信息表，包括如下交易数据:

user_id	data_dt	trans_type	trans_amt	trans_dc
u_001	20191201	支付	20000	支出
u_001	20191203	充值	30010	收入
u_001	20191203	提现	50000	支出
u_001	20191208	支付	20000	支出
u_001	20191210	充值	30010	收入
u_001	20191220	提现	50000	支出
u_002	20191202	支付	20000	支出
u_002	20191202	转入	30010	收入
u_002	20191230	充值	50000	收入
u_003	20200110	支付	60.68	支出
u_004	20200111	支付	90.05	支出
u_004	20200114	充值	100.1	收入
u_005	20200101	还款	30010	支出

查询返回的结果集为：

user_id	data_month
u_001	201912
u_001	202001
u_002	201912

MySQL 5.7  解答
select user_id, left(data_dt, 6) as months from t_trade
where trans_type='充值'
group by months having count(*)>3 and sum(trans_amt)>10000;但是，在 MySQL 8.0 执行上述语句报错，详细的错误信息如下：
### Error
### Expression #1 of SELECT list is not in GROUP BY clause
### and contains nonaggregated column 'test.t_trade.user_id'
### which is not functionally dependent on columns in GROUP BY clause;
### this is incompatible with sql_mode=only_full_group_by笔试题 2

使用一段SQL查询每个同学名下所有课程都大于等于80分的同学姓名。(提示：不一定只有三门课程。)  

student_name	class_name	score_value
张三	语文	80
张三	数学	78
张三	英语	60
王五	语文	82
王五	数学	92
王五	英语	88

MySQL 5.7 解答
select student_name,score_value from t_score
group by student_name,class_name having score_value>80;但是，在 MySQL 8.0 执行上述语句报错，详细的错误信息如下：
### Error:
### 1055 - Expression #2 of SELECT list is not in GROUP BY clause
### and contains nonaggregated column 'test.t_score.score_value'
### which is not functionally dependent on columns in GROUP BY clause;
### this is incompatible with sql_mode=only_full_group_by, Time: 0.000000s上述2道笔试题，作者在 MySQL 5.7 和 MySQL 8.0 执行的结果不一致。MySQL 5.7 可以正常执行，并且得到符合预期的结果集。但是，相同的SQL语句在 MySQL 8.0 执行报错，错误信息基本一致。通过提取错误信息中的关键字 sql_mode=only_full_group_by 在 MySQL 官网终于找到答案。接下来我们研究一下 ONLY_FULL_GROUP_BY 特性。MySQL 官方文档显示在 MySQL 5.7.5 不兼容的变更列表中有几项关于SQL_MODE的参数。例如：STRICT_TRANS_TABLES 默认开启, ONLY_FULL_GROUP_BY 默认开启。ONLY_FULL_GROUP_BY 参数的作用是什么呢？数据库打开该选项，数据库拒绝执行 查询列表、HAVING条件、ORDER BY列表中禁止引用非聚合字段的查询语句。所谓非聚合字段是指这些列既不在GROUP BY子句中指定，也没有在功能上依赖GROUP BY列。MySQL作为标准SQL的扩展是允许HAVING子句引用查询列表中的别名，无论 ONLY_FULL_GROUP 是否开启。
当你发现新版本的MySQL已经启用ONLY_FULL_GROUP_BY导致查询语句执行报错，如何解决上述问题呢？
MySQL 官方给出两种解决方案：

• 修改查询语句，让查询列表中的非聚合列在功能上依赖GROUP BY列，或者使用 ANY_VALUE() 引用非聚合列；
  
• 如果无法修改查询语句，请在数据库服务器SQL_MODE系统变量中不要设置 ONLY_FULL_GROUP_BY 参数；
  

日常测试，我们可以在会话级别的 SQL_MODE 参数中删除 ONLY_FULL_GROUP_BY 参数。
SET sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE';该语句在SQL会话窗口中设置完毕，上文的 2组SQL笔试题都可以正常执行并得到符合预期的结果集。
References

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_only_full_group_by
https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value
10.3 Changes in MySQL 5.7
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
https://dev.mysql.com/blog-archive/mysql-5-7-only_full_group_by-improved-recognizing-functional-dependencies-enabled-by-default/

虎虎男孩

思考：为什么 MySQL 5.7.5 默认开启 ONLY_FULL_GROUP_BY 呢？