Skip to content

How to Reassign Sequential Numbers on the Auto Incremental Column

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. Required fields are marked *