MySql : How to Reset Auto Increment Starting Value?

Recently I've been doing a project using MySql database server. I created some tables using auto increment number as the Primary Key.

As the name suggests, the auto increment number will increase every time I insert a new record into the table.

After some point, i wanted to delete all the dummy data i had inserted.

I used DELETE FROM TABLE_NAME statement to delete all the rows.. It removed all the records but did not reset the auto increment number to 1 again.

After a little research i found 2 useful ways to do the needful.

1) After deleting all the rows using DELETE FROM TABLE_NAME statement, use this simple syntax to reset the number :

ALTER TABLE TABLE_NAME AUTO_INCREMENT = 1

OR

2) You can use the statement TRUNCATE TABLE TABLE_NAME. This will remove all the records from the table and will automatically reset the number to 1.

While researching i also found that TRUNCATE statement is quicker than the DELETE FROM statement. This is because the TRUNCATE statement actually drops the table and recreates it. Whereas DELETE FROM statement deletes the records one by one, and is hence slower. Thus, its advisable to use TRUNCATE statement in the scenarios where you want to remove all the records of a table.


I hope you will find this solution useful.. :)

References :
• http://cookingprogrammers.blogspot.com/2008/03/mysql-to-reset-auto-increment-number.html
• http://www.electrictoolbox.com/article/mysql/delete-all-data-mysql/