MySQL RESET AUTO INCREMENT

Auto-increment is a useful feature provided by MySQL. It always generates a unique identity for each newly inserted row into the table. Generally, we use the auto-increment attribute for the primary key field in a table. Each time we will insert new records into a table, MySQL increments the value automatically to the auto-increment column.

For example, we have a table that already contains ten rows. Next, if we add a new row without giving the value to the auto-increment column, MySQL automatically adds a new row with id=11.

Sometimes, we need to delete these rows and reset the auto-increment column so that when we do the next insertion into a table, the first record's identity will have primary key value 1.

MySQL allows various ways to reset the auto-increment column value. These ways are:

1. Using the ALTER TABLE statement
2. Using the TRUNCATE TABLE statement
3. Using a pair of DROP TABLE and CREATE TABLE statement

First, we will create a table named "Animals" and assign the id column with the auto-increment attribute. See the below statement:

Next, we will insert some sample data into the "Animals" table as follows:

Now, we will execute the below statement to verify the insert operation:

We should get the below output. In the insert statement, we have not specified any value for the auto-increment column. But we can observe in the output that MySQL automatically generates a unique number in the sequence order for this field.

Let us take this table for a demonstration of methods to reset the auto-increment column value in detail.

Using the ALTER TABLE Statement

The ALTER TABLE statement is used to change the name of a table or any table field. It is also used to add, delete, or reset an existing column in a table. MySQL also allows this statement to reset the auto-increment column value whenever we want.

Syntax

The following is the syntax of the ALTER TABLE statement to reset the auto-increment column value:

In this syntax, we have first specified the name of a table after the ALTER TABLE clause. Next, we specify the value which we want to reset in the expression as AUTO_INCREMENT = value.

It is to note that MySQL does not allows resetting the value which has less than or equal to the already been used value. For example, if we are using InnoDB, the value should not be less than or equal to the auto-increment column's current maximum value.

For MyISAM, if the specified value is less than or equal to the maximum value of the auto_increment column, the value will be reset to the current maximum plus one.

For InnoDB, if the specified value is less than the current maximum value of the auto_increment column, MySQL does not issue any error, and the current sequence also unchanged.

Let's delete the lowest row in the table with id = 6 as below:

If we add a new record, MySQL will assign this record with the id = 7. But if we want to reset this column to the last value, we can use the ALTER TABLE statement as follows:

If we will insert new records into this table, MySQL starts generating the row id with 6. Execute the below query:

Finally, query the data in the table to see the effect. The below output explains it more clearly where we have six rows with the last auto-increment value is 6 instead of 7.

Using TRUNCATE TABLE Statement

The TRUNCATE TABLE statement in MySQL completely deletes the table's data without removing a table's structure and always resets the auto-increment column value to zero.

Syntax

The following syntax illustrates the TRUNCATE TABLE statement to reset the auto-increment column value:

It is to note that we cannot recover the deleted data from the table after executing the TRUNCATE TABLE statement.

Using DROP TABLE and CREATE TABLE Statements

We can also reset the auto-increment column value using a pair of DROP TABLE and CREATE TABLE statements. This method permanently removes the complete records from the table.

Similar to the TRUNCATE TABLE query, the pair of DROP TABLE and CREATE TABLE statements first drop the table and then recreate it. Therefore, MySQL resets the auto-increment column value to zero.