472,097 Members | 1,090 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,097 software developers and data experts.

How to reset AUTO_INCREMENT in MySQL

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.
Aug 22 '07 #1
5 3041
5,058 Expert 4TB

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:
Expand|Select|Wrap|Line Numbers
  1. alter table myTbl auto_increment = 1;
PS. I edited the thread title to make it a bit clearer.
Aug 22 '07 #2
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!
Aug 22 '07 #3
5,058 Expert 4TB
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!
Aug 22 '07 #4
Cool, thanks a lot for this!
Aug 23 '07 #5
5,821 Expert 4TB
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.
Aug 30 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Eric Kincl | last post: by
5 posts views Thread by Paul Lamonby | last post: by
reply views Thread by Write a Friend | last post: by
2 posts views Thread by Nico v. Rossum | last post: by
9 posts views Thread by Bart Van der Donck | last post: by
reply views Thread by Shailesh | last post: by
1 post views Thread by Phil Latio | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.