mysql err 报错1055 isn't in GROUP BY

最新推荐文章于 2024-08-24 16:03:29 发布

原创最新推荐文章于 2024-08-24 16:03:29 发布 · 4.7k 阅读

1 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

12 篇文章

订阅专栏

本文详述了如何解决MySQL中因ONLY_FULL_GROUP_BY设置引发的错误，提供了修改my.ini配置文件的具体步骤，以及使用ANY_VALUE()函数和调整SQL查询以确保符合ONLY_FULL_GROUP_BY规则的方法。

出错背景：

备份服务器数据库数据到本地，进行项目测试,数据查询失败，

后台报错：[err] 1055 -- 'xxx' isn't in GROUP BY

解决办法：

初步判断是数据库（版本？配置？）的问题

进入mysql 的my.ini配置文件

ctrl+f 搜索找到字段：ONLY_FULL_GROUP_BY，将其删除即可

修改之后，需要重启mysql

找到电脑服务设置，重新启动MYSQL服务

出错原因：

SQL-92和更早版本不允许选择列表，HAVING条件或ORDER BY列表引用未在GROUP BY子句中命名的非聚合列的查询。例如，此查询在标准SQL-92中是非法的，因为name选择列表中的非聚合列不会出现在GROUP BY：
<code class="language-sql">SELECT o.custid, c.name, MAX(o.payment)
 FROM orders AS o, customers AS c
 WHERE o.custid = c.custid
 GROUP BY o.custid;</code>
要使查询在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参考手册。）

如果ONLY_FULL_GROUP_BY禁用，则标准SQL使用的MySQL扩展 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 子句中命名：
<code class="language-sql">SELECT name, address, MAX(age) FROM t GROUP BY name;</code>
如果查询是name主键t或是唯一NOT NULL列，则查询有效。在这种情况下，MySQL会识别所选列在功能上依赖于分组列。例如，如果name是主键，则其值确定值，address因为每个组只有一个主键值，因此只有一行。因此，address组中值的选择没有随机性，也不需要拒绝查询。

如果查询name不是主键t或唯一NOT NULL列，则查询无效。在这种情况下，不能推断出功能依赖性并发生错误：
<code class="language-sql">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</code>
如果您知道，对于给定的数据集， 每个name值实际上唯一地确定该address值，address 在功能上是有效依赖的 name。要告诉MySQL接受查询，可以使用以下ANY_VALUE()函数：
<code class="language-sql">SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;</code>
或者，禁用 ONLY_FULL_GROUP_BY。

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

如果查询具有聚合函数且没有GROUP BY子句，则在启用的选择列表，HAVING条件或 ORDER BY列表中不能包含非聚合列ONLY_FULL_GROUP_BY：
<code class="language-sql">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</code>
没有GROUP BY，只有一个组，它是不确定的，name为组选择值。ANY_VALUE()如果nameMySQL选择的值不重要，也可以使用这里：
<code class="language-sql">SELECT ANY_VALUE(name), MAX(age) FROM t;</code>
在MySQL 5.7.5及更高版本中，ONLY_FULL_GROUP_BY 还会影响使用DISTINCT和查询的处理 ORDER BY。考虑表的情况下，t有三列c1，c2以及 c3包含这些行：
<code class="language-none">c1 c2 c3
1 2 A
3 4 B
1 2 C</code>
假设我们执行以下查询，期望按以下顺序排序结果c3：
<code class="language-sql">SELECT DISTINCT c1, c2 FROM t ORDER BY c3;</code>
要订购结果，必须首先删除重复项。但要这样做，我们应该保留第一行还是第三行？这种任意选择会影响保留值c3，从而影响排序并使其任意。要防止出现此问题，如果任何表达式不满足以下条件中的至少一个条件，则该查询具有 DISTINCT和ORDER BY被拒绝为无效ORDER BY：

表达式在选择列表中等于1

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

标准SQL的另一个MySQL扩展允许HAVING子句中的引用到选择列表中的别名表达式。例如，以下查询返回 name在表中只出现一次的值orders：
<code class="language-sql">SELECT name, COUNT(name) FROM orders
 GROUP BY name
 HAVING COUNT(name) = 1;</code>
MySQL扩展允许HAVING在聚合列的子句中使用别名：
<code class="language-sql">SELECT name, COUNT(name) AS c FROM orders
 GROUP BY name
 HAVING c = 1;</code>
注意

在MySQL 5.7.5之前，启用 ONLY_FULL_GROUP_BY禁用此扩展，因此要求HAVING 使用非混淆表达式编写子句。

标准SQL只允许GROUP BY子句中的列表达式，因此这样的语句无效，因为它FLOOR(value/100)是一个非列表达式：
<code class="language-sql">SELECT id, FLOOR(value/100)
 FROM tbl_name
 GROUP BY id, FLOOR(value/100);</code>
MySQL扩展标准SQL以允许GROUP BY子句中的非列表达式，并认为前面的语句有效。

标准SQL也不允许GROUP BY子句中的别名。MySQL扩展标准SQL以允许别名，因此编写查询的另一种方法如下：
<code class="language-sql">SELECT id, FLOOR(value/100) AS val
 FROM tbl_name
 GROUP BY id, val;</code>
别名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：
<code class="language-sql">SELECT id, FLOOR(value/100), id+FLOOR(value/100)
 FROM tbl_name
 GROUP BY id, FLOOR(value/100);</code>
解决方法是使用派生表：

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

参考地址：https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html