MySQL ORDER BY insertion order, without sorting columns

Question

How can I arrange the values ​​from a table, increasing since they were set. There is no special column for this, for example, a time stamp or auto-increment.

I know that this is not recommended ... However, I would like to know how to do it.

As I understand from the answers, if no sorting column was added before entering the values, for example: timestamp or autoincremental, there is no way to sort them by inserting.

Answer 1

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

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

 SELECT * FROM mytable ; 

For this query, it is likely that MySQL will perform a full table scan from the beginning of the table. Thus, it is likely that the rows will be returned in the order they were in the physical store.

This may roughly correspond to the order in which the rows were inserted if there were no deletions, no updates, no reorganization, where the space for the inserted row was subsequently freed and reused to store the newly inserted row.

But this behavior is not guaranteed.

To return rows in the order in which they were inserted, the query must specify the sequence in which the rows should be returned, including the ORDER BY .

For rows that should be returned in the "insertion order", this means that the request should be able to have this information available or be able to receive it. For a simple query on a single table, this means that the information should be stored in a row.

Answer 2

You can ORDER BY extract something from your table. If you don’t have anything you can use to figure out the order you need, you cannot order it.

Answer 3

Depending on the data in the table, you can order by the data identifier - if the data has one incremental integer to guarantee the uniqueness of the PC. There is no other way to sort by placement order if the data is not written and not written in the table.

I do not know anything in MySQL, which stores additional (meta) information about records that you did not specify at the table level.

Answer 4

There must be a column to order a request. This will usually be an insert timestamp or an id / incrementing key increase. There is no way to guarantee the order otherwise, because it is not.

corresponding thread from MySQL forum