Mysql insertion order

Question

I don't know whether it is already answered. I hadn't got any answers.In Mysql tables, the rows will be arranged in the order of primary key. For example

+----+--------+
| id | name   |
+----+--------+
|  1 | john   |
|  2 | Bryan  |
|  3 | Princy |
|  5 | Danny  |
+----+--------+

If I insert anothe row insert into demo_table values(4,"Michael").The table will be like

+----+---------+
| id | name    |
+----+---------+
|  1 | john    |
|  2 | Bryan   |
|  3 | Princy  |
|  4 | Michael |
|  5 | Danny   |
+----+---------+

But I need the table to be like

+----+---------+
| id | name    |
+----+---------+
|  1 | john    |
|  2 | Bryan   |
|  3 | Prince  |
|  5 | Danny   |
|  4 | Michael |
+----+---------+

I want the row to be concatenated to the table i.e., The rows of the table should be in the order of insertion.Can anybody suggest me the query to get it.Thank you for any answer in advance.

It's not your job to worry about the insert order, you let the database handle that because it'll do it in such a way that it's easy to retrieve data whenever. Your job is to query the data and tell the database *how* to order rows it returns. Never mess with primary key, it's one of the worst things you can do. Create a column and do your sorting using that column. — N.B., Jul 23 '18 at 13:55
Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. — philipxy, Jul 24 '18 at 00:40

score 4 · Answer 1 · answered Jul 23 '18 at 10:40

4

There is in general no internal order to the records in a MySQL table. The only order which exists is the one you impose at the time you query. You typically impose that order using an ORDER BY clause. But there is a bigger design problem here. If you want to order the records by the time when they were inserted, then you should either add a dedicated column to your table which contains a timestamp, or perhaps make the id column auto increment.

If you want to go with the latter option, here is how you would do that:

ALTER TABLE demo_table MODIFY COLUMN id INT auto_increment;

Then, do your insertions like this:

INSERT INTO demo_table (name) VALUES ('Michael');

The database will choose an id value for the Michael record, and in general it would be greater than any already existing id value. If you need absolute control, then adding a timestamp column might make more sense.

answered Jul 23 '18 at 10:40

Tim Biegeleisen

502,043
27
286
360

Thank you Tim. But I want the original table to be like that. – Jagadesh jakes Jul 23 '18 at 10:43
2

...and I've given you a couple of options for making that possible...what is your question? – Tim Biegeleisen Jul 23 '18 at 10:44
@Jagadeshjakes & TimBiegeleisen This answer itself points out that auto increment does not guarantee insert order. So why does it also suggest using it as if it did? – philipxy Jul 25 '18 at 22:53
@Jagadeshjakes "I want the original table to be like" what? Please use enough words to clearly say what you mean. Also this answer just explained that what you want it to be like in your question is not possible, so what is the point of your comment? – philipxy Jul 25 '18 at 22:54

Sumithran · Answer 2 · 2018-07-23T13:50:28.837

0

Just add another Column Created (Timestamp) in your table to store the time of insertion

Then use this Command for insertion

insert into demo_table id, name,created values(4,"Michael",NOW())

The NOW() function returns the current date and time.

Since you are recording the timestamp, it can be also used for future reference too

edited Jul 23 '18 at 13:50

answered Jul 23 '18 at 10:55

Sumithran

6,217
4
40
54

Thank you Sumithran. It worked. Is there any chance to get without adding the column? – Jagadesh jakes Jul 23 '18 at 12:32

score 0 · Answer 3 · answered Jul 23 '18 at 14:09

It's not clear why you want to control the "order" in which the data is stored in your table. The relational model does not support this; unless you specify an order by clause, the order in which records are returned is not deterministic.. Even if it looks like data is stored in a particular sequence, the underlying database engine can change its mind at any point in time without breaking the standards or documented behaviours.

The fact you observe a particular order when executing a select query without order by is a side effect. Side effects are usually harmless, right up to the point where the mean feature changes and the side effect's behaviour changes too.

What's more - it's generally a bad idea to rely on the primary key to have "meaning". I assume your id column represents a primary key; you should really not rely on any business meaning in primary keys - this is why most people use surrogate keys. Depending on the keys indicating in which order a record was created is probably harmless, but it still seems like a side effect to me. In this, I don't support @TimBiegeleisen's otherwise excellent answer.

If you care about the order in which records were entered, make this explicit in the schema by adding a timestamp column, and write your select statement to order by that timestamp. This is the least sensitive to bugs or changes in the underlying logic/database engine.

Mysql insertion order

3 Answers3