Reorder / reset auto increment primary key

Question

Even though this question seems to be quite old, will post an answer for someone who reaches in here searching.

SET @count = 0;
UPDATE `users` SET `users`.`id` = @count:= @count + 1;

If the column is used as a foreign key in other tables, make sure you use ON UPDATE CASCADE instead of the default ON UPDATE NO ACTION for the foreign key relationship in those tables.

Further, in order to reset the AUTO_INCREMENT count, you can immediately issue the following statement.

ALTER TABLE `users` AUTO_INCREMENT = 1;

For MySQLs it will reset the value to MAX(id) + 1.

You could drop the primary key column and re-create it. All the ids should then be reassigned in order.

However this is probably a bad idea in most situations. If you have other tables that have foreign keys to this table then it will definitely not work.

To reset the IDs of my User table, I use the following SQL query. It's been said above that this will ruin any relationships you may have with any other tables.

ALTER TABLE `users` DROP `id`;
ALTER TABLE `users` AUTO_INCREMENT = 1;
ALTER TABLE `users` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

You may simply use this query

alter table abc auto_increment = 1;

SET  @num := 0;

UPDATE your_table SET id = @num := (@num+1);

ALTER TABLE your_table AUTO_INCREMENT =1;

I think this will do it

Or, from PhpMyAdmin, remove "AutoIncrement" flag, save, set it again and save.this resets it.