Hey all,
I'm nearing the completion of a script of mine that inserts data into a table and assigns each row an auto_increment id. Right now it's populated with a bunch of test entries and I want to know how I can reset the auto_increment id to 0 so that when the script goes into a production environment it starts over at 1 when the first real record is entered.

TIA

    It seems to me that what you really want to do is create the tables from scratch just before going live, thus the auto increment problem does not even exist.

    If you really do need to change the autoincrement offset, you can try:

    ALTER TABLE tablename AUTO_INCREMENT = 1;

      Thanks for the input. I'll give that a try. It's going to be a couple days before I go live so I'll resolve this thread once I try it.

        But why not just drop the tables and then create them again? That should reset the auto increment offset too.

          I could do that too I suppose. They production site has a hosting plan with phpMyAdmin and I planned originally on just doing an export and then an import to create the table on the production system without having to recreate each little table.

            Just for another thought to consider: for most uses of such fields it really does not matter what number the field starts at; all that matters is that the values are unique. It normally only really matters if you are at risk of expending all available values (based on the data type of the field in question). Therefore, unless you had a lot of test entries, it might not be worth bothering with other than giving you that nice, clean, fresh start feeling. 🙂

              Oh I know. I'm just ocd about things like that. I like that fresh clean feeling. it's the type of thing that nags at me. Much like when people don't use the code tags or don't indent properly in their source code and everything is jumbled. 🙂

                If you're talking about resetting a table that's filled with test data, try:

                TRUNCATE TABLE tableName;

                That will erase all entries from the table and reset the AUTO_INCREMENT column.

                Anyway, don't forget to mark this thread resolved (if it is).

                  Thanks. Laserlight's option worked good on my test environment so I'll just do it again when the time comes, export the .sql file, and reupload the .sql file onto the live box. Thanks!

                    Write a Reply...