Friday, January 14, 2011

Reset Mysql auto increment field

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    |                | 
+-------+------------------+------+-----+---------+----------------+
  • ALTER TABLE tbl_name AUTO_INCREMENT = 1

    If the table is empty.

    From Eppz
  • 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>;
    
    From Dan Carley

0 comments:

Post a Comment