Skip to content

How to Reorder Auto Increment Column

  • MySQL

You might want to save fragmentary values on the auto incremental column back to an ordered sequence by reassigning sequential values from 1. I tried self-import the table by mysqldump, but it cannot be helpful.

This is how I did: I dropped the original column, and added a new column for auto increment first and set auto_increment to 1 as well.

mysql> alter table employees drop column sid, add column sid int(11) unsigned not null auto_increment first, drop primary key, add primary key (sid), auto_increment=1;
Query OK, 0 rows affected (14.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

Please note that if you have constraints on this column, you should drop it before executing above statement.

Leave a Reply

Your email address will not be published. Required fields are marked *