How to reset the initial value of the auto-increment column in the MySQL table

How to reset the initial value of the auto-increment column in the MySQL table

1. Problem Statement

In MySQL database design, an auto-increment numeric column is usually designed to be used as a primary key that is not related to the business. After frequent deletion or clearing operations occur in the database, its auto-increment value will still increase automatically. What should you do if you need to start over?

2. Solution

a. alter table

delete from table_name; 
ALTER TABLE table_name AUTO_INCREMENT = 1;  

If there is a lot of data in the database table, the deletion operation will take a long time, so this problem needs attention.

b. truncate

truncate table_name 

Simple and fast, clear data directly.

3. delete vs tuncate

The main differences are as follows:

• Truncate is fast and does not record logs, so rollback is not possible. delete and vice versa.
• Truncate will reset the index and auto-increment initial value, but delete will not.
• Truncate will not trigger the trigger, but delete will.

Thank you for reading, I hope it can help you, thank you for your support of this site!