mysql删除唯一主键_如何在mysql中重用已删除的主键？

i've got an column named 'id' in a mysql table which also is a primary key that auto-increments.

when i delete rows their id's will also be deleted thus creating "holes" in my id sequence, eg.

1, 2, 3, 9, 10, 30 and so on

is there a way of reusing these deleted id:s?

解决方案

Using:

ALTER TABLE [your table name here] AUTO_INCREMENT = 1

... will reset the auto_increment value to be the next based on the highest existing value existing in the table. That means it can't be used to correct gaps of more than one.

The only reason to do this would be for cosmetic ones - the database doesn't care if records are sequential, only that they relate to one another consistently. There's no need to "correct" the values for the database's sake.

If you are displaying the id values to the user, which is why you'd like them to always be sequential, then I'd recommend adding a surrogate key. Use the surrogate key for displaying to the user, so the values can be re-sequenced as needed but referencial integrity is otherwise unaffected. The surrogate key in this case would be an integer column.