MySQL关闭only_full_group_by

1. 引言

在使用MySQL进行数据查询的时候，有时候会遇到类似于"Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column"的错误提示。这是因为MySQL默认开启了only_full_group_by模式，它要求在GROUP BY语句中的每个非聚合列都必须出现在SELECT列表中。本文将会介绍only_full_group_by模式的作用、原因以及在不同版本的MySQL中如何关闭它。

2. only_full_group_by的作用

only_full_group_by模式的目的是为了保证查询结果的准确性。当使用GROUP BY语句进行分组查询时，只有被分组的列和聚合函数可以出现在SELECT列表中，其他列必须使用聚合函数进行处理。这样可以避免在查询结果中出现不一致的情况。

3. only_full_group_by的原因

MySQL默认开启only_full_group_by模式的原因是为了保证查询结果的正确性。如果不开启该模式，那么在进行分组查询的时候就有可能出现以下问题：

  • 非聚合列的值不确定：默认情况下，如果一个列不在GROUP BY语句中，那么在分组之后的结果集中，这个列的值是不确定的。开启only_full_group_by模式后，这个问题就会被解决，确保每个非聚合列的值是准确的。

  • 无法使用HAVING子句：在只有GROUP BY语句的情况下，如果没有only_full_group_by模式，我们就无法使用HAVING子句对分组之后的结果进行过滤。只有开启这个模式，才能保证HAVING子句的正确性。

4. 关闭only_full_group_by的方法

4.1 MySQL 5.7以及更早版本

在MySQL 5.7以及更早的版本中，可以通过修改sql_mode参数来关闭only_full_group_by模式。具体步骤如下：

  1. 使用管理员权限登录MySQL服务器。

  2. 执行以下SQL语句查看当前的sql_mode参数：

SELECT @@sql_mode;

如果结果中包含了ONLY_FULL_GROUP_BY，说明only_full_group_by模式是开启的。

  1. 执行以下SQL语句将only_full_group_by模式关闭：
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
  1. 再次执行以下SQL语句查看sql_mode参数，确认only_full_group_by模式已经关闭：
SELECT @@sql_mode;

4.2 MySQL 8.0及更新版本

在MySQL 8.0及更新版本中，only_full_group_by模式被设置为默认模式，无法通过修改sql_mode参数来直接关闭。但是可以通过修改sql_mode参数来部分关闭only_full_group_by模式，即将其转换为警告模式。

  1. 使用管理员权限登录MySQL服务器。

  2. 执行以下SQL语句查看当前的sql_mode参数：

SELECT @@sql_mode;

如果结果中包含了ONLY_FULL_GROUP_BY，说明only_full_group_by模式是开启的。

  1. 执行以下SQL语句将only_full_group_by模式转换为警告模式：
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
  1. 再次执行以下SQL语句查看sql_mode参数，确认only_full_group_by模式已经转换为警告模式：
SELECT @@sql_mode;

5. 示例

我们来看一个示例，说明在不同的only_full_group_by模式下查询结果的差异。

假设有以下一张订单表orders：

order_id customer_id order_date amount
1 1 2021-01-01 100
2 1 2021-01-02 200
3 2 2021-01-03 150
4 2 2021-01-03 300

5.1 开启only_full_group_by模式

首先，我们查看一下默认情