How to reset AUTO_INCREMENT in MySQL
You can reset the counter with:
ALTER TABLE tablename AUTO_INCREMENT = 1For InnoDB you cannot set the auto_increment value lower or equal to the highest current index. (quote from ViralPatel):
Note that you cannot reset the counter to a value less than or equalto any that have already been used. For MyISAM, if the value is lessthan or equal to the maximum value currently in the AUTO_INCREMENTcolumn, the value is reset to the current maximum plus one. ForInnoDB, if the value is less than the current maximum value in thecolumn, no error occurs and the current sequence value is not changed.
See How can I reset an MySQL AutoIncrement using a MAX value from another table? on how to dynamically get an acceptable value.
SET @num := 0;UPDATE your_table SET id = @num := (@num+1);ALTER TABLE your_table AUTO_INCREMENT =1;I think this will do it
Simply like this:
ALTER TABLE tablename AUTO_INCREMENT = value;Reference: 13.1.9 ALTER TABLE Statement