ONLY_FULL_GROUP_BY 是什么？

首先我说 ONLY_FULL_GROUP_BY 是什么之前，我得说说 sql_mode 是什么？

sql_mode 是 MySQL 中的一个系统变量(variables)，可由多个 mode 组成，每个 mode 控制一种行为，如是否允许除数为 0，日期中是否允许 '0000-00-00' 值，等等。

MySQL 5.7 的 sql_mode 默认值如下:

sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

那么 ONLY_FULL_GROUP_BY 就相当于是一种规则了，这个规则要求:

凡是在 group by 后面出现的字段，必须同时出现在 select 的后面
凡是在 select 后面出现的，同时未在聚合函数中出现的字段，必须同时出现在 group by 后面实际上比我说的要更复杂一些，具体见官档 https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

为什么开发人员经常有关闭 ONLY_FULL_GROUP_BY 的诉求？如何应对？

这个可能是因为 MySQL 太灵活，给大家惯了个坏习惯，首先别的数据库，例如 Oracle 压根没有 sql_mode 这个特殊玩法，可以让你控制 SQL 行为。然后，MySQL 5.7 以前均没有默认 "ONLY_FULL_GROUP_BY" 约束，用法偏 “叙利亚风格”，很乱没有约束，所以大家都习惯性地用了一些灵活的 SQL 写法，以至于有一些框架也没管过 "ONLY_FULL_GROUP_BY" 问题，如果是框架问题，那就不好改了。这个时候，我只能做到，告诉开发人员，我不赞成改，和改这个的后果，如果他们领导同意修改，我还是帮他们修改。

其实啊，也可以不改全局级变量的，因为 sql_mode 是 session 级别的参数，没必要改我全局的 sql_mode 吧。所以，如果我不同意改全局级变量，你还是要用，你们就在代码里嵌入这句只修改 session 级别的参数不就行了？

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

以上的 SQL，需要 sys 库 list_drop 函数的调用权限，更适合 DBA 账号，以下两个 SQL 适合普通账号。

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

或者

set @old_sql_mode=@@sql_mode; 
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION '; 
{{EXCUTE YOUR SQL HERE}};
set sql_mode=@old_sql_mode; # 用完即改回来

还是不满意怎么办？那么我可以告诉你，其实你连 sql_mode 都可以不改。。改 SQL 就能实现你的需求。具体怎么改？其实很简单，使用 any_value

用法见 https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

mysql> select a,b,c,count(*) from test_group_by group by b;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fander.test_group_by.a' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

mysql> select any_value(a),b,any_value(c),count(*) from test_group_by group by b;
+--------------+------+--------------+----------+
| any_value(a) | b    | any_value(c) | count(*) |
+--------------+------+--------------+----------+
|            3 | abc  |            4 |        3 |
|            1 | def  |            6 |        3 |
+--------------+------+--------------+----------+
2 rows in set (0.00 sec)

我为什么不赞成sql_mode去掉ONLY_FULL_GROUP_BY？

原因如下:

1. 没有必要

前面我指出，可以修改 session 级变量，也可以直接修改 SQL 来解决，所以没有必要改全局的。改全局影响的是一堆人，如果你的数据库服务器是共用的，例如开发环境，你会影响别的开发同事；又例如，你虽然是生产环境，但运维对这个数据库运维其实会有一些监控或者一些统计分析，需要用到 group by 语句，那你改全局就是动了别人的奶酪！

2. 这是官方的默认值

sql_mode 包含 ONLY_FULL_GROUP_BY ，这个是 MySQL5.7 官方的默认值。原本在 MySQL 5.6 他还不是默认值，官方在新版设置为默认值肯定有他的原因，说明这个东西很重要，是遵循sql-92标准的玩法。

这是本来要贴一下 5.6 的 sql_mode 默认值是多少， 5.7 的 sql_mode 默认值是多少，水文的态度就是，你自己查哈。

3. 别的三大关系数据库天生ONLY_FULL_GROUP_BY

