How to reset Auto-increament in MySQL?

MySQLIt 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.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *