MySQL运行报错：“Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column”如何解决

在使用MySQL进行查询时，有时会遇到如下错误信息：

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.table.column' 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模式导致的。本文将详细介绍这个错误的原因以及如何解决它。

错误原因

在MySQL 5.7及以上版本中，默认启用了ONLY_FULL_GROUP_BY模式。这个模式要求在使用GROUP BY时，SELECT列表中的所有列都必须是聚合函数（如COUNT、SUM、AVG等）的参数，或者这些列必须在GROUP BY子句中列出。

如果SELECT列表中的某个列既不是聚合函数的参数，也没有出现在GROUP BY子句中，MySQL就会抛出上述错误。

解决方法

方法一：修改SQL查询

最简单的解决方法是将SELECT列表中的所有非聚合列都包含在GROUP BY子句中。例如：

SELECT column1, column2, SUM(column3)
FROM table
GROUP BY column1, column2;

在这个例子中，column1和column2都出现在GROUP BY子句中，因此查询是合法的。

方法二：使用聚合函数

如果你不需要对某些列进行分组，而是希望对这些列进行聚合操作，可以使用聚合函数。例如：

SELECT column1, MAX(column2), SUM(column3)
FROM table
GROUP BY column1;

在这个例子中，column2使用了MAX聚合函数，因此查询是合法的。

方法三：禁用ONLY_FULL_GROUP_BY模式

如果你确定查询的逻辑是正确的，并且不希望修改查询语句，可以选择禁用ONLY_FULL_GROUP_BY模式。可以通过以下步骤实现：

1. 查看当前的sql_mode设置：

   SELECT @@sql_mode;

1. 从结果中移除ONLY_FULL_GROUP_BY，并重新设置sql_mode。例如：

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

1. 如果你希望永久修改sql_mode，可以在MySQL配置文件（如my.cnf或my.ini）中添加或修改以下内容：

   [mysqld]
   sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

然后重启MySQL服务。

方法四：使用ANY_VALUE()函数

MySQL 5.7及以上版本提供了ANY_VALUE()函数，可以用于绕过ONLY_FULL_GROUP_BY的限制。ANY_VALUE()函数允许你选择任意一个值，而不需要将其包含在GROUP BY子句中。例如：

SELECT ANY_VALUE(column1), column2, SUM(column3)
FROM table
GROUP BY column2;

在这个例子中，column1使用了ANY_VALUE()函数，因此查询是合法的。

总结

MySQL的ONLY_FULL_GROUP_BY模式是为了确保查询的严格性和一致性，但在某些情况下可能会带来不便。通过修改查询语句、使用聚合函数、禁用ONLY_FULL_GROUP_BY模式或使用ANY_VALUE()函数，可以有效地解决这个错误。根据具体的业务需求选择合适的解决方法，既能保证查询的正确性，又能提高开发效率。