How To Reset MySQL Autoincrement Column - MySQL Developer Tutorial

How To Reset MySQL Auto- increment Column

Auto Increment is feature in MySQL that enabled the column in the table to increment automatically. Once you create the column with Auto increment,the value starts with 1 and on each new insert it increases by 1 automatically.

Often we get into situation where we need to reset the value. There are different ways we can reset the value of Auto Increment.

1) Truncate Table : 

If you don't need the data in the table, then you can simple Truncate the table and it will reset the Auto Increment value for Column.  If you use Delete statement, that does not reset Auto Increment Column.

To truncate table , you can use below syntax.

Truncate table YourTableName;

2) Drop and Recreate Table :

If you really don't care about the data, you can simple script out the table definition, drop the table and recreate the table. that will reset the auto increment column value.

3) Reset the Auto Increment Directly :

You can set the Auto Increment value for the table directly. Remember that you can not reset the value lower than the max value which is already used in your auto increment table.

Let's say if I have a table customer and it has 5 rows. The last value for auto increment is 5. I can run the below statement, it will run fine but will not reset the value for Auto increment.

alter table customer auto_increment=1;

When I will insert the next row, the value will start with 6.
If I would like to reset the value higher than the max value in auto increment ( that is 5 in my case)  , I can do that.Let's say if I want to skip and start with 100. I can use below statement for customer.

alter table customer auto_increment=100;

Reset Auto Increment Column in MySQL Table - MySQL Developer Tutorial