It is possible to reset the auto increment value of a MySQL auto incremental primary key to a new value, either higher or lower than what it would otherwise next be. This post looks at how to do this using a MySQL query and also with phpMyAdmin.
The following example changes the auto increment value for the table named “mytable” to 500. This means that the next time you insert a record into this table the value of the auto incremental primary key will be 500. If the highest value for the column is already greater than 500 then it will be set to the highest value plus 1.
ALTER TABLE mytable AUTO_INCREMENT = 500
The next example shows doing the same thing in phpMyAdmin. Select the database then table, and click the “Operations” tab. The current next auto increment value is already displayed – to change it enter a new value and then click the “Go” button. The relevent buttons etc are highlighted with red circles in the screenshot below.
In the above example, the current auto increment value is 39, meaning that when the next record is inserted the primary key will be 39. You can attempt change it to a lower number, e.g. 20, but if the highest value for the primary key is higher than 20 (e.g. 23) then it will instead be changed to the current highest plus 1 (e.g. if the highest value was 23 then it will be set to 24 instead of 20).
