先上结论
如果 only_full_group_by 被启用，那么在查询时，如果某个列不在group by 列表中，此时如果不对该列进行聚合处理，则该列不能出现在 select 列表，having 条件中及order by 列表中
MySQL 8.0 默认启用了sql_mode，我们可以通过 select @@session.sql_mode 查看会话中的 sql_mode 配置。
mysql> SELECT @@session.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode
|
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
有这么一张表
CREATE TABLE mytable
(
id int unsigned NOT NULL,
a varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
b int DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci;
INSERT INTO mytable
VALUES (1, 'abc', 1000),
(2, 'abc', 2000),
(3, 'def', 4000);
当我们执行的 SQL 语句包含聚合函数时，MYSQL 提示需要使用 GROUP BY 进行分组。
mysql> SELECT a,SUM(b) FROM mytable;
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
如果我们关掉 only_full_group_by 限制，SQL 语句就正常执行了，但又没有完全正常执行。
mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT a,SUM(b) FROM mytable;
+------+--------+
| a | SUM(b) |
+------+--------+
| abc | 7000 |
+------+--------+
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 下也可以正常工作
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
mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT a,SUM(b) FROM mytable;
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
mysql> SELECT a, SUM(b) FROM mytable WHERE a = 'abc';
+------+--------+
| a | SUM(b) |
+------+--------+
| abc | 3000 |
+------+--------+
1 row in set (0.00 sec)
mysql> SELECT * FROM mytable1;
+----+------+------+-------+
| id | a | b | c |
+----+------+------+-------+
| 1 | abc | qrs | 1000 |
| 2 | abc | tuv | 2000 |
| 3 | def | qrs | 4000 |
| 4 | def | tuv | 8000 |
| 5 | abc | qrs | 16000 |
| 6 | def | tuv | 32000 |
+----+------+------+-------+
6 rows in set (0.00 sec)
mysql> SELECT a, b, SUM(c) FROM mytable1 WHERE a = 'abc' OR b = 'qrs';
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
mysql> SELECT a, b, SUM(c) FROM mytable1 WHERE a = 'abc' AND b = 'qrs';
+------+------+--------+
| a | b | SUM(c) |
+------+------+--------+
| abc | qrs | 17000 |
+------+------+--------+
1 row in set (0.00 sec)
这种方式可以理解为通过条件限制确定了分组条件。因为没有指名分组时，MySQL 将所有字段视为一个组处理。
在开启 only_full_group_by 限制时，也可以通过 ANY_VALUE 函数，使MySQL 正常执行语句，显而易见的是，我们得到的值是不确切的。
mysql> SELECT a,SUM(b) FROM mytable;
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
mysql> SELECT ANY_VALUE(a),SUM(b) FROM mytable;
+--------------+--------+
| ANY_VALUE(a) | SUM(b) |
+--------------+--------+
| abc | 7000 |
+--------------+--------+
1 row in set (0.00 sec)
综上，在使用聚合函数的场景中，使用 GROUP BY 进行分组可以确保逻辑严谨性。
Original: https://www.cnblogs.com/arvinhuang/p/16437846.html
Author: 平凡键客
Title: MySQL 关于 only_full_group_by 限制
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/620958/
转载文章受原作者版权保护。转载请注明原作者出处！