How To Reset Autoincrement value of Column in MySQL

[13251 views]

It is very often you find that a column such as the ID column on a table will auto increment. This simply means that the next insert made in the table will have an ID that is one more then the previous one and therefore all ID's will be unique. This is done by Auto_Increment keyword in MySQL.

What is Auto_Increment in MySQL?

Auto_Increment is a keyword in SQL that operates on Number data types. Auto_Increment is used to automatically generate sequential numeric values every time that a new record is inserted into a table for a field defined as Auto_Increment.

MySQL removes the complexity of generating unique Primary Key ID by the user programmatically and provides Auto_Increment keyword feature to generate primary keys. Auto_Increment keyword is used with the INT data type.

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record you enter in the table. To change the AUTO_INCREMENT sequence start value with some another value, you can use the following SQL statement:

ALTER TABLE Persons AUTO_INCREMENT=50;

But, if you delete one row from the table, the table will still auto-increment as if the row had not been deleted at all. The result is a gap in the sequence numbers. This is normally not that big issue, but you may want to reset the auto-increment field.

OR

If someone Deletes Row in the middle and then again wants to Reset the Auto_Increment Column value to the next successive number or 1.

Follow any of these steps mentioned below to Reset AUTO_INCREMENT in MySQL :

ALTER Table (Reset Autoincrement to any value directly)

ALTER TABLE table_name AUTO_INCREMENT = 1;

Note:

You cannot reset the counter value to be less than or equal to any value that have already been used.

For MyISAM SQL, if the value is equal to or less than the maximum value in the AUTO_INCREMENT column of the table, the value automatically gets reset to the current maximum value in the column plus one.
For InnoDB SQL, if the value is less than the current maximum value in the column of MYSQL table, no error will occur and the current sequence value will not be changed.

Truncate Table (If you want to delete all rows in the table use)

TRUNCATE TABLE table_name;

If you want to view the last Auto_increment ID that was generated by MySQL, you can use the LAST_INSERT_ID function in MySQL. Below is the Syntax for that

SELECT LAST_INSERT_ID();

Disclaimer: The above commands can delete all your data! So be very cautious.