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.

ALTER TABLE table_name AUTO_INCREMENT = 1;

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.

Nishant Vaity

I am a skilled self-taught PHP developer with years of experience writing scalable code for high-traffic websites. I am proficient in Linux, Apache, MySQL, PHP, HTML, JavaScript, CSS, Git, SVN, Vagrant, and SSH, among others. I am contributor for WordPress open source plugins and Translator (Marathi) for WordPress 4.5.x. I am passionate about the technologies I use and am always eager to share that passion and learn more.

Leave a Reply

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