Realization method of resetting the initial value of self adding column in table in MySQL

Realization method of resetting the initial value of self-adding column in table in MySQL

1. Question raising

In the database design of MySQL, 1 will generally design self-increasing digital columns as business-independent primary keys. After frequent deletion or emptying of the database, its self-increasing value will still increase automatically. What should I do if I need to start over?

2. Solutions

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 delete operation will last for a long time, which needs attention.

b. truncate

truncate table_name 

Simple and quick, directly empty data.

3. delete vs tuncate

The main differences are as follows:

truncate is fast and does not log, so rollback operation cannot be performed. delete on the contrary. truncate resets indexes and self-increments initializations, delete does not truncate does not trigger trigger, but delete does.

Thank you for reading, hope to help everyone, thank you for your support to this site!