How to reset AUTO_INCREMENT in MySQL?

manpreet Best Answer 2 years ago

How can I reset the AUTO_INCREMENT of a field? I want it to start counting from 1 again.

manpreet 2 years ago

The best solution that worked for me:

ALTER TABLE my_table MODIFY COLUMN ID INT(10) UNSIGNED;
COMMIT;
ALTER TABLE my_table MODIFY COLUMN ID INT(10) UNSIGNED AUTO_INCREMENT;
COMMIT;

Its fast, works with innoDB, and I don't need to know the current maximum value! This way the auto increment counter will reset and it will start automatically from the maximum value exists.