How to reset AUTO_INCREMENT in MySQL

[akapsycho]

I've got an index field which stores id numbers which are created by auto_incrementing and assigning that value as the id. ive already emptied out the values a few times since I'm testing, but I can't seem to figure out how to reset a field set to auto_increment, so that the next time someone registers an account, their ID will be set to 1 and it will rise from then forward.

[Atli]

Hi.

If you are clearing out the entire table, using TRUNCATE will reset AUTO_INCREMENT fields. It drops the table and re-creates it so everything is reset.

However, if you just want to reset the field you could try this:

alter table myTbl auto_increment = 1;
 

PS. I edited the thread title to make it a bit clearer.

[akapsycho]

Alright cool, I think I'll go for truncate. If I truncate it, it will recreate all of the fields for me right? It would be a pain to have to do that for 6 tables each time I clear the database. Thanks!

[Atli]

Yes, as far as the user is concerned, using the TRUNCATE command to clear a table will look pretty much the same as using the DELETE command.

In reality the TRUNCATE command drops the table and re-creates it like it was before, but without the data. All columns will be re-created like they were.
The DELETE command, however, leaves the table as it is and just deletes all the data, which will not reset stuff like auto_increment.

Note, that both methods will completely remove all data from the table!

[akapsycho]

Cool, thanks a lot for this!

[pbmods]

Heya, Psycho.

As a sidenote, TRUNCATE is faster than DELETE *. So if you know you'll be deleting all your data, it is definitely worth it to use TRUNCATE.