SQL-92及更早的版本不允许select列表、有
条件,或命令
类中未命名的非聚合列集团
条款。例如,该查询在标准SQL-92中是非法的,因为非聚合的名字
选择列表中的列不会出现在集团
:
SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers AS c WHERE o.custid = c.custid;
要使查询在SQL-92中合法,需要的名字
列必须从选择列表中省略或在集团
条款。
SQL:1999及以后的版本允许每个可选特性T301的非聚合,如果它们在功能上依赖于集团
列:如果存在这样的关系的名字
而且custid
,表示查询合法。这就是情况,例如,werecustid
的主键客户
。
MySQL 5.7.5及更高版本实现了函数依赖性的检测。如果ONLY_FULL_GROUP_BY
如果启用SQL模式(默认是这样),MySQL将拒绝select列表、有
条件,或命令
类中未命名的非聚合列集团
子句在功能上也不依赖于它们。(在5.7.5之前,MySQL不检测函数依赖和ONLY_FULL_GROUP_BY
默认情况下不启用。有关5.7.5之前的行为的描述,请参见MySQL 5.6参考手册。)
MySQL 5.7.5及更高版本还允许在索引中没有命名的非聚合列集团
条款时ONLY_FULL_GROUP_BY
启用SQL模式,前提是该列被限制为单个值,示例如下:
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>设置SESSION sql_mode = sys.list_addsql_mode ONLY_FULL_GROUP_BY);SELECT a, SUM(b) FROM mytable WHERE a = 'abc';+------+--------+ | 一个|和(b ) | +------+--------+ | abc | 3000 | +------+--------+
控件中有多个非聚合列也是可能的选择
当使用ONLY_FULL_GROUP_BY
。在这种情况下,每个这样的列必须被限制为一个值,并且所有这样的限制条件必须通过逻辑连接和
,如下所示:
删除表mysql> CREATE TABLE mytable (-> id INT UNSIGNED NOT NULL PRIMARY KEY, -> a VARCHAR(10), -> b VARCHAR(10), -> c INT ->);mysql >插入mytable - >值(1、“abc”、“qrs”,1000年),- - - - - - > (2,' abc ',德国莱茵,2000),- - - - - - >(3“def”“qrs”,4000年),- - - - - - >(4“def”德国莱茵,8000),- - - - - - > (5 ' abc ' qrs, 16000), - - - - - - >(6“def”德国莱茵,32000);mysql >选择@@session.sql_mode;+---------------------------------------------------------------+ | @@ 会话。sql_mode | +---------------------------------------------------------------+ | ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES NO_ENGINE_SUBSTITUTION | +---------------------------------------------------------------+ mysql >选择a, b,和(c)从mytable - > =“abc”,b =“qrs”;+------+------+--------+ | a | | (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 GROUP
查询在以下情况下有效的名字
主键是t
或者是独一无二的非空
列。在这种情况下,MySQL识别所选列在功能上依赖于分组列。例如,如果的名字
是否是主键,它的值决定了它的值地址
因为每个组只有一个主键值,因此只有一行。因此,在选择上没有随机性地址
值,不需要拒绝查询。
查询无效,如果的名字
是不是主键的t
或一个独特的非空
列。在这种情况下,无法推断出函数依赖关系,并出现错误:
SELECT name, address, MAX(age) FROM t GROUP BY name;错误1055 (42000):SELECT列表的表达式#2不在GROUP BY子句中,并且包含非聚合列'mydb.t。address'不依赖于GROUP BY子句中的列;这与sql_mode=only_full_group_by不兼容
如果你知道,对于一个给定的数据集,每一个的名字
价值实际上唯一地决定了地址
值,地址
有效地依赖于的名字
。要告诉MySQL接受查询,可以使用ANY_VALUE ()
功能:
SELECT name, ANY_VALUE(地址),MAX(年龄)FROM GROUP BY name
另外,禁用ONLY_FULL_GROUP_BY
。
然而,前面的例子非常简单。特别是,不太可能对单个主键列进行分组,因为每个组只包含一行。有关演示更复杂查询中的函数依赖性的其他示例,请参见第12.20.4节“函数相关性的检测”。
如果查询具有聚合函数且没有集团
子句,它在选择列表中不能有非聚合列,有
条件,或命令
列表,ONLY_FULL_GROUP_BY
启用:
mysql> SELECT name, MAX(age) FROM;错误1140(42000):在没有GROUP BY的聚合查询中,SELECT列表的表达式#1包含非聚合列'mydb. t.t name';这与sql_mode=only_full_group_by不兼容
没有集团
,有一个单一的群体,它是不确定的的名字
值要为组选择。同样的,ANY_VALUE ()
可以用,如果是无关紧要的哪一个的名字
价值MySQL选择:
SELECT ANY_VALUE(name), MAX(age) FROM t;
在MySQL 5.7.5及更高版本中,ONLY_FULL_GROUP_BY
还会影响使用截然不同的
而且命令
。考虑一个表的情况t
有三个列c1
,c2
,c3
包含以下行:
c1 c2 c3 1 2 A 3 4 B 1 2 C
假设我们执行以下查询,期望结果按c3
:
SELECT DISTINCT c1, c2 FROM t
为了使结果有序,必须首先消除重复。但是要这样做,我们应该保留第一行还是第三行呢?这种随意的选择影响了的保留价值c3
,这反过来又影响了排序,并使其具有随意性。为防止此问题,查询已截然不同的
而且命令
是否被拒绝为无效命令
表达式至少不满足以下条件中的一个:
表达式在选择列表中等于1
表达式引用并属于查询所选表的所有列都是选择列表的元素
标准SQL的另一个MySQL扩展允许在有
子句添加到选择列表中的别名表达式。例如,下面的查询返回的名字
表中只出现一次的值订单
:
SELECT name, COUNT(name) FROM orders GROUP
MySQL扩展允许使用别名有
子句用于聚合列:
SELECT name, COUNT(name) AS c FROM orders GROUP BY name
MySQL 5.7.5之前,启用ONLY_FULL_GROUP_BY
禁用此扩展,因此需要有
子句应使用无别名的表达。
标准SQL只允许列表达式集团
从句,这样的语句是无效的,因为地板(价值/ 100)
是非列表达式:
SELECT id, FLOOR(value/100) FROMtbl_nameGROUP BY id, FLOOR(值/100);
MySQL扩展了标准SQL,允许非列表达式集团
条款,并认为上述声明有效。
标准SQL也不允许使用别名集团
条款。MySQL扩展了标准SQL以允许别名,所以另一种写查询的方法如下:
SELECT id, FLOOR(值/100)AS val FROMtbl_nameGROUP BY id, val;
别名瓦尔
的列表达式集团
条款。
中存在非列表达式时集团
子句,MySQL识别该表达式与select列表中的表达式相等。这意味着ONLY_FULL_GROUP_BY
启用SQL模式,查询包含GROUP BY id, FLOOR(值/100)
是有效的,因为地板()
表达式出现在选择列表中。然而,MySQL不尝试识别函数依赖集团
非列表达式,因此下面的查询无效ONLY_FULL_GROUP_BY
的简单公式,即使第三个选择的表达式是id
列和地板()
表达的集团
条款:
SELECT id, FLOOR(值/100),id+FLOOR(值/100)FROMtbl_nameGROUP BY id, FLOOR(值/100);
一种解决方法是使用派生表:
SELECT id, F, id+F FROM (SELECT id, FLOOR(value/100) AS F FROM)tbl_nameGROUP BY id, FLOOR(值/100))AS dt;