MySQL :: MySQL 8.4 参考手册 :: 14.19.3 MySQL 对 GROUP BY 的处理

版本 8.4

8.0 日语

MySQL 8.4 参考手册 / ... / MySQL 对 GROUP BY 的处理

14.19.3 MySQL 对 GROUP BY 的处理

SQL-92 及更早版本不允许在 SELECT 列表、HAVING 条件或 ORDER BY 列表中引用未聚合的列，这些列在 GROUP BY 子句中没有命名。例如，这个查询在标准 SQL-92 中是非法的，因为 SELECT 列表中未聚合的 name 列没有出现在 GROUP BY 子句中。

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

为了使查询在 SQL-92 中合法，name 列必须从 SELECT 列表中省略或在 GROUP BY 子句中命名。

SQL:1999 及更高版本允许根据可选功能 T301 使用此类未聚合的列，前提是它们在功能上依赖于 GROUP BY 列：如果 name 和 custid 之间存在这种关系，则查询是合法的。例如，如果 custid 是 customers 的主键，则会出现这种情况。

MySQL 实现了函数依赖的检测。如果启用了 ONLY_FULL_GROUP_BY SQL 模式（默认情况下启用），则 MySQL 会拒绝 SELECT 列表、HAVING 条件或 ORDER BY 列表中引用未聚合列的查询，这些列既不在 GROUP BY 子句中命名，也不在功能上依赖于它们。

当启用 SQL ONLY_FULL_GROUP_BY 模式时，MySQL 还允许在 GROUP BY 子句中未命名的非聚合列，前提是该列限制为单个值，如下面的示例所示。

mysql> CREATE TABLE mytable (
    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
    ->    a VARCHAR(10),
    ->    b INT
    -> );

mysql> INSERT INTO mytable
    -> VALUES (1, 'abc', 1000),
    ->        (2, 'abc', 2000),
    ->        (3, 'def', 4000);

mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

mysql> SELECT a, SUM(b) FROM mytable WHERE a = 'abc';
+------+--------+
| a    | SUM(b) |
+------+--------+
| abc  |   3000 |
+------+--------+

在使用 SELECT 时，也可以在 SELECT 列表中有多个非聚合列，同时使用 ONLY_FULL_GROUP_BY。在这种情况下，每个此类列在 WHERE 子句中都必须限制为单个值，并且所有此类限制条件都必须通过逻辑 AND 连接，如下所示。

mysql> DROP TABLE IF EXISTS mytable;

mysql> CREATE TABLE mytable (
    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
    ->    a VARCHAR(10),
    ->    b VARCHAR(10),
    ->    c INT
    -> );

mysql> INSERT INTO mytable
    -> VALUES (1, 'abc', 'qrs', 1000),
    ->        (2, 'abc', 'tuv', 2000),
    ->        (3, 'def', 'qrs', 4000),
    ->        (4, 'def', 'tuv', 8000),
    ->        (5, 'abc', 'qrs', 16000),
    ->        (6, 'def', 'tuv', 32000);

mysql> SELECT @@session.sql_mode;
+---------------------------------------------------------------+
| @@session.sql_mode                                            |
+---------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------+

mysql> SELECT a, b, SUM(c) FROM mytable
    ->     WHERE a = 'abc' AND b = 'qrs';
+------+------+--------+
| a    | b    | SUM(c) |
+------+------+--------+
| abc  | qrs  |  17000 |
+------+------+--------+

如果禁用了 ONLY_FULL_GROUP_BY，则 MySQL 对标准 SQL 中 GROUP BY 用法的扩展允许 SELECT 列表、HAVING 条件或 ORDER BY 列表引用未聚合的列，即使这些列在功能上不依赖于 GROUP BY 列。这会导致 MySQL 接受前面的查询。在这种情况下，服务器可以自由选择每个组中的任何值，因此除非它们相同，否则选择的值是不确定的，这可能不是您想要的。此外，通过添加 ORDER BY 子句也不能影响从每个组中选择值。结果集排序发生在选择值之后，并且 ORDER BY 不会影响服务器在每个组中选择哪个值。禁用 ONLY_FULL_GROUP_BY 主要在以下情况下有用：您知道，由于数据的某些属性，GROUP BY 中未命名的每个未聚合列中的所有值对于每个组都是相同的。

您可以通过使用 ANY_VALUE() 来引用未聚合的列，从而在不禁用 ONLY_FULL_GROUP_BY 的情况下实现相同的效果。

下面的讨论演示了函数依赖、当函数依赖不存在时 MySQL 生成的错误消息，以及在函数依赖不存在的情况下导致 MySQL 接受查询的方法。

如果启用了 ONLY_FULL_GROUP_BY，则此查询可能无效，因为 SELECT 列表中未聚合的 address 列在 GROUP BY 子句中未命名。

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

