Search code examples
mysqlphpmyadminauto-incrementincrement

Mysql replace all id's to make a correct sequence

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

  • 1
  • 2
  • 3
  • 8
  • 9
  • 14

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
Solution

  • 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