Tuesday, July 21, 2015

How to Reset the AUTO_INCREMENT in MySQL using a simple SQL query

For example, we have a table with a list of people and we have an ID column. We wanted to add a number to every row automatically, then we enabled AUTO_INCREMENT function for ID column. Now when we add a new person to the table it will automatically increment the number for each time on ID column. 
So let's say that we have a 10 people name in the table. And we have deleted one item/row or person from the table and the next time when are trying to add a new person the id count will resume it will give an 11 for next person.
So for that, we need to use a simple SQL query which will reset the ID count and next time it will add a 10 for next person in above example.

All I'm gonna to do is open up the phpMyAdmin, sign in using the username and password. Then select the database from the left side and go to "SQL" tab from top bar.

Now in query box type the next query:
ALTER TABLE table_name AUTO_INCREMENT = 1
Change the "table_name" from above query to the table name you want to reset the AUTO_INCREMENT count and hit enter or press on "Go" button.

After you can add a new person to your table and you can see that AUTO_INCREMENT has been reset.
Also, you can change the number 1 from "AUTO_INCREMENT = 1" to any number. For example, if I wanted to set 20 for next person to start the count from 20 then replace 1 with 20.

Subscribe To Get Free Updates!