【MySQL 的 ONLY_FULL_GROUP_BY 模式】

引言：
作为一个菜鸟，当写sql中涉及到

group by

这样简单的语句时，也会出现问题，我在牛客网上做sql题时，总报这个错：

ONLY_FULL_GROUP_BY 

到底是什么东西呢？

今天写篇文章解释一下。

一、GROUP BY使用时的关键要点

1. **理解

GROUP BY

的作用**

GROUP BY

用于将数据集分割成多个组，每个组由一组具有相同属性的行组成。这使得聚合函数可以应用于每一组，而不是整个数据集。例如，使用

GROUP BY

可以按产品类别统计销售总额。

2. **遵守

ONLY_FULL_GROUP_BY

规则**

在 MySQL 中，如果启用了

ONLY_FULL_GROUP_BY

模式，那么

SELECT

子句中除聚合函数之外的所有列都必须在

GROUP BY

子句中出现。这是为了避免不确定性和潜在的数据歧义。

3. **使用

HAVING

进行条件过滤**

HAVING

子句用于对分组后的结果进行过滤，类似于

WHERE

子句，但

HAVING

适用于聚合结果。例如，你可以使用

HAVING COUNT(*) > 1

来找出至少出现两次的组。

4. 正确排序结果

虽然

GROUP BY

自身不会自动排序结果，但你通常会希望在结果集中应用

ORDER BY

来排序分组。例如，你可以按销售额降序排序产品类别。

5. 注意空值和 NULL 值

在

GROUP BY

中，NULL 值会被视为相同的值，这意味着所有包含 NULL 的行会被归入同一组。如果需要区分 NULL 和非 NULL 值，可以使用

COALESCE()

或者条件表达式。

二、

ONLY_FULL_GROUP_BY

规则

1. 什么是 ONLY_FULL_GROUP_BY？

ONLY_FULL_GROUP_BY

是 MySQL 中的一个 SQL 模式，它要求在任何包含聚合函数的查询中，所有在

SELECT

子句中出现的非聚合列也必须在

GROUP BY

子句中出现。换句话说，如果一个查询使用了聚合函数，那么除了聚合函数包裹的列以外，所有在

SELECT

子句中出现的列都必须被

GROUP BY

子句引用。

这个规则确保了查询结果的确定性和一致性，避免了由于 SQL 语句的模糊性而导致的潜在错误。

2. 为什么需要 ONLY_FULL_GROUP_BY？

在

ONLY_FULL_GROUP_BY

被引入之前，MySQL 允许在没有

GROUP BY

或者

GROUP BY

不充分的情况下进行查询。这意味着，即使查询中包含了没有被聚合的列，MySQL 也会返回任意一个结果，这可能会导致误导性的结果或数据丢失。

例如，假设我们有以下查询：

SELECT prod_name,COUNT(order_num)FROM products p
JOIN orders o ON p.prod_id = o.prod_id;

这里，

prod_name

列没有被聚合函数包裹，也没有在

GROUP BY

子句中出现。在

ONLY_FULL_GROUP_BY

模式下，这个查询会失败，因为 MySQL 不知道如何从多个可能的

prod_name

值中选择一个来展示。

3.如何启用或禁用 ONLY_FULL_GROUP_BY？

在 MySQL 中，默认情况下

ONLY_FULL_GROUP_BY

是启用的。你可以通过检查

@@sql_mode

系统变量来确认这一点：

SELECT @@sql_mode;

如果

ONLY_FULL_GROUP_BY

已经被启用，并且你想要暂时禁用它（尽管这不是一个推荐的长期解决方案），你可以通过以下命令在会话级禁用它：

SETSESSION sql_mode=(SELECTREPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

或者，你也可以在 MySQL 配置文件（my.cnf 或 my.ini）中修改

sql_mode

设置来全局禁用它。

总结

虽然禁用

ONLY_FULL_GROUP_BY

可能会方便一些查询的编写，但从长远来看，遵循这个规则对于保持数据查询的准确性和一致性至关重要。在编写 SQL 查询时，始终应确保遵循

ONLY_FULL_GROUP_BY

的指导原则，以避免潜在的数据解释错误。