MySQL database re-orders entries automatically

I have created a MySQL database using MySQL Workbench. In there, I have created a table, the first entries of which are:

id     firstName     lastName
1      John          Smith
2      Emma          Richards

Every column contains VARCHAR characters – even the id, it is NOT an integer. For some reason, however, MySQL seems to re-order the entries:

id     firstName     lastName
1      John          Smith
10     Karen         Hill

I don’t want MySQL to re-order my entries. When I retrieve the entries from the database, I want them to appear exactly in the order that I have inserted them into the database. Previously to creating the MySQL database, I have used a SQLite database, which does not re-order the entries.

What might be the reason for this and how can I change it?

Answer

I don’t want MySQL to re-order my entries. When I retrieve the entries from the database, I want them to appear exactly in the order that I have inserted them into the database.

SQL tables represent unordered (multi)sets. Period. When you query a table in any database, the ordering is not guaranteed unless you include an order by. This is even true in SQLite, as this DB fiddle demonstrates.

In most databases, if you want to capture the insertion order, then you need some sort of column that captures the ordering. There are two common methods:

• An identity or auto_increment column that captures the insertion order.
• A datetime/timestamp column that captures the date time. This does not always work, because there can be ties.

Then, when you query the table, you need to use order by on the column.

If your first column represents the ordering but happens to be stored as a string, then you can simply use:

order by (id + 0)