Not exactly a thrilling blog post, but I always forget the exact syntax for this for some reason, so I am putting it in my blog so I can easily find it.  Sometimes I find myself wanting to set the AUTO_INCREMENT start number back to one.  This is usually when I populate a new table, and end up deleting all the data in the table and then decide to repopulate it.  For example if I inserted 4000 records in a table and then deleted them, AUTO_INCREMENT would be set to 4001.  When I re-insert these 4000 records I would prefer it to start at id=1 instead of id=4001 to keep things clean.  Its as easy as running a simple ALTER statement:

#both of these will start you at id=1 when you insert your next record
ALTER TABLE my_table AUTO_INCREMENT=0
ALTER TABLE my_table AUTO_INCREMENT=1

Just to see what happened, I inserted a few test records into the database and then set the AUTO_INCREMENT back to “1″. I was expecting an error when I inserted the next record since the id=1 already existed and AUTO_INCRMENT id field has to be unique. But MYSQL can detect this and it automatically auto increments to the next available id. So if I have 3 records with ids= [1,2,3] then I set AUTO_INCREMENT to “1″, the next record I insert will be ID=4. Same thing if you have ids = [2,3,4]. MySQL skips “1″ and the next record id will be “5″. According to some of the comments in the MySQL documentation, it uses the following formula when setting the ID to “0″ or “1″:

1 + max(AUTO_INCREMENT)