Skip to content

How to Reassign Sequential Numbers on the Auto Incremental Column

  • by
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.
Tags:

Leave a Reply

Your email address will not be published.