MySQL 关于 only_full_group_by 限制

先上结论
如果 only_full_group_by 被启用，那么在查询时，如果某个列不在group by 列表中，此时如果不对该列进行聚合处理，则该列不能出现在 select 列表，having 条件中及order by 列表中
MySQL 8.0 默认启用了sql_mode，我们可以通过 select @@session.sql_mode 查看会话中的 sql_mode 配置。

1. mysql> SELECT @@session.sql_mode;
2. +-----------------------------------------------------------------------------------------------------------------------+
3. | @@session.sql_mode
4. |
5. +-----------------------------------------------------------------------------------------------------------------------+
6. | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
7. +-----------------------------------------------------------------------------------------------------------------------+
8. 1 row in set (0.00 sec)

复制代码

有这么一张表

1. CREATE TABLE `mytable`
2. (
3.     `id` int unsigned NOT NULL,
4.     `a`  varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
5.     `b`  int                                    DEFAULT NULL,
6.     PRIMARY KEY (`id`)
7. ) ENGINE = InnoDB
8.   DEFAULT CHARSET = utf8mb4
9.   COLLATE = utf8mb4_general_ci;
11. INSERT INTO mytable
12. VALUES (1, 'abc', 1000),
13.        (2, 'abc', 2000),
14.        (3, 'def', 4000);

复制代码

当我们执行的 SQL 语句包含聚合函数时，MYSQL 提示需要使用 GROUP BY 进行分组。

1. mysql> SELECT a,SUM(b) FROM mytable;
2. ERROR 1140 (42000): In aggregated query without GROUP BY,
3. expression #1 of SELECT list contains nonaggregated column 'study.mytable.a';
4. this is incompatible with sql_mode=only_full_group_by
5. 如果我们关掉 only_full_group_by 限制，SQL 语句就正常执行了，但又没有完全正常执行。
7. mysql> SET sql_mode = '';
8. Query OK, 0 rows affected (0.00 sec)
10. mysql> SELECT a,SUM(b) FROM mytable;
11. +------+--------+
12. | a    | SUM(b) |
13. +------+--------+
14. | abc  |   7000 |
15. +------+--------+
16. 1 row in set (0.00 sec)

复制代码

可以看到，虽然我们得到了 SUM(b) 的值为 7000 是期望的，但是 a 的值为 abc 不是我们期望的。
MySQL 8.0 里的文档提到这么一句话
the query is processed by treating all rows as a single group, but the value selected for each named column is nondeterministic
在这个例子中，a 的值就是不确定的
当 WHERE 过滤条件中包含了 SELECT 列表中全部非聚合列的字段，则在开启 only_full_group_by 下也可以正常工作

1. In this case, every such column must be limited to a single value in theWHEREclause, and all such limiting conditions must be joined by logicalAND
2. mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');
3. Query OK, 0 rows affected (0.01 sec)
6. mysql> SELECT a,SUM(b) FROM mytable;
7. ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'study.mytable.a'; this is incompatible with sql_mode=only_full_group_by
8. mysql> SELECT a, SUM(b) FROM mytable WHERE a = 'abc';
9. +------+--------+
10. | a    | SUM(b) |
11. +------+--------+
12. | abc  |   3000 |
13. +------+--------+
14. 1 row in set (0.00 sec)
16. mysql> SELECT * FROM mytable1;
17. +----+------+------+-------+
18. | id | a    | b    | c     |
19. +----+------+------+-------+
20. |  1 | abc  | qrs  |  1000 |
21. |  2 | abc  | tuv  |  2000 |
22. |  3 | def  | qrs  |  4000 |
23. |  4 | def  | tuv  |  8000 |
24. |  5 | abc  | qrs  | 16000 |
25. |  6 | def  | tuv  | 32000 |
26. +----+------+------+-------+
27. 6 rows in set (0.00 sec)
29. mysql> SELECT a, b, SUM(c) FROM mytable1 WHERE a = 'abc' OR b = 'qrs';
30. ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'study.mytable1.a'; this is incompatible with sql_mode=only_full_group_by
31. mysql> SELECT a, b, SUM(c) FROM mytable1 WHERE a = 'abc' AND b = 'qrs';
32. +------+------+--------+
33. | a    | b    | SUM(c) |
34. +------+------+--------+
35. | abc  | qrs  |  17000 |
36. +------+------+--------+
37. 1 row in set (0.00 sec)

复制代码

这种方式可以理解为通过条件限制确定了分组条件。因为没有指名分组时，MySQL 将所有字段视为一个组处理。
在开启 only_full_group_by 限制时，也可以通过 ANY_VALUE 函数，使MySQL 正常执行语句，显而易见的是，我们得到的值是不确切的。

1. mysql> SELECT a,SUM(b) FROM mytable;
2. ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'study.mytable.a'; this is incompatible with sql_mode=only_full_group_by
3. mysql> SELECT ANY_VALUE(a),SUM(b) FROM mytable;
4. +--------------+--------+
5. | ANY_VALUE(a) | SUM(b) |
6. +--------------+--------+
7. | abc          |   7000 |
8. +--------------+--------+
9. 1 row in set (0.00 sec)

复制代码

综上，在使用聚合函数的场景中，使用 GROUP BY 进行分组可以确保逻辑严谨性。
推荐阅读：
https://dev.mysql.com/doc/refman/8.0/en/counting-rows.html
https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！