高版本Mysql使用group by分组报错的解决方案

下面是详细的“高版本MySQL使用GROUP BY分组报错的解决方案”攻略。

问题描述

在高版本的MySQL（如MySQL5.7、MySQL8.0）中，对数据进行分组时，可能会出现以下报错信息：

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这是由于MySQL高版本默认启用了sql_mode的only_full_group_by模式，对于GROUP BY操作，要求SELECT语句中的字段必须出现在GROUP BY子句中或者使用聚合函数进行处理。否则就会报上述错误。

解决方案

1. 修改sql_mode参数值

我们可以通过修改MySQL的配置来改变sql_mode参数的值。

首先可以通过如下命令查看当前MySQL所使用的sql_mode参数：

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

如果看到返回值包含了only_full_group_by，那么就启用了该模式。

接下来，我们可以通过修改MySQL的my.cnf或者my.ini配置文件来去掉only_full_group_by模式。

在该文件中找到[mysqld]节点下的sql_mode参数（可能会有多个），然后把其中包含only_full_group_by的配置项去掉。

例如：

# 原始配置
sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 去掉only_full_group_by后的配置
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

修改完配置后，重启MySQL服务，使新配置生效。

2. 对查询结果中的字段应用聚合函数

若不能修改MySQL的配置文件，或者需要在特定场景使用原始的sql_mode值，我们也可以尝试在查询语句中对未出现在GROUP BY子句中的字段应用聚合函数来解决报错问题。

例如：

SELECT user_id, SUM(score) FROM scores GROUP BY user_id;

该语句会报错，因为SELECT语句中的score字段未出现在GROUP BY子句中。但是，我们可以尝试使用聚合函数：

SELECT user_id, MAX(score) FROM scores GROUP BY user_id;

该语句中的score字段被应用了MAX聚合函数，这样就符合了only_full_group_by模式的要求，不会报错。

示例

下面提供两个具体的示例来展示如何使用上述解决方案。假设我们有如下的scores数据表：

id   user_id   score
1    100      88
2    100      93
3    101      73
4    102      85
5    102      91

示例1：修改sql_mode参数值

我们可以通过如下步骤来修改MySQL的sql_mode参数：

1. 打开MySQL的配置文件my.cnf或my.ini。

2. 找到[mysqld]节点下的sql_mode参数（可能会有多个）。

3. 将其值修改为：sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION。

4. 保存文件并重启MySQL服务。

5. 使用如下SQL查询语句，测试是否能正确查询出score字段的平均值：

SELECT AVG(score) FROM scores GROUP BY user_id;

示例2：使用聚合函数

在不修改MySQL的sql_mode参数的情况下，我们可以改变查询语句，使用聚合函数来解决only_full_group_by模式带来的限制。

例如，下列SQL语句查询出每个用户的最小成绩：

SELECT user_id, MIN(score) FROM scores GROUP BY user_id;

在这个查询语句中，score字段被应用了MIN聚合函数，符合only_full_group_by模式的要求。因此可以正常执行，并正确返回结果。

总结

本文介绍了高版本的MySQL在使用GROUP BY分组时可能会遇到的报错，以及两种解决方案：

1. 修改MySQL的sql_mode参数。

2. 在SELECT语句中使用聚合函数。

这两种方案都能有效解决only_full_group_by模式带来的限制，让MySQL在分组查询时更加灵活方便。