Summary: in this tutorial, we will show you various ways to reset auto-increment values of
AUTO_INCREMENT columns in MySQL.
MySQL provides you with a useful feature called auto-increment. You can assign the
AUTO_INCREMENT attribute to a column of a table to generate a unique identity for the new row. Typically, you use the
AUTO_INCREMENT attribute for the primary key column of the table.
For example, if the table has eight rows and you insert a new row without specifying the value for the auto-increment column, MySQL will automatically insert a new row with
id value 9.
Sometimes, you may need to reset the value of the auto-increment column so that the first record’s identity that you insert into the table starts from a specific number e.g., 1.
In MySQL, you can reset auto increment values in various ways.
MySQL reset auto increment value examples
First, create a table named
tmp and assign the
AUTO_INCREMENT attribute to the
id primary key column.
CREATE TABLE tmp (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(45) DEFAULT NULL,
PRIMARY KEY (id)
Second, insert some sample data into the
INSERT INTO tmp(name)
Third, query the
tmp table to verify the insert operation:
We have three rows with values of ID column are 1, 2, and 3. Perfect! It is time to practice reset the auto-increment value of the ID column.
Using ALTER TABLE statement
You can reset the auto-increment value by using the
ALTER TABLE statement. The syntax of the
ALTER TABLE statement to reset the auto increment value is as follows:
ALTER TABLE table_name AUTO_INCREMENT = value;
You specify the table name after the
ALTER TABLE clause and the
value which you want to reset to in the expression
Notice that the
value must be greater than or equal to the current maximum value of the auto-increment column.
Let’s delete the last record in the
tmp table with
id value 3:
DELETE FROM tmp
ID = 3;
If you insert a new row, MySQL will assign 4 to the
id column of the new row. However, you can reset the number generated by MySQL to 3 by using the
ALTER TABLE statement as the following:
ALTER TABLE tmp AUTO_INCREMENT = 3;
Now, let’s try to insert a new row into the
tmp table and query data from it to see the effect:
INSERT INTO tmp(name)
VALUES ('MySQL example 3');
We have three rows with the last auto-increment value is 3 instead of 4, which is what we expected.
Using TRUNCATE TABLE statement
The TRUNCATE TABLE statement removes all the data from a table and resets the auto-increment value to zero.
The following illustrates the syntax of the
TRUNCATE TABLE statement:
TRUNCATE TABLE table_name;
By using the
TRUNCATE TABLE statement, you delete all data from the table permanently and reset the auto-increment value to zero.
Using DROP TABLE and CREATE TABLE statements
TRUNCATE TABLE statement, those statements drop the table and recreate it, therefore, the value of the auto-increment is reset to zero.
DROP TABLE table_name;
CREATE TABLE table_name(...);
In this tutorial, you have learned how to reset auto-increment value in MySQL in various ways. The first way is preferable because it is the easiest way and has no side effect.