MySQL: resetting auto_increment

When using a table with an auto_increment column, it is sometimes useful to know how to reset the auto_increment value. For instance, if you needed to add some temporary test records to a table and removed them later, but didn't want a "gap" to appear in the values of the auto_increment field in question. As an example, suppose you had a table with a primary key auto_increment field named id with 7 records in it. You then add test records 8, 9, 10 and subsequently remove these records. This causes the auto_increment to be set to 11 (the last inserted id plus 1). Thus the next record inserted would have an id value of 11, causing a "gap" (... 4, 5, 6, 7, 11). To correct this, after deleting records 8, 9, 10 reset the auto_increment to 8 using

ALTER TABLE test_table AUTO_INCREMENT = 8

This would set the auto_increment to 8 thus removing the "gap". The auto_increment field cannot be reset to a value smaller than the current largest auto_increment field value plus 1 (so in the preceding example you couldn't set it to 7 or something smaller). You can also use this feature if for some reason you wanted to start the value of the auto_increment for a given table as something other than the default value of 1 . Suppose you had a table of user data which required the user ids to be numbers larger than 1000. You could then set the auto_increment for that table to 1000 immediately after creating it. For more on using auto_increment see the MySQL documentation page.