sql - MySQL table reorder

Question

I have a table like

id - some data
1 - ...
2 - ...
4 - ...
5 - ...
8 - ...
12 - ...

The table is really long, with thousands IDs. Look at IDs, there are "empty" numbers between of them. I want to reorder all IDs to have no empty ones.

Table should look like
1 - ...
2 - ...
3 - ...
4 - ...
etc.

How would the query look like?

Answer 1

I don't advocate re-numbering ids. The id column should be the primary key for the table, and gaps don't make a difference. If it is used for foreign key references, then you will be messing up your database.

But, you can do it as:

set @rn = 0;

update `table` t
    set t.id = (@rn := @rn + 1)
    order by t.id;