MySQL为什么要添加与SQL标准冲突的功能? [英] Why does MySQL add a feature that conflicts with SQL standards?
问题描述
我已经习惯了Microsoft技术，包括SQL Server.今天，我遇到了 a Q& A ，其中以下段落引用了MySQL文档中的内容:
I am used to Microsoft technologies including SQL Server. Today I ran across a Q&A where the following passage from the MySQL documentation was quoted:
标准SQL 将拒绝您的查询，因为您无法选择 非聚合字段，它们不属于 汇总查询. MySQL扩展了GROUP BY的使用，以便选择 列表可以引用未在GROUP BY中命名的非聚合列 条款.这意味着前面的查询在MySQL中是合法的.你 可以使用此功能避免不必要的操作以获得更好的性能 列排序和分组.但是，这主要在以下情况下有用 未在GROUP BY中命名的每个非聚合列中的所有值都是 每个小组都一样.服务器可以自由选择任何值 每个组，所以除非它们相同，否则选择的值是 不确定.
Standard SQL would reject your query because you can not SELECT non-aggregate fields that are not part of the GROUP BY clause in an aggregate query. MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.
如果该MySQL扩展与SQL标准相冲突，是什么原因呢?
What is the reason for this MySQL extension, if it conflicts with the SQL Standard?
推荐答案
标准SQL会拒绝您的查询，因为您无法选择聚合查询中不属于GROUP BY子句的非聚合字段
Standard SQL would reject your query because you can not SELECT non-aggregate fields that are not part of the GROUP BY clause in an aggregate query
这是正确的，直到1992年..
This is correct, up to 1992.
但这是显然是错误的，从2003年开始.
根据SQL-2003标准， 6IWD6-02-Foundation-2011-01.pdf，摘自http://www.wiscorp.com/，第7.12段(查询规范)，第398页:
From SQL-2003 standard, 6IWD6-02-Foundation-2011-01.pdf, from http://www.wiscorp.com/, paragraph-7.12 (query specification), page 398:
17)如果T是分组表，则令G为T的分组列的集合.在每个((值表达式))中 在[[select list))中，每个引用T列的列引用都应引用某个C列，该列C 功能上依赖取决于G ，或者必须包含在((设置功能规范))的聚合参数中 聚合查询为QS
17) If T is a grouped table, then let G be the set of grouping columns of T. In each ((value expression)) contained in ((select list)) , each column reference that references a column of T shall reference some column C that is functionally dependent on G or shall be contained in an aggregated argument of a ((set function specification)) whose aggregation query is QS
现在，MYSQL已通过允许不仅功能上依赖的分组列上的列，而且允许所有列的列实现了此功能.列.这给用户带来了一些问题，这些用户不了解分组的工作原理，并在他们意想不到的地方获得不确定的结果.
Now MYSQL, has implemented this feature by allowing not only columns that are functionally dependent on the grouping columns but allowing all columns. This is causing some problems with users that do not understand how grouping works and get indeterminate results where they don't expect.
但是您可以正确地说MySQL添加了与SQL标准冲突的功能(尽管您似乎出于错误的原因而认为).由于他们添加了SQL标准功能，但并不是以最佳方式(更像是简单的方式)，这并不完全准确，但确实与最新标准相抵触.
But you are right to say that MySQL has added a feature that conflicts with SQL-standards (although you seem to think that for the wrong reason). It's not entirely accurate as they have added a SQL-standard feature but not in the best way (more like the easy way) but it does conflict with the latest standards.
为回答您的问题，此MySQL功能(扩展名)的原因是我认为符合最新的SQL标准(2003+).为什么他们选择以这种方式实现(不完全兼容)，我们只能推测.
To answer your question, the reason for this MySQL feature (extension) is I suppose to be accordance with latest SQL-standards (2003+). Why they chose to implement it this way (not fully compliant), we can only speculate.
正如@Quassnoi和@Johan用示例回答的那样，这主要是性能和可维护性问题.但是无法轻易地将RDBMS更改为足够聪明(不包括Skynet)以识别功能相关列，因此MySQL开发人员做出了选择:
As @Quassnoi and @Johan answered with examples, it's mainly a performance and maintainability issue. But one can't easily change the RDBMS to be clever enough (Skynet excluded) to recognize functionally dependent columns, so MySQL developers made a choice:
我们(MySQL)为您(MySQL用户)提供了SQL-2003标准中的此功能.它可以提高某些
GROUP BY查询的速度，但有一个问题.您必须小心(而不是SQL引擎)，以使SELECT和HAVING列表中的列在功能上取决于GROUP BY列.如果没有，您可能会得到不确定的结果.
We (MySQL) give you (MySQL users) this feature which is in SQL-2003 standards. It improves speed in certain
GROUP BYqueries but there's a catch. You have to be careful (and not the SQL engine) so columns in theSELECTandHAVINGlists are functionally dependent on theGROUP BYcolumns. If not, you may get indeterminate results.
If you want to disable it, you can set sql_mode to ONLY_FULL_GROUP_BY.
全部包含在 MySQL文档:扩展至GROUP BY(5.5)-尽管不在上面的措词中，但与您引用的内容相同(他们甚至忘记提及它与标准SQL-2003的差异，而不是与标准SQL-92的差异).我认为这种选择在所有软件(包括其他RDBMS)中都是常见的.它们是出于性能，向后兼容性和许多其他原因而制造的.例如，Oracle有著名的'' is the same as NULL，而SQL-Server也可能有一些.
It's all in the MySQL docs: Extensions to GROUP BY (5.5) - although not in the above wording but as in your quote (they even forgot to mention that it's a deviation from standard SQL-2003 while not standard SQL-92). This kind of choices is common I think in all software, other RDBMS included. They are made for performance, backward compatibility and a lot of other reasons. Oracle has the famous '' is the same as NULL for example and SQL-Server has probably some, too.
还有Peter Bouman撰写的此博客文章，其中捍卫了MySQL开发人员的选择:揭穿GROUP BY神话.
There is also this blog post by Peter Bouman, where MySQL developers' choice is defended: Debunking GROUP BY myths.
更新(2011)
@Mark Byers 在评论中(在DBA的一个相关问题中)通知了我们. SE)， PostgreSQL 9.1添加了新功能 (发布日期:2011年9月)就是为此目的而设计的.它比MySQL的实施更具限制性，并且更接近于标准.
As @Mark Byers informed us in a comment (in a related question at DBA.SE), PostgreSQL 9.1 added a new feature (release date: September 2011) designed for this purpose. It is more restrictive than MySQL's implementation and closer to the standard.
更新2(2015年)
MySQL宣布在5.7版本中，该行为已得到改进，以符合标准并实际上可以识别功能依赖性(甚至比Postgres实现更好).文档: GROUP BY的MySQL处理(5.7) 和彼得·布曼(Peter Bouman)的另一篇博客文章:
MySQL announced that in 5.7 version, the behaviour is improved to conform with the standard and actually recognize functional dependencies, (even better than the Postgres implementation). The documentation: MySQL Handling of GROUP BY (5.7) and another blog post by Peter Bouman: MySQL 5.7.5: GROUP BY respects functional dependencies!
这篇关于MySQL为什么要添加与SQL标准冲突的功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
