Hello friends,
Today we will learn how to reset auto-increment in MySQL. Before starting this topic you should know that there can be only one auto column(auto-increment) in a table and it must be defined as a key(primary). There are various ways to reset auto-increment as follows:

1. To reset the auto-increment you need to execute following MySQL statement:

ALTER TABLE <tablename> AUTO_INCREMENT = <value>;

In above statement, <value> is greater than the current maximum value of the auto-increment column otherwise alter statement will not work. In other words, while resetting the auto increment value yo should keep in mind that it will now less than or equal to any value that have already been used.

2. Also you can reset the auto increment by trucating the table as follows:

Trucnate table <tablename>;

But in this case all records has been deleted and auto-increment value should be reset. Many users use DELETE statement to delete all the records but this statement only delete the records i.e., does not reset auto-increment.

3. One more way to reset the auto-increment. For this you don't need to know the current maximum value:

ALTER TABLE my_table MODIFY COLUMN ID INT(10) UNSIGNED;

ALTER TABLE my_table MODIFY COLUMN ID INT(10) UNSIGNED AUTO_INCREMENT;

In first MySQL statement, you will remove the auto-increment from the column and in second statement, you will again declare the field as autoincrement. This way the auto increment counter will reset and it will start automatically from the maximum value exists.