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 配置，

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 進行分組可以確保邏輯嚴謹性，

推薦閱讀：
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

標籤：MySQL

上一篇：MySQL主從備庫過濾引數分析和測驗

下一篇：MySQL