Mysql – Reset Mysql auto increment field

MySQL

Is there a way to reset the auto incrementing value of a field so it starts back at 1 ?

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| name  | varchar(45)      | NO   |     | NULL    |                | 
+-------+------------------+------+-----+---------+----------------+

Best Answer

To reset back to it's lowest possible value.

ALTER TABLE <tablename> AUTO_INCREMENT=0;

If you're doing it because you've deleted records with the view to resetting a table back to an empty state, then consider using TRUNCATE in the future, which will take care of the auto increment for you.

TRUNCATE <tablename>;
Related Topic