CODEWITHSUNDEEP
mysqlauto-incrementmyisam
HaLo2FrEeEk
HaLo2FrEeEk

Reputation: 596

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

Upvotes: 0

Views: 1645

Answers (3)

No'am Newman
No'am Newman

Reputation: 6477

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.

Upvotes: 0

Mat
Mat

Reputation: 206737

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.

Upvotes: 1

dfsq
dfsq

Reputation: 193271

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.

Upvotes: 1

Related Questions