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.

DROP TABLE table_name;
CREATE TABLE table_name columns;

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

Nishant Vaity

Knowledgeable and skilled Technology Lead with an ability of software development and supervision. Possess a Bachelor of Science (BS) in Information Technology along with 10+ years of experience with hands-on coding and team management. By profession, I am a Software Engineer, Technology Mentor & Entrepreneur. Passionate about the technologies I use and always eager to share & learn more from that passion.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: