In my database I have some records where I am sorting by a column that contains identical values:

在我的数据库中，我有一些按包含相同值的列进行排序的记录：

| col1 | timestamp              |
| row1 | 2011-07-01 00:00:00    |
| row2 | 2011-07-01 00:00:00    |
| row3 | 2011-07-01 00:00:00    |

SELECT ... ORDER BY timestamp

It looks like the result is in random order.
Is the random order consistent? I have these data in two mysql servers can I expect the same result？

看起来结果是随机排列的。随机顺序是否一致？我在两台MySQL服务器中都有这些数据，我能期待相同的结果吗？

I'd advise against making that assumption. In standard SQL, anything not required by an explicit ORDER BY clause is implementation dependent.

我建议不要做这样的假设。在标准SQL中，显式ORDER BY子句不需要的任何内容都依赖于实现。

I can't speak for MySQL, but on e.g. SQL Server, the output order for rows that are "equal" so far as the ORDER BY is concerned may vary every time the query is run - and could be influenced by practically anything (e.g. patch/service pack level of the server, workload, which pages are currently in the buffer pool, etc).

我不能为MySQL说话，但在例如SQL Server上，每次运行查询时，就ORDER BY而言“相等”的行的输出顺序可能会有所不同-并且可能会受到几乎任何东西的影响(例如，服务器的补丁/服务包级别、工作负载、当前在缓冲池中的页面等)。

So if you need a specific order, the best thing you can do (both to guarantee it, and to document your query for future maintainers) is explicitly request the ordering you want.

因此，如果您需要特定的订单，您可以做的最好的事情(既可以保证它，也可以为将来的维护人员记录您的查询)就是显式地请求您想要的订单。

Lot's of answers already, but the bottom line answer is NO.

答案已经很多了，但底线答案是否定的。

If you want rows returned in a particular sequence, consistently, then specify that in an ORDER BY. Without that, there absolutely NO GUARANTEE what order rows will be returned in.

如果希望以特定的顺序一致地返回行，则在ORDER BY中指定。否则，绝对不能保证以什么顺序返回行。

I think what you may be missing is that there can be multiple expressions listed in the ORDER BY clause. And you can include expressions that are not in the SELECT list.

我认为您可能遗漏的是，ORDER BY子句中可能列出了多个表达式。您还可以包括不在选择列表中的表达式。

In your case, for example, you could use ORDER BY timestamp, id.

例如，在您的例子中，可以使用ORDER BY TIMESTAMP，id。

(Or some other columns or expressions.)

(或其他一些列或表达式。)

That will order the rows first on timestamp, and then any rows that have the same value for timestamp will be ordered by id, or whatever the next expression in this list is.

这将首先对TIMESTAMP上的行进行排序，然后任何具有相同TIMESTAMP值的行都将按照id或该列表中的下一个表达式进行排序。

The answer is: No, the order won't be consistent. I faced the same issue and solved it by adding another column to the order section. Be sure that this column is unique for each record like 'ID' or whatever it is.

答案是：不，顺序不会一致。我遇到了同样的问题，并通过在Order部分添加另一个专栏来解决它。请确保该列对于每个记录都是唯一的，如“ID”或其他名称。

In this case, you must add the 'ID' field to your table which is unique for each record. You can assign it 'AI' (auto increment) so that you are not going to deal with the maintenance.

在这种情况下，您必须将“ID”字段添加到表中，该字段对于每条记录都是唯一的。你可以给它分配‘AI’(自动递增)，这样你就不需要处理维护了。

After adding the 'ID' column, update the last part of your query like:

添加‘ID’列后，更新查询的最后部分，如下所示：

SELECT mt.*
FROM my_table mt
ORDER BY mt.timestamp ASC, mt.id DESC

In ORDER BY condition if the rows are same values or if you want to arrange the data by selecting ORDER BY statement. CASE : You want to ORDER BY the values of column are frequency of words. And two words in the table may have the same frequency value in the frequency occurrence column.. So in the frequency column you will have two same frequencies of two different words. So, in "select * from database_name ORDER BY frequency" you may find any of one the two words having the same frequency showing up just before its latter. And in second run the other word which was showing after the first word showing up earlier now. It depends on buffer memory,pages being in and out at the moment etc..

如果行的值相同，或者如果要通过选择ORDER BY语句来排列数据，则使用ORDER BY条件。案例：您想要按列的值是词频进行排序。并且表中的两个词在频率出现列中可以具有相同的频率值。因此，在频率列中，您将拥有两个不同单词的相同频率。因此，在“SELECT*FROM DATABASE_NAME ORDER BY FREQUENCY”中，您可能会发现具有相同频率的两个单词中的任何一个出现在其前面。在第二次运行中，在第一个单词之后显示的另一个单词现在出现得更早。这取决于缓冲内存、当前进出的页面等。

According to https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html

根据https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html的说法

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders

If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic.

Beginning with MySQL 8.0.21, it is possible to turn off this optimization by setting the optimizer_switch system variable's prefer_ordering_index flag to off.

That depends on storage engine used. In MyISAM they'll be ordered in natural order (i.e. in order they're stored on the disk - which can be changed using ALTER TABLE ... ORDER BY command). In InnoDB they'll be ordered by PK. Other engines can have their own rules.

这取决于使用的存储引擎。在MyISAM中，它们将按自然顺序排序(即，它们存储在磁盘上的顺序-可以使用ALTER TABLE更改...按命令排序)。在InnoDB中，它们将通过PK订购。其他引擎可以有自己的规则。