Re-synchronize MySQL auto-increment field

I have a table which has an auto-increment column and I made a mistake last night and accidentally inserted a few bad rows, so I deleted them.

Unfortunately this means that I'm "missing" a few ids in the auto-increment field (I inserted two good rows and three bad rows, ids 315 and 316 were good, but I deleted the rows with ids 317, 318, and 319. When counting restarted it mad开发者_开发技巧e the id 320, so it skipped the three rows that I deleted).

I know this is the desired functionality of an AI field, but is there a way I can reset it so those ids don't get skipped? I read about dropping the id field and recreating it (and I understand that this will destroy any relationships with other tables, I can fix that manually).

Is there any guarantee that the new ids assigned to the rows will be the same as before. For example, will rows 1, 2, 3, 4, ... still be rows 1, 2, 3, 4, ... after I do this?

Is there another way to do it?

According to the documentation, you should be able to use:

ALTER TABLE your_table AUTO_INCREMENT = <your desired value>;

But this will only work if you have no IDs above that value yet. In your case, since you state that you already have IDs generated at 320 (and possibly above), you'll have to delete those and re-insert them afterwards.

In your situation next query will do the work:

ALTER TABLE `table` AUTO_INCREMENT = 1

After this next auto_increment value will be the highest available (not 2). If you had 10, 11, 12, 13 ids, and then removed 11, 12 and 13, then after above query your next auto_increment value will be 11 again.

It shouldn't really matter that there are gaps in your autoincrement field, as long as no other table uses those values as a foreign key. Such a field should never be displayed to the user, anyway.