How to: Reset the Auto Increment value for a MySQL table

How to: Reset the Auto Increment value for a MySQL table

Although this is something most people won’t need to do, there are some scenarios where you want to reset the ID value you have in a column. In my case for example, I am working with WordPress and the blog ID is set based on the ID in the column of the “wp_blogs” table. So for example in a multisite deployment if you delete a blog for whatever reason, that blog ID is never used again. So at the end you might be left with blogs 1, 4, 8, 13,14,15, etc. leaving spaces for those deleted ones. This is not an issue by any means. Now, if you wanted to use blog ID 2 or 3 in the example above that would not be possible with Auto Increment. Take the above example and assume you delete sites 14 and 15. Normally when you create a new site in that scenario the next site ID would be 16 even though 14 and 15 are available. What resetting Auto Increment does is allow you to start one value above the highest value you have (or if for some reason now you wanted to start with 50 you could).

Most people seek out this functionality specially after they’ve cleaned up an entire table. If you delete all records is sort of nice to start with an ID of 1 than say 35862. Again, nothing wrong with using 35862 as a starting point as it is a valid unique ID which is what we need as developers/applications but starting with a 1 at least gives you the right sense of dimension even though later on those IDs might as well be random GUIDs.

Although most people would use this function after deleting all records from a table, there are two ways to accomplish resetting the next value used by an ID field:

1. Truncate the table (This would result in data loss on that table): “The table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.”:
   • Use:
     • Truncate table myTableName
2. Modify the AUTO_INCREMENT value. This results in no data loss on the table and the next value used would be the one assigned to this value. Keep in mind that if the value provided is smaller than the larger ID already assigned then the system will assign it the value of the largest ID+1. Using the example mentioned above if I set AUTO_INCREMENT to 2, MYSQL will see that I already have a value of 13 and assign AUTO_INCREMENT to 14 for me.
   • Use the following command to reasign AUTO_INCREMENT:
     • ALTER TABLE myTableName AUTO_INCREMENT = 1
3. Drop and Recreate the table: If you can Truncate I don’t see why you would this route (or Modify if you don’t want to lose your data). This also results in data loss on the table. Use the following SQL command:

• DROP TABLE myTableName;
• CREATE TABLE myTableName { ... }};

 

---

Now, say you wanted to rearrange a table so that all IDs are consecutive. This involves much work so I will just go over the general concept and you can create your own script. Keep in mind if you have relationships that are based on this Unique ID they would be broken unless you fix them.

1. Create a new table that replicates the schema of the one you are trying to change the unique IDs.
2. Copy all the data from the source to the destination table letting the destination table use new IDs. This will result in an ordered sequence of IDs.
3. Fix any dependency issues with other tables: Foreign key relationships, etc.
4. Rename your source table something else and your destination table the original name of your source table.
5. Again, fix any dependencies and key relationships.

Needless to say you should perform a backup before you do any modifications to the database!