MYSQL TIPS - How to reset AUTO_INCREMENT in MySQL?

How to reset MySQL Autoincrement Column, MySQL Database have wonderfull feature Autoincrement Column Index. Programer also aply the feature Autoincrement to define its primary key. Autoincrement define as primary key because Autoincrement will take care of its value while inserting new record in rows.

Each time you add a new row, MySQL increments the value automatically and persist it to table. But sometime you may want to reset the Autoincrement column value to 1. Say you writing a sample application and you have inserted few rows already in the table. Now you want to delete these rows and reset the autoincrement column to 1 so that new row which you insert will have primary key value 1.

There are few methods to achieve this.

1. Direct Reset Autoincrement Value

   Alter table syntax provides a way to reset autoincrement column. Take a look at following example.

   ALTER TABLE table_name AUTO_INCREMENT = 1;

   Note that you cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed

2. Truncate Table

   Truncate table automatically reset the Autoincrement values to 0.

   TRUNCATE TABLE table_name;

   Use this with caution. When Truncation is used, it resets any AUTOINCREMENT counter to zero. From MySQL 5.0.13 on, the AUTOINCREMENT counter is reset to zero by TRUNCATE TABLE, regardless of whether there is a foreign key constraint.

   Once TRUNCATE is fired, the table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

All these techniques are value techniques to reset autoincrement column number. Use whatever suits your requirement

Disclaimer: The above commands can delete all your data! Be very very cautious