MySQL - how to reset auto_increment column to the latest value after deleting rows

Question

After deleting some rows from the table, I found that the auto_increment column is not reset to the current largest value. It seems that the value of the auto_increment column is not decreased automatically after deleting rows. How can I reset the column?

Answer 1

I think the value of the auto_increment column is not decreased automatically; you need to manually run queries to reset the value to the current maximum value.

You can run the following SQLs to reset the value.

1. Find the value for the auto_increment column you will reset to.

SELECT MAX( column_name )+1 FROM table_name;

2. Reset the value of the auto_increment column. Let's say the output of the above query is 100.

ALTER TABLE table_name AUTO_INCREMENT = 100