Summary: in this tutorial, we’ll 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 unique identity for the new row. Typically you use the auto-increment attribute for the primary key column of a 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 8 rows and you insert a new row without specifying the value for the auto-increment column, MySQL will automatically inserts 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, let’s create a tmp table and assign AUTO_INCREMENT attribute to id primary key column.
CREATE TABLE tmp ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(45) DEFAULT NULL, PRIMARY KEY (id) );
Second, insert some sample data into the tmp table:
INSERT INTO tmp(name) VALUES('test 1'), ('test 2'), ('test 3');Third, query the tmp table to see if the insert operations has been done successfully:
SELECT * FROM tmp;
We have three rows with values of ID column are 1, 2, and 3. Perfect! It is time to practice reset auto increment value of the ID column.
MySQL reset auto increment value by using ALTER TABLE statement
You can reset auto increment value by using the ALTER TABLE statement. The syntax of the ALTER TABLE statement to reset 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 we 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.
Let’s delete the last record in the tmp table with id value 3:
DELETE FROM tmp WHERE ID = 3;
If you insert a new row, MySQL will assign 4 to id column of for 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'); SELECT * FROM tmp;We have three rows with the last auto-increment value is 3 instead of 4, which is what we expected.
MySQL reset auto increment value by using TRUNCATE TABLE statement
The TRUNCATE TABLE statement removes all the data of a table and reset 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 can only reset the auto-increment value to zero. In addition, all the data in the table is wiped out so you should use the TRUNCATE TABLE statement with extra caution.
MySQL reset auto increment value 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. Like the TRUNCATE TABLE statement, those statements removes all the data and reset the auto-increment value 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.
原文链接：http://outofmemory.cn/mysql/tips/mysql-reset-auto-increment