How To Reset MySQL Auto Increment Column

MySQL database provides a beautiful feature of Auto Increment Column index. Database table can define its primary key as Auto Increment number and MySQL will take care of its unique value while inserting new 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 Auto Increment column value to 1. You are writing a sample application and you have inserted few rows already in the table. Now you want to delete these rows and reset the auto increment column to 1 so that new row which you insert will have primary key value 1.

Following methods can be used to Reset MySQL Auto Increment Column

By Altering table

Alter table syntax provides a way to reset auto increment column.


By Truncate Table

Truncate table will automatically reset the Auto increment values to 0.

TRUNCATE TABLE table_name;

By Dropping and Recreate Table

This is simple way of resetting auto increment index. But not recommended.

* Above commands can delete all your data! Be careful while using commands.

%d bloggers like this: