How to reorder MySQL auto increment primary key
I recently had an issue where some data in my mysql database are no more required and I would like to delete them but I will not want deleting these records interfere with the ordering of the primary key. I know a lot of you would agree it is not a big deal but my with my OCD, I can't allow it slide. Here is the solution for the people who are in the same frame of mind like me.
SET @count = 0;
UPDATE `table_name` SET `table_name`.`id` = @count:= @count + 1;
ALTER TABLE table_name AUTO_INCREMENT = 1;
