Home / server / Questions / 42143
Accepted
Mark
Asked: 2009-07-18 05:43:00 +0800 CST

Reset Mysql auto increment field

  • 772

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    |                | 
+-------+------------------+------+-----+---------+----------------+
mysql

2 Answers

  1. 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>;
    • 7

  2. ALTER TABLE tbl_name AUTO_INCREMENT = 1

    If the table is empty.

    • 2