MySQL，ORDER BY插入顺序，无排序列 [英] MySQL, ORDER BY insertion order, no sorting columns

问题描述

如何从表中订购值(从插入值开始递增).没有专门的列，例如时间戳或自动增量.

How can I order values from a table, ascending from the time they were inserted. There is no special column for this matter, like a timestamp or autoincrement.

我知道不建议这样做....我仍然想知道如何做到这一点.

I know this is not recommended to do... Still I would like to know how to do this.

据我从答案中了解到，如果在插入值之前未添加排序列(例如timestamp或autoincremental)，则无法通过插入对它们进行排序.

As I understand from the answers, if no sorting columns e.g: timestamp or autoincremental were added before the values were inserted, there is no way of sorting them by insertion.

推荐答案

如果没有查询中的ORDER BY子句，则不能保证以任何特定的顺序返回行.

There is no guarantee that rows will be returned in any particular order without an ORDER BY clause in the query.

考虑一个简单的查询，该查询返回表中所有行的所有列.例如:

Consider a simple query that returns all columns of all rows in a table. For example:

SELECT * FROM mytable ; 

对于该查询，MySQL可能会从表的开头开始执行全表扫描.因此，很可能将按照在物理存储中找到的顺序返回这些行.

For that query, it is likely that MySQL will perform a full table scan, from the beginning of the table. So it is likely that the rows will be returned in the order they are found in physical storage.

这大概与插入行的顺序相对应，如果没有删除，没有更新也没有重组，则稍后会回收插入行的空间，并重新用于存储新插入的行.

This may roughly correspond to the order that rows were inserted, if there have been no deletes, no updates and no reorganization, where space for an inserted row was later reclaimed, and reused to store a newly inserted row.

但是不能保证这种行为.

But this behavior is NOT guaranteed.

要按插入顺序返回行，查询必须通过包含ORDER BY子句来指定要返回行的顺序.

To return the rows in the order that they were inserted, the query must specify the sequence that rows are to be returned, by including an ORDER BY clause.

对于以插入顺序"返回的行，这意味着查询需要能够使该信息可用，或者能够派生该信息.对于针对单个表的简单查询，这意味着信息需要存储在行中.

For the rows to be returned in "insertion order", that means the query needs to be able to have that information available, or be able to derive that. For a simple query against a single table, that means the information needs to be stored in the row.

这篇关于MySQL，ORDER BY插入顺序，无排序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！