Reset AUTO_INCREMENT after Delete in MySQL

Sometimes the most recent entries get deleted from our table. After the rows are deleted, try to insert some rows. We can observe a gap in the number_values of the auto_increment column, which is ok in many cases. However, many times the requirement is to reset the auto_increment to maintain the sequence. Well, there is a solution for the same.

Let us understand through an example. Assume that we have the table sales_data with the below row entries.

We will be deleting the last two rows from the table sales_data and then inserting a new row and finally select * to sales_data. Observe the below queries for the same.

DELETE FROM sales_data WHERE sale_person_id  > 4;
INSERT INTO sales_data (sale_person_name,commission_percentage,no_products_sold) 
VALUES("Rani",15,5000); 
SELECT * FROM sales_data;

Output :-

As we can see that since two rows with sale_person_id values ‘5’ and ‘6’ got deleted, the newly inserted row has id ‘7’. To avoid this behaviour, let us write another query, as shown below.

ALTER TABLE sales_data AUTO_INCREMENT=1;

Let us again repeat the insert after deleting the rows and view the results output will be as shown in below figure 1.3

As we can see, the sequence is maintained, and the new row had sale_person_id value as ‘5’ instead of ‘7’.

To know all about How to reset auto_increment in MySQL read through RESET AUTO_INCREMENT in MYSQL.