Mysql的ONLY_FULL_GROUP_BY模式

报错

• 执行：

-- 示例
select y from z group by x;

• 报错：

  ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘x.y.z’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

• 粗略翻译：
  SELECT列表里的第一个表达式(注：示例中的”x”)不在GROUP BY列中，而且包含了非功能性依赖于GROUP BY列的非汇总列‘x.y.z’，对于sql_mode=only_full_group_by模式是不兼容的。

• 问题：

  • SELECT列表里包含了未在GROUP BY语句中出现的列
  • SELECT列表包含了非汇总列，而非汇总列又不功能性依赖于GROUP BY中的列
  • 这两种情况与sql_mode=only_full_group_by不兼容

解决

• 服务运行时修改

-- 只修改当前session
-- 查看当前session的sql_mode
select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+

-- 修改，将ONLY_FULL_GROUP_BY去掉
set @@session.sql_mode='';


-- 修改全局
select @@global.sql_mode;
+--------------------+
| @@global.sql_mode |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+

-- 修改，将ONLY_FULL_GROUP_BY去掉
set @@global.sql_mode='';

• 修改my.cnf
  • 修改my.cnf文件内的参数sql_mode，将ONLY_FULL_GROUP_BY字样去掉
  • 重启服务

再看原因

• 先看下官网对ONLY_FULL_GROUP_BY模式的介绍

  Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

• 粗略翻译：
  拒绝请求，因为他们在select列表里，having条件里或者order by列里面引用非汇总的列，而这些非汇总列即不是在group by列中也不是功能性依赖于group by中的列。

若ONLY_FULL_GROUP_BY模式开启，那么在SELECT HAVING ORDER BY这些”引用”（或者叫”打印”，反正就是用到列的地方）必须是汇总列，而这些汇总列必须是依赖于GROUP BY列。也就是说：

• SELECT HAVING ORDER BY 要么是GROUP BY列
• SELECT HAVING ORDER BY 要么是基于GROUP BY列的汇总列

那么什么是汇总列呢。就是一些汇总函数操作之后的列。那么都有哪些函数是汇总函数呢。

An aggregate function performs a calculation on multiple values and returns a single value.

基本上一些经常用到的与GROUP BY搭配的函数都是汇总函数，比如：
MIN() MAX() COUNT() SUM()

总结

通俗的讲，GROUP BY是对目标集进行分类。分类后，对每个具体分类取一个值代表这个分类，而这些操作都是基于整个这个分类的，比如求和、取平均值等。但如果对分类取的值与分类方式无关，比如一群人，先按性别分类之后，再打印年龄，这时候是不具有代表性的。此时若ONLY_FULL_GROUP_BY模式开启就会报错。若ONLY_FULL_GROUP_BY模式关闭，先按性别分类，再打印年龄，就会在分类中取一个返回。

Reference & Thanks

• ONLY_FULL_GROUP_BY
• MySQL Aggregate Functions