1/08/2010

How to Change / Reset MySQL Auto Incement

I goofed up my SQL database one day, long, long ago, and it's been bugging me ever since.

I have a database that has different sections of a website.
Ex.

Key   Name
1     Cars
2     Houses
3     Pets
666   Junk

Well, setting that 666 for the (Auto_Incement) key for the Junk column was a nice idea at the time, but when I added new sections, they'd jump forward to 667, 668, 669, etc. Even after I deleted the whole record for 666 Junk. I would have to go in and manually set the new records to a lower number.

So, here's how to fix such a thing:

First, delete the erroneous entry from your table.
DELETE FROM exampleTableName WHERE Key = 666 LIMIT 1

Then enter this ...
ALTER TABLE exampleTableName AUTO_INCREMENT = 1
This does not reset the Auto_Increment to 1. Instead (if I had deleted Key 666), the next auto increment would be set to 4.

Exactly what I needed. Hope it helps you.

No comments :

Post a Comment