为什么Mysql的Group By和Oracle的Group by行为不同 [英] Why Mysql's Group By and Oracle's Group by behaviours are different

问题描述

为什么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.

这篇关于为什么Mysql的Group By和Oracle的Group by行为不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！