How to reset AUTO_INCREMENT in MySQL?

How can I reset the AUTO_INCREMENT of a field? I want it to start counting from 1 again.

Answers:

You can reset the counter with:

ALTER TABLE tablename AUTO_INCREMENT = 1

For 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 equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

See How to Reset an MySQL AutoIncrement using a MAX value from another table? on how to dynamically get an acceptable value.

---

Simply like this:

ALTER TABLE tablename AUTO_INCREMENT = value;

reference: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

---

There is a very easy way with phpmyadmin under the "operations" tab, you can set, in the table options, autoincrement to the number you want.

---

There are good options given in How To Reset MySQL Autoincrement Column

Note that ALTER TABLE tablename AUTO_INCREMENT = value; does not work for InnoDB

---

Adding an update because the functionality changed in MySQL 5.6. As of MySQL 5.6 you CAN use the simple ALTER TABLE with InnoDB:

ALTER TABLE tablename AUTO_INCREMENT = 1;

The docs are updated to reflect this:

http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

My testing also shows that the table is NOT copied, the value is simply changed.

---

You can also use the syntax TRUNCATE table like this : TRUNCATE TABLE table_name

BEWARE!! TRUNCATE TABLE your_table will delete everything in your your_table!!

---

You can simply truncate the table to reset the sequence

TRUNCATE TABLE TABLE_NAME

---

I tried to alter the table and set auto_increment to 1 but it did not work. I resolved to delete the column name I was incrementing, then create a new column with your preferred name and set that new column to increment from the onset.

---

To update latest plus one id

 ALTER TABLE table_name AUTO_INCREMENT = 
 (SELECT (id+1) id FROM table_name order by id desc limit 1);

How To Reset MySQL Autoincrement Column

MySQL database provides a wonderful feature of Autoincrement Column index. Your database table can define its primary key as Autoincrement number and MySQL will take care of its unique value while inserting new rows.

Each time you add a new row, MySQL increments the value automatically and persist it to table. But sometime you may want to reset the Autoincrement column value to 1. Say you writing a sample application and you have inserted few rows already in the table. Now you want to delete these rows and reset the autoincrement column to 1 so that new row which you insert will have primary key value 1.

There are few methods to achieve this.

1. Directly Reset Autoincrement Value

Alter table syntax provides a way to reset autoincrement column. Take a look at following example.

ALTER TABLE table_name AUTO_INCREMENT = 1;

Note that you cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

2. Truncate Table

Truncate table automatically reset the Autoincrement values to 0.

TRUNCATE TABLE table_name;

Use this with caution. When Truncation is used, it resets any AUTO_INCREMENT counter to zero. From MySQL 5.0.13 on, the AUTO_INCREMENT counter is reset to zero by TRUNCATE TABLE, regardless of whether there is a foreign key constraint.

Once TRUNCATE is fired, the table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

3. Drop & Recreate Table

This is another way of reseting autoincrement index. Although not very desirable.

DROP TABLE table_name;
CREATE TABLE table_name { ... };

All these techniques are value techniques to reset autoincrement column number. Use whatever suits your requirement.

Disclaimer: The above commands can delete all your data! Be very very cautious.