MySQL SQL：查询的默认顺序是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8746519/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: What is the default Order By of queries?
提问by Vijayan
What is the default order of a query when no ORDER BYis used?
ORDER BY使用no 时查询的默认顺序是什么？
回答by Rahul
There is no such order present. Taken from http://forums.mysql.com/read.php?21,239471,239688#msg-239688
不存在这样的命令。取自http://forums.mysql.com/read.php?21,239471,239688#msg-239688
Do not depend on order when ORDER BY is missing.
Always specify ORDER BY if you want a particular order -- in some situations the engine can eliminate the ORDER BY because of how it does some other step.
- GROUP BY forces ORDER BY. (This is a violation of the standard. It can be avoided by using ORDER BY NULL.)
SELECT * FROM tbl-- this will do a "table scan". If the table has never had any DELETEs/REPLACEs/UPDATEs, the records will happen to be in the insertion order, hence what you observed.If you had done the same statement with an InnoDB table, they would have been delivered in PRIMARY KEY order, not INSERT order. Again, this is an artifact of the underlying implementation, not something to depend on.
当 ORDER BY 丢失时，不要依赖于顺序。
如果你想要一个特定的顺序，总是指定 ORDER BY —— 在某些情况下，引擎可以消除 ORDER BY，因为它如何执行其他一些步骤。
- GROUP BY 强制 ORDER BY。（这违反了标准。可以通过使用 ORDER BY NULL 来避免。）
SELECT * FROM tbl-- 这将进行“表扫描”。如果该表从未有任何 DELETE/REPLACE/UPDATE，则记录将碰巧按插入顺序排列，这就是您所观察到的。如果您对 InnoDB 表执行了相同的语句，则它们将以 PRIMARY KEY 顺序而不是 INSERT 顺序传递。同样，这是底层实现的工件，而不是依赖的东西。
回答by alf
There's none. Depending on what you query and how your query was optimised, you can get any order. There's even no guarantee that two queries which look the same will return results in the same order: if you don't specify it, you cannot rely on it.
没有。根据您查询的内容以及查询的优化方式，您可以获得任何订单。甚至不能保证两个看起来相同的查询会以相同的顺序返回结果：如果你不指定它，你就不能依赖它。
回答by scipilot
I've found SQL Server to be almost random in its default order (depending on age and complexity of the data), which is good as it forces you to specify all ordering.
我发现 SQL Server 的默认顺序几乎是随机的（取决于数据的年龄和复杂性），这很好，因为它强制您指定所有顺序。
(I vaguely remember Oracle being similar to SQL Server in this respect.)
（我依稀记得 Oracle 在这方面类似于 SQL Server。）
MySQL by default seems to order by the record structure on disk, (which can include out-of-sequence entries due to deletions and optimisations) but it often initially fools developers into not bother using order-by clauses because the data appears to default to primary-key ordering, which is notthe case!
默认情况下，MySQL 似乎按磁盘上的记录结构排序（可能包括由于删除和优化而导致的乱序条目），但它最初常常愚弄开发人员不去使用 order-by 子句，因为数据似乎默认为主键排序，事实并非如此！
I was surprised to discovere today, that MySQL 5.6 and 4.1 implicitly sub-order records which have been sorted on a column with a limited resolution in the opposite direction. Some of my results have identical sort-values and the overall order is unpredictable. e.g. in my case it was a sorted DESC by a datetime column and some of the entries were in the same second so they couldn't be explicitly ordered. On MySQL 5.6 they select in one order (the order of insertion), but in 4.1 they select backwards! This led to a very annoying deployment bug.
我今天惊讶地发现，MySQL 5.6 和 4.1 隐式地对已按相反方向有限分辨率的列排序的记录进行排序。我的一些结果具有相同的排序值，并且整体顺序是不可预测的。例如，在我的情况下，它是按日期时间列排序的 DESC，并且某些条目在同一秒内，因此无法明确排序。在 MySQL 5.6 中，它们按一种顺序（插入顺序）进行选择，但在 4.1 中它们向后选择！这导致了一个非常烦人的部署错误。
I have't found documentation on this change, but found notes on on implicit grouporder in MySQL:
我没有找到有关此更改的文档，但找到了有关MySQL 中隐式组顺序的说明：
By default, MySQL sorts all GROUP BY col1, col2, ... queries as if you specified ORDER BY col1, col2, ... in the query as well.
默认情况下，MySQL 对所有 GROUP BY col1, col2, ... 查询进行排序，就像您在查询中指定 ORDER BY col1, col2, ... 一样。
However:
然而：
Relying on implicit GROUP BY sorting in MySQL 5.5 is deprecated. To achieve a specific sort order of grouped results, it is preferable to use an explicit ORDER BY clause.
在 MySQL 5.5 中依赖隐式 GROUP BY 排序已被弃用。要实现分组结果的特定排序顺序，最好使用显式 ORDER BY 子句。
So in agreement with the other answers - never rely on default or implicit ordering in any database.
因此，与其他答案一致-永远不要依赖任何数据库中的默认或隐式排序。
回答by scipilot
The default ordering will depend on indexes used in the query and in what order they are used. It can change as the data/statistics change and the optimizer chooses different plans.
默认排序将取决于查询中使用的索引以及它们的使用顺序。它可以随着数据/统计数据的变化以及优化器选择不同的计划而变化。
If you want the data in a specific order, use ORDER BY
如果您想要特定顺序的数据，请使用 ORDER BY
