I've got an index field which stores id numbers which are created by auto_incrementi ng 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.
-
-
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:
[code=mysql]
alter table myTbl auto_increment = 1;
[/code]
PS. I edited the thread title to make it a bit clearer. -
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!Comment
-
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!Comment
-
Cool, thanks a lot for this!Comment
-
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.Comment
Comment