Auto_Increment After deleted data from the table

Question

How can I reset the auto_increment of a field? I want it to start counting from 1 again after I delete all the data.

Answer 1

ALTER TABLE tablename AUTO_INCREMENT = 1;

See also the same question on stackoverflow.

Answer 2

It will be better to truncate table to start auto_increment from beginning of a auto_increment field:

Truncate table tableName;

as delete table starts auto_increment from last auto_incremented number.

Second option is, if you have to delete all data then you may drop and re-create table to start auto_increment from beginning.