解决MySQL的sql_mode=only_full_group_by错误提示

2023 年 12 月 18 日

113 次浏览

2538字数

在MySQL中 ONLY_FULL_GROUP_BY模式是启用严格的GROUP BY检查。如果一个SQL查询含有GROUP BY语句，只有在SELECT中的每一列要么是在GROUP BY语句中声明的，要么是通过聚合函数计算出来的，此时查询才符合 ONLY_FULL_GROUP_BY的要求。

这个设置的设计初衷是为了避免不精确或者不可预知的查询结果，因为在未指定完全分组条件的情况下，非聚合列的值可能有多个而导致返回结果难以预测。

当遇到MySQL 报错提示 sql_mode=only_full_group_by，意味着我们的查询没有完全遵守这种严格的规则。解决这个错误消息通常有以下几种策略：

1. 调整查询语句

调整SQL查询，确保SELECT后面跟的每一个字段都符合 ONLY_FULL_GROUP_BY的要求。对于GROUP BY后面没有包含的字段，要使用聚合函数，例如，AVG(), COUNT(), MIN(), MAX(), SUM() 等。

示例调整：

SELECT customer, MAX(sale_date) 最后购买日期
FROM sales
GROUP BY customer;

这里，customer是GROUP BY的一部分，而 sale_date使用了MAX()聚合函数，因此避免了错误。

2. 禁用 `ONLY_FULL_GROUP_BY`

如果确信查询结果是符合期望的，或者是在开发环境中并且想要临时允许这种行为，可以通过修改MySQL配置来禁用 ONLY_FULL_GROUP_BY。

这可以通过以下几种方式完成：

临时禁用：

可以在MySQL客户端中执行以下命令禁用 ONLY_FULL_GROUP_BY：

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

或者针对当前会话：

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

永久禁用（不建议在生产环境中这样做）：

在服务器配置文件中修改 sql_mode，去掉 ONLY_FULL_GROUP_BY。具体操作如下：

找到MySQL配置文件（通常为 my.cnf或 my.ini，位置根据不同系统可能有所不同）。
将 sql_mode变量的值更改为不包含 ONLY_FULL_GROUP_BY。
重启MySQL服务器使配置生效。

3. 使用ANY_VALUE()函数

从MySQL 5.7.5开始，引入了ANY_VALUE()函数来抑制 ONLY_FULL_GROUP_BY的聚合检查。这可以允许查询选择一个非聚合列的任何值。

示例：

SELECT customer, ANY_VALUE(sale_date) 任意购买日期
FROM sales
GROUP BY customer;

采用以上方法之一可以有效解决 sql_mode=only_full_group_by错误。

在实际开发过程中，首选的做法应该是调整查询语句，确保查询逻辑的正确性和数据库的性能。在某些情况下，如果想要临时绕过规则，那么可以使用设置会话sql_mode的方式暂时禁用 ONLY_FULL_GROUP_BY，但这种做法应当谨慎使用，以免在生产环境中带来难以预料的结果。对于程序发布的环境，建议遵守 ONLY_FULL_GROUP_BY的规则。而使用 ANY_VALUE()是一种折衷的选择，它保持 sql_mode设置不变，同时解决了错误提示问题。