How do I reset the self increment AUTO_INCREMENT initial value of mysql
- 2020-05-17 06:45:39
- OfStack
Reset MySQL self - increment AUTO_INCREMENT initial value
Note that existing data is deleted using any of the following methods.
Method 1:
delete from tb1;
ALTER TABLE tbl AUTO_INCREMENT = 100;
(for good, you can set AUTO_INCREMENT to start with any value)
Tip: if there are a lot of table columns and data, the speed will be slow, such as more than 900,000 pieces, will be more than 10 minutes.
Method 2:
truncate tb1;
(benefit, simplicity, AUTO_INCREMENT value start counting again.)
How do I reset mysql's self-increment column
1. Support setting the value of self-increment column
ALTER TABLE table_name AUTO_INCREMENT = 1;
However, this method can automatically set the value greater than the currently used value, and cannot set the value less than or equal to the currently used value of the self-added column. If myisam is set less than or equal to, the value of the self-increment column is automatically set to
Current maximum plus 1. innodb will not change.
2. Set the autoincrement column to 0 via TRUNCATE, and from MySQL 5.0.13, TRUNCATE can reset the autoincrement column to 0.myisam and innode.
TRUNCATE TABLE table_name;
3.drop and create rebuild table mode reset the self-increment list to 0
DROP TABLE table_name;
CREATE TABLE table_name { ... };
Note that existing data is deleted using any of the following methods.
Method 1:
delete from tb1;
ALTER TABLE tbl AUTO_INCREMENT = 100;
(for good, you can set AUTO_INCREMENT to start with any value)
Tip: if there are a lot of table columns and data, the speed will be slow, such as more than 900,000 pieces, will be more than 10 minutes.
Method 2:
truncate tb1;
(benefit, simplicity, AUTO_INCREMENT value start counting again.)
How do I reset mysql's self-increment column
1. Support setting the value of self-increment column
ALTER TABLE table_name AUTO_INCREMENT = 1;
However, this method can automatically set the value greater than the currently used value, and cannot set the value less than or equal to the currently used value of the self-added column. If myisam is set less than or equal to, the value of the self-increment column is automatically set to
Current maximum plus 1. innodb will not change.
2. Set the autoincrement column to 0 via TRUNCATE, and from MySQL 5.0.13, TRUNCATE can reset the autoincrement column to 0.myisam and innode.
TRUNCATE TABLE table_name;
3.drop and create rebuild table mode reset the self-increment list to 0
DROP TABLE table_name;
CREATE TABLE table_name { ... };