Fix spaces in id of mysql table row after deleting some of them

Question

I have a mysql table with over 17000 rows. And I deleted about 530 lines from some middle part. Now each row had a sequential primary key with the number AUTO-INCREAMENTED. As you can now understand, several line numbers have been deleted. So I just wanted to ask if there is a way to fix all the lines in some perfect order?

Answer 1

You can , but be careful 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 ... then you need to reset auto_increment:

 ALTER TABLE table AUTO_INCREMENT = 1; 

Resets the following identifier MAX(id)+1 from docs :

To change the value of the AUTO_INCREMENT counter that will be used for new lines, do the following:

 ALTER TABLE t2 AUTO_INCREMENT = value; 

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