MySQL中的只允许使用全组的查询语句

MySQL中的只允许使用全组的查询语句

在MySQL中，当我们使用GROUP BY语句对数据进行分组时，通常会结合使用聚合函数来进行计算。然而，MySQL的默认行为是在使用GROUP BY语句时只允许在SELECT列表中包含分组条件或聚合函数的列，这意味着在没有设置全组时，不允许查询中出现没有被聚合函数处理的列。这种行为在MySQL 5.7版本之前是默认的，称为”only full group by”，在MySQL 5.7版本后，默认值被修改为了sql_mode中的ONLY_FULL_GROUP_BY选项。

本文将详细介绍MySQL中”only full group by”的概念、工作原理以及如何解决这个问题。

“only full group by”是什么？

“only full group by”是一个MySQL的一个语法规则或特性，它要求在使用GROUP BY语句时，查询中的SELECT列表中的每个非聚合字段都必须出现在GROUP BY子句中。如果不满足这个规则，MySQL会抛出一个错误。

例如，我们有一个名为students的表，包含学生的姓名、年龄和成绩。如果我们想要按照学生的姓名进行分组，并计算每个学生的平均年龄，通常的SQL语句可能会是这样的：

SELECT name, AVG(age) 
FROM students 
GROUP BY name;

在MySQL 5.7版本之前，以上查询会执行成功。但在MySQL 5.7及更新版本中，默认情况下会抛出一个错误，因为name没有出现在GROUP BY子句中。这就是”only full group by”的工作原理。

如何避免”only full group by”错误？

为了避免”only full group by”错误，有几种方法可以采用。

1. 将所有字段都包含在GROUP BY子句中

最简单的方法是将所有SELECT列表中的字段都包含在GROUP BY子句中。虽然这种方法能够确保不会出现”only full group by”错误，但在某些情况下可能会导致结果不准确，因为GROUP BY会同时按照所有字段进行分组。

SELECT name, AVG(age) 
FROM students 
GROUP BY name, age;

2. 使用聚合函数

另一种避免错误的方法是使用聚合函数来处理SELECT列表中的字段。这样可以确保在结果集中只有聚合函数处理的列，而不会出现没有被聚合函数处理的列。

SELECT name, AVG(age), AVG(score) 
FROM students 
GROUP BY name;

3. 设置sql_mode

如果你不想采用上述方法，也可以通过修改sql_mode来关闭”only full group by”的检查。你可以在MySQL配置文件中设置sql_mode为一个不包含ONLY_FULL_GROUP_BY选项的值，或者在会话级别中执行以下命令：

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

修改sql_mode可能会影响到其他查询的行为，因此请谨慎使用此方法。

示例代码

为了演示”only full group by”错误和解决方法，我们来创建一个简单的students表并插入一些数据：

CREATE TABLE students (
    name VARCHAR(50),
    age INT,
    score DECIMAL(5, 2)
);

INSERT INTO students (name, age, score) VALUES
('Alice', 20, 85.5),
('Bob', 22, 90.0),
('Alice', 21, 88.5);

现在，让我们尝试运行一个不符合”only full group by”规则的查询：

SELECT name, AVG(age) 
FROM students 
GROUP BY name;

在MySQL 5.7及更新版本中，运行以上查询会导致一个错误，类似于：

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

为了解决这个错误，我们可以采用前面提到的方法之一。例如，我们可以将所有字段都包含在GROUP BY子句中：

SELECT name, AVG(age) 
FROM students 
GROUP BY name, age;

或者使用聚合函数处理所有字段：

SELECT name, AVG(age), AVG(score) 
FROM students 
GROUP BY name;

结论

“only full group by”是MySQL的一个语法规则，要求在使用GROUP BY时SELECT列表中的每个非聚合字段都必须出现在GROUP BY子句中。为了避免出现错误，可以采用将所有字段包含在GROUP BY子句中或使用聚合函数处理所有字段的方法。此外，还可以通过修改sql_mode的方式关闭”only full group by”的检查。在实际开发中，遵循这些规则可以确保查询的准确性并提高代码的可维护性。

本文链接：http://so.lmcjl.com/news/10129/