MySQL 数据库中 GROUP BY 的使用注意事项

本文介绍 MySQL 数据库中 GROUP BY 的使用注意事项 (OceanBase 数据库中 MySQL 模式下也适用)。

MySQL GROUP BY 使用注意事项

GROUP BY 使用注意事项，简单描述如下：

根据 SQL 标准，在 SELECT 子句、HAVING 子句或 ORDER BY 子句中引用的列，必须是在 GROUP BY 子句中指定的列 。

MySQL GROUP BY 使用示例

示例 1 - SELECT 子句

• 错误使用 GROUP BY 示例 1 。

  SELECT name, address, MAX(age) FROM t GROUP BY name;
  

  错误原因为以上 SQL SELECT 中的列 address 未出现在 GROUP BY 子句中。

  注意

  示例 1 中 name 列为非主键列。

• 正确使用 GROUP BY 示例 1 。

  SELECT name, MAX(age) FROM t GROUP BY name;
  

  去掉 address 列，因为它未在 GROUP BY 子句中。

  name 出现在了 GROUP BY 子句中，所以可以在 SELECT 子句中引用。

示例 2 - ORDER BY 子句

• 错误使用 GROUP BY 示例 2 。

  SELECT department, MAX(age) FROM t GROUP BY department ORDER BY department, gender;
  

  错误原因为以上 SQL ORDER BY 中的列 gender 未出现在 GROUP BY 子句中。

  注意

  示例 2 中 department 列为非主键列。

• 正确使用 GROUP BY 示例 2 。

  SELECT department, gender, MAX(age) FROM t GROUP BY department, gender ORDER BY department, gender;
  

  在 GROUP BY 子句中增加 gender 列，以使在 ORDER BY 中的列都在 GROUP BY 子句中；同时，在 SELECT 中也可以考虑增加 gender 列以增加可读性。

检验 GROUP BY 使用是否符合 SQL 标准

当 sql_mode 中包含 ONLY_FULL_GROUP_BY 时，如果 SQL 执行不报错，则 GROUP BY 使用正确，符合 SQL 标准。

详情参考 MySQL 官网 MySQL Handling of GROUP BY。

若 sql_mode 启用 ONLY_FULL_GROUP_BY SQL 模式，参考如下内容。

MySQL implements detection of functional dependence. If the [ONLY_FULL_GROUP_BY](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_only_full_group_by)SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them.

SQL-92 标准

在 SELECT 子句、HAVING 子句或 ORDER BY 子句中引用的列，必须是在 GROUP BY 子句中指定的列。

SQL-92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause.

SQL:1999 (SQL 3)，也称为 SQL-99

除上述 SQL 92 标准中规定的一般情况外，允许一些特殊情况，但这些特殊情况平时基本用不到，如 GROUP BY 后是主键、WHERE 子句中该列已指定为了常量等。

SQL:1999 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY column

GROUP BY 非标准用法后果

简而言之，MySQL 允许非标准用法，但结果是不确定的，即很可能不是你想要的结果。

实际场景有遇到的此类情况是，在一些环境基本都是你想要的结果，但在其他环境则是另外的结果。

若禁用 ONLY_FULL_GROUP_BY SQL 模式，参考如下内容。

If [ONLY_FULL_GROUP_BY](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_only_full_group_by)is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the preceding query. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want.

适用版本

OceanBase 数据库所有版本。