only_full_group_by詳解

一、only_full_group_by意義

MySQL提供了GROUP BY語句，可以對數據進行分組，以滿足不同的查詢需求。只有當GROUP BY子句中的字段出現在SELECT列表中，MySQL才會將其作為分組依據進行聚合。為了確保分組結果的正確性，MySQL提供一種嚴格的模式，即only_full_group_by模式。只有在only_full_group_by模式下，MySQL才會強制檢查GROUP BY子句和SELECT列表中的字段是否一致，避免了不同字段的意外聚合。

二、only_full_group_by禁用

only_full_group_by默認處於開啟狀態，這時如果我們執行聚合查詢語句時，如果SELECT中的字段不在GROUP BY子句中，MySQL就會報錯，如下所示：

SELECT a, SUM(b) FROM c GROUP BY a;
# ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.c.a' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

只要將只開啟這個模式的MySql的唯一字符串，從“ONLY_FULL_GROUP_BY”改成其他，例如“STRICT_TRANS_TABLES”，就可以禁用這種模式。

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

三、only_full_group_by報錯

MySQL的only_full_group_by模式可以避免意外的聚合，但也有可能因為語法錯誤而報錯。當我們執行類似下面這種查詢時，MySQL會報錯，提示非聚合數據無法在SELECT列表中出現：

SELECT a, b, SUM(c) FROM d GROUP BY a;
# ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.d.b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

這個報錯的原因是因為GROUP BY的字段是a，對於SELECT中的c是可以聚合的，但是對於b這個字段，MySQL並不知道應該如何聚合，所以會報錯。如果我們想讓b字段正確的顯示出來，我們需要將其加入GROUP BY子句中：

SELECT a, b, SUM(c) FROM d GROUP BY a, b;

四、only_full_group_by區別選取

下面我們將分別給出只開啟和關閉only_full_group_by模式的代碼示例，讓大家對only_full_group_by模式有更加具體的認識和理解。

開啟only_full_group_by模式的代碼示例

-- 開啟only_full_group_by模式
SET sql_mode=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));

--創建表
CREATE TABLE orders (
    order_id INT(11) PRIMARY KEY,
    order_date DATETIME,
    customer_id INT(11),
    total_cost DECIMAL(10,2) 
);

--插入數據
INSERT INTO orders VALUES (1, '2019-01-01 01:00:00', 1, 100.00), (2, '2019-02-02 02:00:00', 2, 200.00), 
(3, '2019-03-03 03:00:00', 1, 300.00), (4, '2019-04-04 04:00:00', 3, 400.00), (5, '2019-05-05 05:00:00', 1, 500.00);

--查詢數據
SELECT order_date, SUM(total_cost) FROM orders GROUP BY YEAR(order_date);

-- 查詢結果
+---------------------+----------------+
| order_date          | SUM(total_cost) |
+---------------------+----------------+
| 2019-01-01 01:00:00 |          100.00 |
| 2019-02-02 02:00:00 |          200.00 |
| 2019-03-03 03:00:00 |          300.00 |
| 2019-04-04 04:00:00 |          400.00 |
| 2019-05-05 05:00:00 |          500.00 |
+---------------------+----------------+

關閉only_full_group_by模式的代碼示例

--關閉only_full_group_by模式
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

--創建表
CREATE TABLE orders (
    order_id INT(11) PRIMARY KEY,
    order_date DATETIME,
    customer_id INT(11),
    total_cost DECIMAL(10,2) 
);

--插入數據
INSERT INTO orders VALUES (1, '2019-01-01 01:00:00', 1, 100.00), (2, '2019-02-02 02:00:00', 2, 200.00), 
(3, '2019-03-03 03:00:00', 1, 300.00), (4, '2019-04-04 04:00:00', 3, 400.00), (5, '2019-05-05 05:00:00', 1, 500.00);

--查詢數據
SELECT YEAR(order_date), SUM(total_cost) FROM orders GROUP BY YEAR(order_date);

-- 查詢結果
+------------------+----------------+
| YEAR(order_date) | SUM(total_cost) |
+------------------+----------------+
|             2019 |          1500.00 |
+------------------+----------------+

五、小結

only_full_group_by模式可以避免意外的聚合，但由於嚴格要求GROUP BY與SELECT一致，容易導致非聚合數據無法在SELECT列表中出現而報錯。關閉only_full_group_by模式後則不存在這個問題，但容易因為不小心聚合錯誤而產生邏輯錯誤，因此在使用時需要根據情況選擇打開或關閉only_full_group_by模式。