MySQL 报错：sql_mode=only_full_group_by 的原因及解决方案

在使用 MySQL 时，特别是在执行带有 GROUP BY 子句的 SQL 查询时，可能会遇到 sql_mode=only_full_group_by 报错。该错误通常是由于 MySQL 的 SQL 模式设置引起的。本文将详细分析该错误的原因，并提供多种解决方案。

一、错误原因分析

1.1 什么是 ONLY_FULL_GROUP_BY

ONLY_FULL_GROUP_BY 是 MySQL 中的一种 SQL 模式，该模式对 GROUP BY 操作进行了严格的要求。启用该模式时，所有在 SELECT 语句中出现的非聚合列必须出现在 GROUP BY 子句中，或者必须通过聚合函数（如 SUM、AVG、MAX 等）进行处理。如果不满足这一要求，MySQL 将抛出错误。

示例：

假设有如下数据表 orders：

以下查询会在启用了 ONLY_FULL_GROUP_BY 模式时报错：

SELECT customer_id, total_amount FROM orders GROUP BY customer_id;

报错信息通常如下：

ERROR 1055 (42000): 'orders.total_amount' isn't in GROUP BY

这是因为 total_amount 既没有出现在 GROUP BY 子句中，也没有通过聚合函数处理。

二、解决方案

2.1 修改 SQL 查询

最符合规范的解决方案是修改 SQL 查询，使其符合 ONLY_FULL_GROUP_BY 模式的要求。

1. 使用聚合函数：

   对 SELECT 中未在 GROUP BY 中出现的列使用聚合函数处理：

   SELECT customer_id, SUM(total_amount) FROM orders GROUP BY customer_id;

   该查询通过 SUM 函数对 total_amount 进行聚合，不会触发 ONLY_FULL_GROUP_BY 错误。

2. 添加所有非聚合列到 GROUP BY 子句：

   将 SELECT 中的所有非聚合列添加到 GROUP BY 子句中：

   SELECT customer_id, total_amount FROM orders GROUP BY customer_id, total_amount;

   该查询通过将 total_amount 列添加到 GROUP BY 子句中避免了报错。

2.2 修改 MySQL 配置

如果现有 SQL 查询难以修改，或者需要临时禁用该模式以保持兼容性，可以通过修改 MySQL 配置来禁用 ONLY_FULL_GROUP_BY。

1. 全局禁用 ONLY_FULL_GROUP_BY 模式：

   可以通过以下 SQL 语句禁用全局的 ONLY_FULL_GROUP_BY 模式：

   SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

   该命令会从当前全局 SQL 模式中移除 ONLY_FULL_GROUP_BY。

2. 针对当前会话禁用 ONLY_FULL_GROUP_BY：

   如果只需要在当前会话中禁用该模式，可以使用：

   SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

   这只影响当前数据库连接的 SQL 模式设置，不会影响其他会话。

3. 永久性禁用 ONLY_FULL_GROUP_BY：

   如果希望永久禁用该模式，可以编辑 MySQL 的配置文件（通常为 my.cnf 或 my.ini），在 [mysqld] 部分添加以下内容：

   [mysqld]
   sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

   然后重启 MySQL 服务使配置生效。

2.3 升级 MySQL 版本

在 MySQL 5.7 版本之前，ONLY_FULL_GROUP_BY 并不是默认启用的。如果项目可以升级到较新的 MySQL 版本，尤其是 MySQL 8.0，该版本中引入了对 ONLY_FULL_GROUP_BY 模式的更好处理，允许开发者在保留严格模式的同时通过表达式处理来避免错误。

三、思维导图

解决 MySQL sql_mode=only_full_group_by 报错
    1. 错误原因
        1.1 ONLY_FULL_GROUP_BY 模式介绍
        1.2 未使用聚合函数的列导致错误
    2. 解决方案
        2.1 修改 SQL 查询
            2.1.1 使用聚合函数
            2.1.2 添加所有非聚合列到 GROUP BY
        2.2 修改 MySQL 配置
            2.2.1 全局禁用 ONLY_FULL_GROUP_BY
            2.2.2 会话级禁用 ONLY_FULL_GROUP_BY
            2.2.3 永久禁用 ONLY_FULL_GROUP_BY
        2.3 升级 MySQL 版本

四、总结

当 MySQL 启用了 ONLY_FULL_GROUP_BY 模式时，SQL 查询必须严格遵守 GROUP BY 的规则。通过正确地修改 SQL 查询，使用聚合函数或调整 MySQL 配置，可以有效解决 sql_mode=only_full_group_by 的报错。对于长远的项目维护，建议在保证 SQL 语句规范性的前提下，通过学习和应用新的 MySQL 特性来提升数据库查询的质量和稳定性。