不同版本的MySQL Group By功能 [英] MySQL Group By functionality in different version

问题描述

以下是一个简单的SQL查询:

Following is a simple SQL query:

SELECT * FROM *table_name*
GROUP BY *column_name*

在我的系统中，我有MySQL 5.5.它工作正常. 而在我朋友的系统中，他有MySQL 5.7，并且他收到以下错误:

In my system I have MySQL 5.5. It is working absolutely fine. Whereas in my friend's system he have MySQL 5.7, and he is getting the following error:

错误1055(42000):SELECT列表的表达式#1不在GROUP BY中 子句，包含非聚合列'testdb.assetentry.entryId' 在功能上不依赖于GROUP BY子句中的列； 这与sql_mode = only_full_group_by

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'testdb.assetentry.entryId' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

由于版本不同，因此可以清楚地看到发生了这种情况.

It is clearly visible this is happening because the versions are different.

但是我想知道的是这背后的原因.

But what I want to know is the reason behind this.

任何人都可以解释一下.

Can any one please explain.

推荐答案

首先，请阅读这不是SQL Standard的行为.

It is not SQL Standard behaviour.

12.16.3 MySQL对GROUP BY的处理

要禁用MySQL GROUP BY扩展并启用标准SQL行为，请启用ONLY_FULL_GROUP_BY SQL模式.在这种情况下，除非包含在聚合函数中，否则不能在选择列表或HAVING子句中使用GROUP BY子句中未命名的列.

To disable the MySQL GROUP BY extension and enable standard SQL behavior, enable the ONLY_FULL_GROUP_BY SQL mode. In this case, columns not named in the GROUP BY clause cannot be used in the select list or HAVING clause unless enclosed in an aggregate function.

在第二台服务器上，您似乎已经激活了ONLY_FULL_GROUP_BY模式.

It looks like on second server you have acitivated ONLY_FULL_GROUP_BY mode.

SELECT @@sql_mode;

您可以在MySQL 5.5上模拟这种行为:

You could simulate this behaviour on your MySQL 5.5:

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY';

SELECT *
FROM tab
GROUP BY col;
-- tab.col2' isn't in GROUP BY

SqlFiddleDemo

来自 MySQL 5.7 :

更多地实现了ONLY_FULL_GROUP_BY SQL模式 复杂，不再拒绝确定性查询 以前被拒绝了. 因此，现在只有ONLY_FULL_GROUP_BY 默认情况下启用，以禁止包含以下内容的不确定查询 不能保证在一个组中唯一确定这些表达式.

Implementation of the ONLY_FULL_GROUP_BY SQL mode has been made more sophisticated, to no longer reject deterministic queries that previously were rejected. In consequence, ONLY_FULL_GROUP_BY is now enabled by default, to prohibit nondeterministic queries containing expressions not guaranteed to be uniquely determined within a group.

这篇关于不同版本的MySQL Group By功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！