一文详解MySQL设置only_full_group_by报错问题

什么是only_full_group_by问题？

only_full_group_by是MySQL的一个严格模式，它强制所有未在GROUP BY中的列与聚合函数一起使用，否则会抛出错误。这个问题的根源在于SQL的聚合特性。在SELECT语句中，我们可以使用聚合函数（如SUM，AVG等），将一列或多列数据分组，并执行一个计算。然而，由于该分组将数据进行分类，因此不能在SELECT语句中使用分组之外的列。如果我们试图这样做，就会导致MySQL错误。

SELECT customer_name,MAX(order_date),total_price FROM orders GROUP BY customer_name;

在上面的代码中，利用GROUP BY按顾客名分组并找到其最新的订单日期和总价。但是，如果该数据集中有同名顾客，但是它们的订单日期和总价不同会怎么样？

SELECT customer_name,order_date,total_price FROM orders GROUP BY customer_name;

在以上代码中，尝试选择顾客名，订单日期和总价，但是只分组基于顾客名。这违反了only_full_group_by的限制，因为它要求在SELECT语句中使用聚合函数或按组折叠的列必须在GROUP BY语句中指定。

only_full_group_by问题的修正

一种解决only_full_group_by问题的方法是在MySQL的服务器级别上设置sql_mode系统变量。我们可以将该变量设置为非严格模式或选择除了only_full_group_by之外的所有模式。这个变量包含一个用逗号分隔的字符串，表示应该启用哪些模式。例如，可以使用以下命令查询当前的sql_mode设置：

SELECT @@sql_mode;

以下是一种在服务器级别上设置sql_mode变量的方法，从而在不启用only_full_group_by模式的情况下解决问题：

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

only_full_group_by的优点和缺点

使用only_full_group_by模式的优点在于它能识别许多引入数据不一致性和逻辑错误的查询，强制所有未在GROUP BY中的列与聚合函数一起使用，因此减少了在查询中的混淆和错误。

然而，only_full_group_by模式也具有一些缺点，它可能会限制一些查询和应用程序中合法的查询，因为它要求在SELECT语句中使用聚合函数或按组折叠的列必须在GROUP BY语句中指定。而这些查询和应用程序中可能就是需要不需要聚合函数的复杂查询。此外，由于它增加了数据处理需求，可能会导致性能下降。

结论

在实际生产中，要根据业务需求和查询的重要性来决定是否启用only_full_group_by模式。如果查询需要聚合函数或依赖于分组、合并、连接等操作，则必须启用该模式，以确保查询结果的完整性和正确性；如果查询中不需要按组折叠列，或者业务允许某些不符合聚合特性的查询，则可以设置sql_mode来关闭only_full_group_by模式。