12.20.3 MySQL 对 GROUP BY 的处理

# 12.20.3 MySQL 对 GROUP BY 的处理

SQL-92 和更早的版本不允许选择列表的查询，拥有条件，或订购方式列表引用未在通过...分组条款。例如，此查询在标准 SQL-92 中是非法的，因为未聚合的姓名选择列表中的列未出现在通过...分组：

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 中合法，姓名列必须从选择列表中省略或在通过...分组条款。

SQL:1999 及更高版本允许每个可选功能 T301 的此类非聚合，如果它们在功能上依赖于通过...分组columns：如果存在这样的关系姓名和卡斯蒂德，查询是合法的。这将是这种情况，例如，卡斯蒂德的主键顾客.

MySQL实现了功能依赖的检测。如果ONLY_FULL_GROUP_BYSQL 模式已启用（默认情况下），MySQL 拒绝选择列表的查询，拥有条件，或订购方式list 指的是未在通过...分组子句在功能上也不依赖于它们。

MySQL 还允许未在通过...分组SQL 时的子句ONLY_FULL_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.在这种情况下，每个这样的列都必须限制为在哪里子句，并且所有这些限制条件必须由逻辑连接和，如此处所示：

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 使用的扩展通过...分组允许选择列表，拥有条件，或订购方式列表以引用非聚合列，即使这些列在功能上不依赖于通过...分组列。这会导致 MySQL 接受前面的查询。在这种情况下，服务器可以自由地从每个组中选择任何值，因此除非它们相同，否则选择的值是不确定的，这可能不是您想要的。此外，从每个组中选择值不会受到添加订购方式条款。结果集排序发生在选择值之后，并且订购方式不影响服务器选择的每个组中的哪个值。禁用ONLY_FULL_GROUP_BY主要是当您知道由于数据的某些属性，每个非聚合列中的所有值都没有在通过...分组每个组都是相同的。

您可以在不禁用的情况下达到相同的效果ONLY_FULL_GROUP_BY通过使用ANY_VALUE()引用非聚合列。

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

此查询可能无效ONLY_FULL_GROUP_BY启用，因为非聚合地址选择列表中的列未在通过...分组条款：

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

查询有效，如果姓名是一个主键吨或者是独一无二的非空柱子。在这种情况下，MySQL 识别出所选列在功能上依赖于分组列。例如，如果姓名是一个主键，它的值决定了地址因为每个组只有一个主键值，因此只有一行。因此，选择没有随机性地址组中的值，无需拒绝查询。

查询无效，如果姓名不是的主键吨或独一无二的非空柱子。在这种情况下，无法推断出任何函数依赖关系并发生错误：

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

如果你知道，*对于给定的数据集，*每个姓名值实际上唯一地决定了地址价值，地址在功能上有效地依赖于姓名.要告诉 MySQL 接受查询，可以使用ANY_VALUE()功能：

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

或者，禁用ONLY_FULL_GROUP_BY.

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

如果查询具有聚合函数并且没有通过...分组子句，它不能在选择列表中有非聚合列，拥有条件，或订购方式列出与ONLY_FULL_GROUP_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

没有通过...分组，有一个组，它是不确定的姓名为组选择的值。这里也，ANY_VALUE()可以使用，如果它无关紧要姓名MySQL 选择的值：

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

ONLY_FULL_GROUP_BY也影响使用查询的处理清楚的和订购方式.考虑一个表的情况吨三列c1,c2，和c3包含这些行：

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

SELECT DISTINCT c1, c2 FROM t ORDER BY c3;

要对结果排序，必须首先消除重复项。但要这样做，我们应该保留第一行还是第三行？这种任意选择会影响c3，这反过来又会影响排序并使其变得任意。为防止出现此问题，具有清楚的和订购方式被拒绝为无效（如果有）订购方式表达式至少不满足以下条件之一：

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

标准 SQL 的另一个 MySQL 扩展允许在拥有子句到选择列表中的别名表达式。例如，以下查询返回姓名表中仅出现一次的值命令：

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

MySQL 扩展允许在拥有聚合列的子句：

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

标准 SQL 仅允许在通过...分组子句，所以这样的陈述是无效的，因为楼层（价值/100）是一个非列表达式：

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

MySQL 扩展了标准 SQL 以允许在通过...分组子句并认为前面的陈述有效。

标准 SQL 也不允许在通过...分组条款。MySQL 扩展了标准 SQL 以允许别名，因此编写查询的另一种方法如下：

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

别名值被认为是列表达式通过...分组条款。

在存在非列表达式的情况下通过...分组子句，MySQL 识别该表达式和选择列表中的表达式之间的相等性。这意味着随着ONLY_FULL_GROUP_BY启用 SQL 模式，查询包含按 id 分组，楼层（值/100）是有效的，因为同样地面（）表达式出现在选择列表中。但是，MySQL 不会尝试识别函数依赖于通过...分组非列表达式，因此以下查询无效ONLY_FULL_GROUP_BY启用，即使第三个选定的表达式是id列和地面（）中的表达通过...分组条款：

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;

← 12.20.2 GROUP BY 修饰符 12.20.4 检测功能依赖 →