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 5.7.5 及更高版本实现了功能依赖检测。如果
ONLY_FULL_GROUP_BY
启用 SQL 模式(默认情况下启用),MySQL 将拒绝选择列表、HAVING
条件或
ORDER BY
列表引用既未在GROUP BY
子句中命名也未在功能上依赖于它们的非聚合列的查询。(在 5.7.5 之前,MySQL 不检测功能依赖,
ONLY_FULL_GROUP_BY
默认情况下不启用。有关 5.7.5 之前行为的描述,请参阅MySQL 5.6 参考手册。)
MySQL 5.7.5 及更高版本还允许在启用 SQL 模式GROUP BY
时未在子句
中命名的非聚合列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 |
+------+--------+
SELECT
使用 时,列表
中也可能有多个非聚合列ONLY_FULL_GROUP_BY
。在这种情况下,每个此类列都必须限制为单个值,并且所有此类限制条件都必须由 logical 连接
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
允许选择列表、
HAVING
条件或ORDER
BY
列表引用非聚合列,即使这些列在功能上不依赖于GROUP
BY
列。这会导致 MySQL 接受前面的查询。在这种情况下,服务器可以自由地从每个组中选择任何值,因此除非它们相同,否则所选的值是不确定的,这可能不是您想要的。此外,从每个组中选择值不会受到添加ORDER BY
子句的影响。结果集排序发生在选择值之后,并且
ORDER BY
不影响服务器选择每个组中的哪个值。ONLY_FULL_GROUP_BY
当您知道由于数据的某些属性,每个组中未命名的每个非聚合列中的所有值
GROUP BY
对于每个组都相同时,
禁用
主要有用。
ONLY_FULL_GROUP_BY
您可以通过使用
ANY_VALUE()
引用非聚合列
来实现相同的效果而无需禁用
。
下面的讨论演示了函数依赖,当函数依赖不存在时 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
是一个主键,它的值决定了的值,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
.
然而,前面的示例非常简单。特别是,您不太可能对单个主键列进行分组,因为每个组只包含一行。有关在更复杂的查询中演示函数依赖的其他示例,请参阅第 12.20.4 节,“函数依赖的检测”。
如果查询具有聚合函数但没有子句,则它在选择列表、条件或
启用
的列表
GROUP
BY
中不能有非聚合列:HAVING
ORDER BY
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
如果没有GROUP BY
,则只有一个组,并且不确定name
为该组选择哪个值。ANY_VALUE()
如果name
MySQL 选择哪个值
并不重要,这里也
可以使用:
SELECT ANY_VALUE(name), MAX(age) FROM t;
在 MySQL 5.7.5 及更高版本中,ONLY_FULL_GROUP_BY
还会影响使用
DISTINCT
and的查询处理ORDER BY
。考虑t
具有三列c1
,c2
并且
c3
包含以下行的表的情况:
c1 c2 c3
1 2 A
3 4 B
1 2 C
假设我们执行以下查询,期望结果按以下顺序排序c3
:
SELECT DISTINCT c1, c2 FROM t ORDER BY c3;
要对结果进行排序,必须先消除重复项。但是这样做,我们应该保留第一行还是第三行?这种任意选择会影响 的保留值c3
,进而影响排序并使其具有任意性。为防止出现此问题,
如果任何
表达式不满足以下至少一个条件
,则查询已被视为无效DISTINCT
而被拒绝:ORDER BY
ORDER BY
表达式等于选择列表中的一个
表达式引用并属于查询的选定表的所有列都是选择列表的元素
标准 SQL 的另一个 MySQL 扩展允许在HAVING
子句中引用选择列表中的别名表达式。例如,以下查询返回
name
在表中只出现一次的值
orders
:
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;
在 MySQL 5.7.5 之前,启用
ONLY_FULL_GROUP_BY
会禁用此扩展,因此需要HAVING
使用无别名表达式编写子句。
标准 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
列的简单公式和
子句
FLOOR()
中的表达式
:GROUP BY
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;