Summary: in this tutorial, we will show you various ways to reset the 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.
Whenever you insert a new row into a table, MySQL automatically assigns a sequence number to the AUTO_INCREMENT
column.
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.
In MySQL, you can reset auto-increment values in various ways.
MySQL reset auto-increment value examples
First, create a table named items
and assign the AUTO_INCREMENT
attribute to the id
primary key column.
CREATE TABLE items (
id INT AUTO_INCREMENT,
name VARCHAR(45) NOT NULL,
PRIMARY KEY (id)
);
Code language: SQL (Structured Query Language) (sql)
Second, insert some sample data into the items
table:
INSERT INTO items(name)
VALUES('Item 1'),
('Item 2'),
('Item 3');
Code language: SQL (Structured Query Language) (sql)
Third, query the items
table to verify the insert operation:
SELECT * FROM items;
Code language: SQL (Structured Query Language) (sql)
Output:
+----+--------+
| id | name |
+----+--------+
| 1 | Item 1 |
| 2 | Item 2 |
| 3 | Item 3 |
+----+--------+
3 rows in set (0.00 sec)
Code language: JavaScript (javascript)
We have three rows with values of ID column 1, 2, and 3. Perfect! It is time to practice resetting the auto-increment value of the ID column.
1) 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;
Code language: SQL (Structured Query Language) (sql)
In this syntax, you specify the table name after the ALTER TABLE
clause and the value
which you want to reset to in the expression AUTO_INCREMENT=value
.
Notice that the value
must be greater than or equal to the current maximum value of the auto-increment column.
First, delete the last record in the items
table with id
value 3:
DELETE FROM items
WHERE id = 3;
Code language: SQL (Structured Query Language) (sql)
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.
Second, reset the auto-increment value to 3 using the ALTER TABLE
statement:
ALTER TABLE items AUTO_INCREMENT = 3;
Code language: SQL (Structured Query Language) (sql)
Third, insert a new row into the items
table:
INSERT INTO items(name)
VALUES ('Items 4');
Code language: SQL (Structured Query Language) (sql)
Finally, retrieve data from the items table:
SELECT * FROM items;
Output:
+----+---------+
| id | name |
+----+---------+
| 1 | Item 1 |
| 2 | Item 2 |
| 3 | Items 4 |
+----+---------+
3 rows in set (0.00 sec)
Code language: JavaScript (javascript)
We have three rows with the last auto-increment value is 3 instead of 4, which is what we expected.
2) Using the 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;
Code language: SQL (Structured Query Language) (sql)
By using the TRUNCATE TABLE
statement, you delete all data from the table permanently and reset the auto-increment value to zero.
For example:
TRUNCATE TABLE items;
3) Using DROP TABLE and CREATE TABLE statements
You can use a pair of statements: DROP TABLE and CREATE TABLE to reset the auto-increment column. Note that this method deletes all data from the table permanently.
Like the TRUNCATE TABLE
statement, those statements drop the table and recreate it, therefore, the value of the auto-increment is reset to zero. For example:
DROP TABLE items;
CREATE TABLE items (
id INT AUTO_INCREMENT,
name VARCHAR(45) NOT NULL,
PRIMARY KEY (id)
);
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned various methods to reset auto-increment values in MySQL. The ALTER TABLE AUTO_INCREMENT = value
statement is preferable as it is the easiest and has no side effects.