Fixing gaps in mysql table row id after we delete some of them [duplicate]

Question

I have a mysql table with more than 17000 rows in it. And I have deleted about 530 rows from some mid part of it. Now each row had a sequential AUTO-INCREAMENTED number primary key. As you can understand now several numbers for rows have been deleted. So i just wanted to ask that is there any way to fix all rows again in some flawless order?

Answer 1

You can but be carefull of other tables using this primary key as a foreign key

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

this will update the id column of the table table ... you then need to reset the auto_increment :

ALTER TABLE table AUTO_INCREMENT = 1;

This resets the next id to be MAX(id)+1 from the docs :

To change the value of the AUTO_INCREMENT counter to be used for new rows, do this:

ALTER TABLE t2 AUTO_INCREMENT = value;  

You cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one