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);