如果 name 是 t 的主键或唯一的 NOT NULL 列，则查询有效。在这种情况下，MySQL 会识别出所选列在功能上依赖于分组列。例如，如果 name 是主键，则其值决定 address 的值，因为每个组只有一个主键值，因此只有一行。因此，在组中选择 address 值时没有随机性，也不需要拒绝查询。

如果 name 不是 t 的主键或唯一的 NOT NULL 列，则查询无效。在这种情况下，无法推断出函数依赖关系，并且会发生错误。

mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by

如果您知道，对于给定的数据集，每个 name 值实际上唯一地确定了 address 值，则 address 在功能上实际上依赖于 name。要告诉 MySQL 接受查询，可以使用 ANY_VALUE() 函数。

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

或者，禁用 ONLY_FULL_GROUP_BY。

但是，前面的示例非常简单。特别是，您不太可能在单个主键列上进行分组，因为每个组只包含一行。有关在更复杂的查询中演示函数依赖的其他示例，请参见第 14.19.4 节，“函数依赖的检测”。

如果查询具有聚合函数但没有 GROUP BY 子句，则在启用了 ONLY_FULL_GROUP_BY 的情况下，它不能在 SELECT 列表、HAVING 条件或 ORDER BY 列表中具有未聚合的列。

mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'mydb.t.name'; this
is incompatible with sql_mode=only_full_group_by

如果没有 GROUP BY，则只有一个组，并且为该组选择哪个 name 值是不确定的。同样，如果 MySQL 选择哪个 name 值无关紧要，则可以使用 ANY_VALUE()

SELECT ANY_VALUE(name), MAX(age) FROM t;

ONLY_FULL_GROUP_BY 也会影响使用 DISTINCT 和 ORDER BY 的查询的处理。考虑表 t 的情况，它有三列 c1、c2 和 c3，包含以下行

假设我们执行以下查询，期望结果按 c3 排序

SELECT DISTINCT c1, c2 FROM t ORDER BY c3;

要对结果进行排序，必须先消除重复项。但是要做到这一点，我们应该保留第一行还是第三行？这种任意选择会影响 c3 的保留值，进而影响排序并使其也变得任意。为了防止出现此问题，如果任何 ORDER BY 表达式不满足以下至少一项条件，则具有 DISTINCT 和 ORDER BY 的查询将被拒绝为无效

表达式等于选择列表中的一个
表达式引用的所有列以及属于查询的所选表的列都是选择列表的元素

标准 SQL 的另一个 MySQL 扩展允许在 HAVING 子句中引用选择列表中的别名表达式。例如，以下查询返回仅在表 orders 中出现一次的 name 值

SELECT name, COUNT(name) FROM orders
  GROUP BY name
  HAVING COUNT(name) = 1;

MySQL 扩展允许在聚合列的 HAVING 子句中使用别名

SELECT name, COUNT(name) AS c FROM orders
  GROUP BY name
  HAVING c = 1;

标准 SQL 仅允许 GROUP BY 子句中的列表达式，因此这样的语句无效，因为 FLOOR(value/100) 是非列表达式

SELECT id, FLOOR(value/100)
  FROM tbl_name
  GROUP BY id, FLOOR(value/100);

MySQL 扩展了标准 SQL 以允许在 GROUP BY 子句中使用非列表达式，并认为前面的语句有效。

标准 SQL 也不允许在 GROUP BY 子句中使用别名。MySQL 扩展了标准 SQL 以允许别名，因此编写查询的另一种方法如下

SELECT id, FLOOR(value/100) AS val
  FROM tbl_name
  GROUP BY id, val;

别名 val 被视为 GROUP BY 子句中的列表达式。

如果 GROUP BY 子句中存在非列表达式，MySQL 会识别该表达式与选择列表中的表达式之间的相等性。这意味着在启用了 ONLY_FULL_GROUP_BY SQL 模式的情况下，包含 GROUP BY id, FLOOR(value/100) 的查询是有效的，因为相同的 FLOOR() 表达式出现在选择列表中。但是，MySQL 不会尝试识别对 GROUP BY 非列表达式的函数依赖关系，因此以下查询在启用了 ONLY_FULL_GROUP_BY 的情况下无效，即使第三个选择的表达式是 id 列和 GROUP BY 子句中的 FLOOR() 表达式的简单公式

SELECT id, FLOOR(value/100), id+FLOOR(value/100)
  FROM tbl_name
  GROUP BY id, FLOOR(value/100);

一种解决方法是使用派生表

SELECT id, F, id+F
  FROM
    (SELECT id, FLOOR(value/100) AS F
     FROM tbl_name
     GROUP BY id, FLOOR(value/100)) AS dt;