Mysql replace all id's to make a correct sequence

Question

I was wondering if it's possible in mysql to reassign auto incremented ID's so that they will follow eachother up.

Currently I have deleted rows and because of that my ID's are as follow

Is it possible to run a query that will replace all id's with autoincrement again? So that the excisting rows will be

No, don't mess with the auto-incrment. What do you need it for? — juergen d
– juergen d, Commented Feb 4, 2015 at 10:05
This is a frequent question,but the answer is that you dont need to.Gaps are not a probelm in the primary key. — Mihai
– Mihai, Commented Feb 4, 2015 at 10:05
Damn, I searched but couldn't find anything, BTW you guys are quick. I personally don't find it an issue but a client/colleague of mine insists on using the row ID to show in the CMS instead of just adding the numbers based on $i in the iteration of the loop. No idea why but I thought, if he insists we'll just do it =') — Jebble
– Jebble, Commented Feb 4, 2015 at 10:07
and the answer is not possible till u are using auto-increment. — varsha
– varsha, Commented Feb 4, 2015 at 10:07
Yes aesthetically its ugly,but think about it;everytime you delete something,you need to fill the gaps.And what if you have millions or more rows? — Mihai
– Mihai, Commented Feb 4, 2015 at 10:13

Marc Delisle · Accepted Answer · 2015-02-04 15:13:54Z

3

Can be done via phpMyAdmin:

On your id column, remove the auto-increment setting
Delete your primary key in Structure > indexes
Create a new column future_id as primary key, auto_increment
Browse your table and verify that the new increments correspond to what you're expecting
Drop your old id column
Rename the future_id column to id
Move the new id column via Structure > Move columns

answered Feb 4, 2015 at 15:13

Marc Delisle

9,0253 gold badges32 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Can't upvote yet, need 4 more reputation but this helped. thanks!