Resyncing the auto-increment in MySQL

ddrfreek

I'm using auto-increment to create article IDs when uploading a new article to my site, and I accidentally submitted one form twice, creating a duplicate entry. I deleted the entry in the database, but the auto-increment continued on after that deleted IDs. Is there any way to change this, or do I just have to go on having a hole in the consecutive IDs?

bradgrafelman

You can change the next number that will be used:

ALTER TABLE `myTable` AUTO_INCREMENT = 9

ddrfreek

Alright, I guess that could work. Thanks.

bradgrafelman

Don't forget to mark this thread resolved.

Sxooter

Note that if you're designing an application that expects autoincs to NOT have spaces, you're making a mistake. Generally, autoincs are there to guarantee you don't get dups. they're not there to make sure there aren't any holes in the sequence.

MarkR

Yep - auto increment is not supposed to be used to produce sequential numbers with no gaps.

I imagine (perhaps wrongly) that an auto increment value used in a rolled-back transaction also results in a gap.

Mark

Sxooter

MarkR wrote:
Yep - auto increment is not supposed to be used to produce sequential numbers with no gaps.

I imagine (perhaps wrongly) that an auto increment value used in a rolled-back transaction also results in a gap.

Mark

Nope, you're absolutely right.