mysql ONLY_FULL_GROUP_BY

背景

 select user_id, max(create_time) as create_time,is_success
 from user_login_log
 group by user_id

上面的sql与上篇博文一致，由于之前接触过其它数据库，比如sqlserver，oracle，初看这条语句时，我认为它是有问题的，因为is_success并不在group by子句中。
直到今天，在dba 列出的注意事项上看到这么一句话，Mysql数据库需要保证sql_mode的配置正确。需要注意 ONLY_FULL_GROUP_BY 这个选项，如果有这个配置，那么如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的。

查看mysql的官方文档，ONLY_FULL_GROUP_BY是mysql5.7.5以上版本默认设置的sql_mode。在sql-92标准中，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的。而在mysql之前的版本中，并没有对此进行检查，在5.7.5版本后，mysql增加了此语义。

调试

通过以下语句，查看数据库设置的sql_mode

SELECT @@GLOBAL.sql_mode;
或
SELECT @@SESSION.sql_mode;

结果显示如下

在当前的数据库中，sql_mode中确实去掉了“ONLY_FULL_GROUP_BY”这一选项。

如果在sql_mode中增加ONLY_FULL_GROUP_BY语义后，再执行文章开头的sql，则会报错

SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

错误如下：

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

对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。

另外，除了select子句中的列，HAVING condition, or ORDER BY list 也有类似的问题。

因此考虑mysql版本升级的兼容性，需要暂时在sql_mode移除此模式。
但是如果因为移除模式，导致没有检查select 子句中的列是否明确取值，也可能会造成一定问题。

解决方式

在开启ONLY_FULL_GROUP_BY后，可以通过给列加上聚合函数或其他函数来保证sql运行成功，例如：

#当不关心分组数据取自哪一行时，可以使用any_value
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

其他影响与优化

在MySQL 5.7.5及更高版本中，ONLY_FULL_GROUP_BY 也会影响到同时使用DISTINCT，ORDER BY的查询。

SELECT DISTINCT is_success, create_time FROM user_login_log ORDER BY user_id; 

当order by 子句不在select 字句中，会报如下错误：

[Err] 3065 - Expression #1 of ORDER BY clause is not in SELECT list, references column 'mgr.user_login_log.user_id' which is not in SELECT list; this is incompatible with DISTINCT

先对结果进行排序，则需先消除重复项。但如果前两列出现了重复数据，且第三列数据不同，则应该保留哪行数据呢？如果任意进行选择，将反过来影响第三列的排序。因此为了防止这种问题，当满足order by子句 取自select 列表中列时或order by 子句引用的列取自select 列表中列时，才能认为该语句时合法的。

对标准SQL的另一个扩展，则是允许在HAVING子句中引用select列表中的别名

SELECT name, COUNT(name) FROM orders
  GROUP BY name
  HAVING COUNT(name) = 1;

 SELECT name, COUNT(name) AS c FROM orders
  GROUP BY name
HAVING c = 1;

同样的，mysql对group by 中的非聚集函数也作了支持。
在标准sql中，如果查询语句中的target list, having condition 或者order by list里引用了的表达式不是聚集函数，语句将士不合法的。但是在mysql中，只要和group by list中的表达式严格匹配，该语句也是合法的，如下：

SELECT id, FLOOR(value/100), id+FLOOR(value/100)
  FROM tbl_name
GROUP BY id, FLOOR(value/100);

在sqlserver中会报错：

Err] 42000 - [SQL Server]选择列表中的列 'tbl_name.value' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

类似的，对别名也作了支持

SELECT id, FLOOR(value/100) AS val
  FROM tbl_name
  GROUP BY id, val;

mysql虽然支持非聚集函数，但是要求和group by list中的表达式严格匹配，该语句才是合法的。mysql并不会去识别select 中的子句与group by中表达式是否有依赖关系，哪怕只是简单的相加一下，如下sql已然会报错

SELECT user_id,FLOOR(id/10),  user_id+FLOOR(id/10)
FROM user_login_log
GROUP BY user_id, FLOOR(id/10);

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

总结一下，MySQL对于ONLY_FULL_GROUP_BY语义的判断规则是，如果group by list中的表达式是basic column，那么target list中允许出现表达式是group by list中basic column或者alias column的组合结果，如果group by list中的表达式是复杂表达式(非basic column或者alias column)，那么要求target list中的表达式必须能够严格和group by list中的表达式进行匹配，否者这条查询会被认为不合法。

官方文档：

sql_mode：
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
group by：
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
any_value：
https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value