How to reset the initial value of AUTO_INCREMENT in the auto-increment column of mysql

Source: Internet
Author: User

Reset the initial value of AUTO_INCREMENT In the MySQL auto-incrementing Column
Note that existing data will be deleted using any of the following methods.

Method 1:
Delete from tb1;
Alter table tbl AUTO_INCREMENT = 100;
(Benefit, you can set AUTO_INCREMENT to start with any value)
Tip: if there are many columns and data, the speed will be very slow. For example, if there are more than 0.9 million rows, it will take more than 10 minutes.

Method 2:
Truncate tb1;
(Benefit, simple. The AUTO_INCREMENT value starts counting again .)

How to reset the auto-increment column of mysql

1. the auto-increment column value can be set.

Alter table table_name AUTO_INCREMENT = 1;
However, this method can be set to be greater than the currently used value, and cannot be set to be less than or equal to the currently used auto-increment column value. If myisam is set to be less than or equal to, the value of the auto-incrementing column is automatically set
The current maximum value is 1. Innodb will not change.

2. Use TRUNCATE to set the auto-increment column to 0. The auto-increment column 0. myisam and innode can be reset from MySQL 5.0.13.

Truncate table table_name;
3. Reset the auto-increment column 0 in the form of drop and create Table Reconstruction

Drop table table_name;
Create table table_name {...};

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.