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 1. This means that the next time you insert a record into this table the value of the auto incremental primary key will be 1. If the highest value for the column is already greater than 1 then it will be set to the highest value plus 1.
1. Directly Reset
Alter table syntax provides a way to reset autoincrement column. See the following example.
ALTER TABLE mytable AUTO_INCREMENT = 1;
Note that you cannot reset the counter to a value less than or equal to any that have already been used.
2. Truncate Table
Truncate table automatically reset the Autoincrement values to 0.
TRUNCATE TABLE mytable;
3. Drop & Recreate Table
This is another way of reset autoincrement index.
DROP TABLE mytable;
CREATE TABLE mytable { … };
Note: The above commands can flush your table! So, before running the above command I would suggesst take the backup of table first.
