为什么 Mysql 的 Group By 和 Oracle 的 Group by 行为不同

2021-12-26 00:00:00 sql group-by mysql oracle aggregate-functions

为什么Mysql的Group By和Oracle的Group by行为不同

Why Mysql's Group By and Oracle's Group by behaviours are different

我多次发现 Mysql 的 groupBy 功能和 Oracle 的 GroupBy 功能表现不同

I found many times that Mysql's groupBy functionality and Oracle's GroupBy funcnality are behaving different

很多时候我在 Oracle 中发现错误(实际上是错误的查询)但是 Mysql 会给出结果

Many times I found error in Oracle(which is actually wrong query) but Mysql will give result in to this

这种Mysql奇怪行为背后有什么原因吗

so is there any reason behind this Mysql weird behavior

推荐答案

MySQL 设计者将他们的非标准扩展添加到 GROUP BY 中，试图使开发更容易，某些查询更高效.

The MySQL designers put in their nonstandard extension to GROUP BY in an attempt to make development easier and certain queries more efficient.

这是他们的理由.

https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

有一个名为 ONLY_FULL_GROUP_BY 的服务器模式，它禁用非标准扩展.您可以使用此语句设置此模式.

There is a server mode called ONLY_FULL_GROUP_BY which disables the nonstandard extensions. You can set this mode using this statement.

SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY'

这是该页面的引述，并添加了重点.

Here's a quote from that page, with emphasis added.

如果 ONLY_FULL_GROUP_BY 被禁用，GROUP BY 的标准 SQL 使用的 MySQL 扩展允许选择列表、HAVING 条件或 ORDER BY 列表引用非聚合列，即使这些列在功能上不依赖于 GROUP BY 列...在这种情况下，服务器可以自由选择任何来自每个组的值，因此除非它们相同，否则选择的值是不确定的，这可能不是您想要的.

If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns... In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want.

这里的重要词是不确定性.这是什么意思?这意味着随机，但更糟.如果服务器选择随机值，这意味着它会在不同的查询中返回不同的值，因此您在测试软件时有机会发现问题.但是不确定性在这种情况下意味着服务器每次都选择相同的值，直到它没有.

The important word here is nondeterministic. What does that mean? It means random, but worse. If the server chose random values, that implies it would return different values in different queries, so you have a chance of catching the problem when you test your software. But nondeterministic in this context means the server chooses the same value every time, until it doesn't.

为什么它会改变它选择的值?服务器升级是一个原因.表大小的变化可能是另一个.关键是，服务器可以自由地返回它想要的任何值.

Why might it change the value it chooses? A server upgrade is one reason. A change to table size might be another. The point is, the server is free to return whatever value it wants.

我希望刚学 SQL 的人能设置这个 ONLY_FULL_GROUP_BY 模式；他们会从查询中获得更可预测的结果，服务器会拒绝不确定性查询.

I wish people newly learning SQL would set this ONLY_FULL_GROUP_BY mode; they'd get much more predictable results from their queries, and the server would reject nondeterministic queries.

相关文章