Mysql重置auto_increment不适用于新的ID，但适用于所有（也是旧的ID）

Question

Hi all is there any SQL function to reset all auto_increment ids?

I mean , i have this situation (id =1 , id2 = 2, id3 =3)

once i delete id i would like to be returned this situation (id2 = 1, id3 = 2) and so on.

Need i a script to do that?

Answer 1

You can reset the AUTO_INCREMENT for the whole table:

ALTER TABLE table AUTO_INCREMENT=1;

But you cannot really go back to fill in values in individual rows that have been deleted. The auto_increment values are intended to be unique, but aren't guaranteed to remain sequential if deletions happen or new rows are inserted with an explicit value set for the auto_increment column.

If you needed to modify them to be sequential again, it would have to be done in code, probably looping over all rows and performing UPDATE statements individually.