MySQL :: MySQL 9.0 参考手册 :: 14.19.3 MySQL 处理 GROUP BY

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / MySQL 处理 GROUP BY

14.19.3 MySQL 处理 GROUP BY

SQL-92 及更早版本不允许查询选择列表、HAVING 条件或 ORDER BY 列表引用不在 GROUP BY 子句中命名的未聚合列。例如，此查询在标准 SQL-92 中是非法的，因为选择列表中的未聚合 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 列，或者在 GROUP BY 子句中命名它。

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

MySQL 实现函数依赖检测。如果启用了 ONLY_FULL_GROUP_BY SQL 模式（默认情况下启用），MySQL 会拒绝选择列表、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 |
+------+--------+

在使用 ONLY_FULL_GROUP_BY 时，在 SELECT 列表中拥有多个非聚合列也是可能的。在这种情况下，所有此类列都必须在 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 对 GROUP BY 的标准 SQL 用法进行扩展，允许选择列表、HAVING 条件或 ORDER BY 列表引用未聚合列，即使这些列与 GROUP BY 列无关。这会导致 MySQL 接受前面的查询。在这种情况下，服务器可以自由选择每个组中的任何值，因此，除非它们相同，否则选择的 value 都是不确定的，这可能不是您想要的。此外，无法通过添加 ORDER BY 子句来影响从每个组中选择的 value。结果集排序发生在选择 value 之后，而 ORDER BY 不会影响服务器在每个组中选择哪个 value。禁用 ONLY_FULL_GROUP_BY 主要在您知道由于数据的某些属性，每个组中每个未在 GROUP BY 中命名的非聚合列中的所有 value 都相同的情况下有用。

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

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

此查询在启用 ONLY_FULL_GROUP_BY 时可能无效，因为选择列表中的非聚合 address 列未在 GROUP BY 子句中命名

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

如果 name 是 t 的主键，或是一个唯一的 NOT NULL 列，则该查询有效。在这种情况下，MySQL 会识别所选列与分组列在功能上相关。例如，如果 name 是主键，则它的 value 会确定 address 的 value，因为每个组只有一个主键 value，因此只有一行。因此，在组中选择 address value 时没有随机性，也没有必要拒绝该查询。

如果 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 value 实际上都唯一地确定了 address value，那么 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 时，选择列表、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 的查询将被拒绝为无效

该表达式等于 select 列表中的一个表达式
表达式引用的所有列并且属于查询所选表的列都是 select 列表的元素

MySQL 对标准 SQL 的另一个扩展允许在 HAVING 子句中引用 select 列表中带别名的表达式。例如，以下查询返回在 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 会识别该表达式与 select 列表中的表达式之间的相等性。这意味着在启用了 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;