MySQL 默认 only_full_group_by

ONLY_FULL_GROUP_BY是MySQL中的一个SQL模式，它要求在使用GROUP BY语句时，SELECT列表、HAVING条件或ORDER BY列表中的每个列，要么是聚合函数的一部分（如COUNT(), SUM(), AVG()等），要么必须在GROUP BY子句中明确指定。这个模式的设计初衷是增强查询的准确性和可预测性，避免因为列的不明确引用而导致的数据错误或不一致。

以下是ONLY_FULL_GROUP_BY模式的具体举例：

示例 1：正确使用 ONLY_FULL_GROUP_BY

假设有一个orders表，包含customer_id、order_date和order_amount字段，我们想要查询每个客户的订单数和订单总额。在ONLY_FULL_GROUP_BY模式下，正确的查询应该是这样的：

SELECT customer_id, COUNT(order_id) AS order_count, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;

在这个查询中，customer_id列在GROUP BY子句中明确指定，而其他列（order_id和order_amount）则通过聚合函数COUNT()和SUM()进行处理。

示例 2：违反 ONLY_FULL_GROUP_BY 规则的查询

如果我们尝试运行以下查询（在ONLY_FULL_GROUP_BY启用时）：

SELECT customer_id, order_date, COUNT(order_id) AS order_count, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;

这个查询会因为order_date列没有在GROUP BY子句中声明，且不是聚合函数的一部分，而引发错误。MySQL会要求你明确这个字段的处理方式。

解决方法

1. 修改查询：

   • 将order_date列添加到GROUP BY子句中，但这通常不符合查询的意图，因为这样做会按每个客户的每个订单日期分组，而不是按客户分组。
   • 使用聚合函数处理order_date列（如MIN(), MAX()等），但这通常不是想要的结果。
   • 使用ANY_VALUE()函数（如果MySQL版本支持），这个函数可以返回组内的任意一个值，但需要注意这可能会引入不确定性。

2. 修改SQL模式：

   • 临时禁用ONLY_FULL_GROUP_BY模式，通过执行如SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));的命令（注意，这种方法在当前会话有效，重启数据库后恢复）。
   • 永久禁用ONLY_FULL_GROUP_BY模式，通过修改MySQL的配置文件（如my.cnf或my.ini），在[mysqld]部分设置sql_mode，去掉ONLY_FULL_GROUP_BY，然后重启MySQL服务。

示例 3：使用 ANY_VALUE() 函数

如果MySQL版本支持ANY_VALUE()函数，你可以这样修改查询：

SELECT customer_id, ANY_VALUE(order_date), COUNT(order_id) AS order_count, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;

这样，order_date列将不会引发错误，但需要注意返回的是组内任意一个订单的日期。

总的来说，ONLY_FULL_GROUP_BY模式有助于确保SQL查询的准确性和一致性，但在某些情况下可能需要调整查询或SQL模式以满足特定的查询需求。