only_full_group_by详解

发布时间：2023-05-18

一、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模式。

推荐文章