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?
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