MySQL Reset Auto Increment After Truncate | Tutorial Note

How to reset auto increment after truncating in MySQL? Read the article to know more. At Bobcares, with our MySQL Support Service, we can handle your MySQL issues.

How to reset auto increment after truncating in MySQL?

MySQL offers the useful capability of auto-increment. Every time a new row is added to the table, it automatically creates a unique identity for that row. In most cases, we use a table’s main key field’s auto-increment property. When we add new records to a table, MySQL automatically increases the value in the auto-increment column.

In MySQL, we can truncate a table to remove all of its rows. The primary key column’s auto-increment value—often referred to as id—does not reset by default following truncation. This means that the auto-increment value will resume where it left off if we add new records to the table after truncation.

The auto-increment value after truncation may, however, need to be reset in some circumstances so that it starts at 1. To accomplish this, we can reset the auto-increment value by using the TRUNCATE TABLE statement followed by an ALTER TABLE statement.

Steps to reset auto increment after truncating in MySQL

1. Firstly, truncate the table using the code:

2. Then reset the auto-increment value using the ALTER TABLE statement:

The next record that is inserted will begin with an auto-increment value of 1 following the execution of these statements. Please be aware that truncating a table and setting the auto-increment value also permanently deletes all of the data from the table and should only be done with caution. If necessary, make sure we have a backup of the data because once it has been truncated, it cannot be retrieved.

[Looking for a solution to another query? We are just a click away.]

Conclusion

The article simplified the method to reset auto increment after truncating in MySQL in two steps. Always keep a backup of the data because once it has been truncated, it cannot be retrieved.