SQL SERVER、ORACLE、Postgres 规范是一致的，遵循sql-92标准。所以他甚至不能让你调。天生自带 ONLY_FULL_GROUP_BY 约束，认为违反这个规则的SQL都是不合法的，直接报错！MySQL 作为四大关系数据库之一，也应该跟随 SQL 标准。

本来要贴图证明的。算了，水文要有水文的态度，不贴图了。选择相信我就行了，我是做过实验的

4. 不使用ONLY_FULL_GROUP_BY约束，可能会有数据不一致的问题

其实这个才是核心原因！前面的原因都是我凑字数水出来的。

这种数据不一致，是你这条 SQL 今天跑出来的结果是 A，而明天跑出来的结果可能是 B，这种后果是完全不能接受的。

如果数据库没有设置 "ONLY_FULL_GROUP_BY"，当数据量变化时，SQL 的执行计划有可能发生变化，两个不同的执行计划使用的索引不一样，那就有可能出来的结果不一致。

下面，我举一个场景例子，来验证这点:
第一步，我们先建一个表，插入 6 条数据，如下

# 没有设置主键索引
create table test_group_by(
a INT,
b VARCHAR(10),
c INT
);

# 插入 6 条数据
INSERT INTO test_group_by
VALUES 
(1, 'def', 6),
(2, 'def', 5),
(3, 'abc', 4),
(4, 'abc', 3),
(5, 'def', 2),
(6, 'abc', 1);

mysql> select * from test_group_by;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | def  |    6 |
|    2 | def  |    5 |
|    3 | abc  |    4 |
|    4 | abc  |    3 |
|    5 | def  |    2 |
|    6 | abc  |    1 |
+------+------+------+
6 rows in set (0.00 sec)

第二步，接着，我设置 sql_mode 为空，即关闭 ONLY_FULL_GROUP_BY，使我下面这个 select 了 a、c 列，但没有 group by a、c 列的 SQL 可以跑。

mysql> select a,b,c,count(*) from test_group_by group by b;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fander.test_group_by.a' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

mysql> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select a,b,c,count(*) from test_group_by group by b;
+------+------+------+----------+
| a    | b    | c    | count(*) |
+------+------+------+----------+
|    3 | abc  |    4 |        3 |
|    1 | def  |    6 |        3 |
+------+------+------+----------+
2 rows in set (0.00 sec)

第三步，接着我在 C 列创建主键索引，你可以发现这张表和第一步对比，排序变了，a 列成倒序了。

mysql> alter table test_group_by add primary key(c);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from test_group_by;
+------+------+---+
| a    | b    | c |
+------+------+---+
|    6 | abc  | 1 |
|    5 | def  | 2 |
|    4 | abc  | 3 |
|    3 | abc  | 4 |
|    2 | def  | 5 |
|    1 | def  | 6 |
+------+------+---+
6 rows in set (0.00 sec)

第四步，然后再执行刚才 select 了 a、c 列，但没有 group by a、c 列的 SQL，结果居然变了。这就是问题所在，两次结果不一致！(和第二步结果对比一下)

mysql> select a,b,c,count(*) from test_group_by group by b;
+------+------+---+----------+
| a    | b    | c | count(*) |
+------+------+---+----------+
|    6 | abc  | 1 |        3 |
|    5 | def  | 2 |        3 |
+------+------+---+----------+
2 rows in set (0.00 sec)
你可能会说，这不是表结构不一样吗？你这个第四步的结果和第二步的结果区别是在于，前者是有主键索引的。我知道你肯定会这么说，那看第五步。

第五步，这时，我把这主键索引删除，变回之前没有任何索引的原始表结构。但结果也无法回到从前了！

mysql> alter table test_group_by drop primary key;
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select a,b,c,count(*) from test_group_by group by b;
+------+------+---+----------+
| a    | b    | c | count(*) |
+------+------+---+----------+
|    6 | abc  | 1 |        3 |
|    5 | def  | 2 |        3 |
+------+------+---+----------+
2 rows in set (0.00 sec)

所以，这里证明了，不开启 ONLY_FULL_GROUP_BY 有可能会导致 SQL 两次执行结果不一致。所有这类 SQL 不符合sql-92标准，理论上都应该改造。

Enjoy MySQL！