最近，新版 MySQL 服务器开始为原本可以正常工作的查询生成 1055 错误。本章解释了这些错误的原因。MySQL 团队一直在努力弃用 GROUP BY 的非标准扩展，或至少让查询编写者更难被其“烧伤”。

第 15.1 节：误用 GROUP BY 导致不可预测的结果：墨菲定律

SELECT item.item_id, uses.category, /* 非标准 */ COUNT(*) number_of_uses
FROM item
JOIN uses ON item.item_id = uses.item_id
GROUP BY item.item_id;

该查询将显示 item 表中的行，并显示 uses 表中相关行的计数。它还显示名为 uses.category 的列的值。

在出现 ONLY_FULL_GROUP_BY 标志之前，此查询在 MySQL 中可以正常工作。它使用了 MySQL 对 GROUP BY 的非标准扩展。

但该查询有一个问题：如果 uses 表中有多行匹配 JOIN 条件，MySQL 只返回其中一行的 category 列。哪一行？查询编写者和应用程序用户无法提前知道。形式上说，它是不可预测的：MySQL 可以返回任何它想要的值。

“不可预测”类似于“随机”，但有一个显著的区别。人们可能期望随机选择会不时变化。因此，如果选择是随机的，你可能会在调试或测试期间发现它。不可预测的结果更糟糕：MySQL 每次查询都返回相同的结果，直到它不再返回相同的结果。有时是 MySQL 服务器的新版本导致了不同的结果。有时是表变大了导致了问题。可能出错的事情，终将出错，而且是在你最不期望的时候。这就是墨菲定律。

MySQL 团队一直在努力使开发人员更难犯这个错误。5.7 序列中的较新版本有一个名为 ONLY_FULL_GROUP_BY 的 sql_mode 标志。当设置该标志时，MySQL 服务器返回 1055 错误，并拒绝运行此类查询。

第 15.2 节：误用 SELECT * 的 GROUP BY 以及如何修复

有时查询看起来像这样，在 SELECT 子句中带有 *：

SELECT item.*, /* 非标准 */ COUNT(*) number_of_uses
FROM item
JOIN uses ON item.item_id = uses.item_id
GROUP BY item.item_id;

这样的查询需要重构以符合 ONLY_FULL_GROUP_BY 标准。

为此，我们需要一个子查询来正确使用 GROUP BY 返回每个 item_id 的 number_of_uses 值。这个子查询很短很简单，因为它只需要查看 uses 表：

SELECT item_id, COUNT(*) number_of_uses
FROM uses
GROUP BY item_id;

然后，我们可以将该子查询与 item 表连接：

SELECT item.*, usecount.number_of_uses
FROM item
JOIN (SELECT item_id, COUNT(*) number_of_uses FROM uses GROUP BY item_id) usecount
    ON item.item_id = usecount.item_id;

这使得 GROUP BY 子句简单且正确，同时也允许我们使用 * 说明符。

注意：尽管如此，明智的开发者在任何情况下都应避免使用 * 说明符。列出你想要的列通常更好。

第 15.3 节：ANY_VALUE()

SELECT item.item_id, ANY_VALUE(uses.tag) tag, COUNT(*) number_of_uses
FROM item
JOIN uses ON item.item_id = uses.item_id
GROUP BY item.item_id;

这将显示 item 表中的行、相关行的计数以及相关表 uses 中的一个值。

你可以将 ANY_VALUE() 函数视为一种奇怪的聚合函数。它不是返回计数、总和或最大值，而是指示 MySQL 服务器从组中任意选择一个值。这是一种解决错误 1055 的方法。

在生产应用程序的查询中使用 ANY_VALUE() 时要小心。

它实际上应该被称为 SURPRISE_ME()。它返回 GROUP BY 组中某一行的值。返回哪一行是不确定的。这意味着完全由 MySQL 服务器决定。形式上说，它返回一个不可预测的值。

服务器不会随机选择一个值，它比这更糟糕：它每次运行查询时都返回相同的值，直到它不再返回相同的值。当表增长或收缩、服务器内存变化、服务器版本变化、或火星逆行（无论那意味着什么），或无缘无故时，它都可能改变。

你已经被警告过了。

第 15.4 节：正确和错误地使用 GROUP BY

SELECT item.item_id, item.name, /* 不符合 SQL-92 */ COUNT(*) number_of_uses
FROM item
JOIN uses ON item.item_id = uses.item_id
GROUP BY item.item_id;

在 SQL-92 标准（以及 MySQL 的 ONLY_FULL_GROUP_BY 模式）中，SELECT 子句中的每一列必须满足以下两个条件之一：

  1. 在 GROUP BY 子句中提及，或者
  2. 是聚合函数（如 COUNT()、MIN() 等）的参数。

此示例的 SELECT 子句提到了 item.name，该列不满足上述任一条件。如果 SQL 模式包含 ONLY_FULL_GROUP_BY，MySQL 5.6 及更早版本将拒绝此查询。

要使此示例查询符合 SQL-92 标准，可以通过修改 GROUP BY 子句来实现：

SELECT item.item_id, item.name, COUNT(*) number_of_uses
FROM item
JOIN uses ON item.item_id = uses.item_id
GROUP BY item.item_id, item.name;

后来的 SQL-99 标准允许 SELECT 语句从分组键中省略未聚合的列，前提是数据库管理系统能够证明这些列与分组键列之间存在函数依赖关系。由于 item.name 在函数上依赖于 item.item_id，因此最初的示例在 SQL-99 中是有效的。MySQL 在 5.7 版本中获得了函数依赖证明器。在 ONLY_FULL_GROUP_BY 模式下，最初的示例可以正常工作。