How to reset AUTO_INCREMENT in MySQL?

mysql sql auto-increment
homerun picture homerun · Jan 19, 2012 · Viewed 1.3M times · Source

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

Answer

Niels picture Niels · Jan 19, 2012

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.

Related questions

Add Auto-Increment ID to existing table?

I have a pre-existing table, containing 'fname', 'lname', 'email', 'password' and 'ip'. But now I want an auto-increment column. However, when I enter: ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT I get the following: #1075 - Incorrect table …

Read More
Auto Increment after delete in MySQL

I have a MySQL table with a primary key field that has AUTO_INCREMENT on. After reading other posts on here I've noticed people with the same problem and with varied answers. Some recommend not using this feature, others state …

Read More
How can I avoid getting this MySQL error Incorrect column specifier for column COLUMN NAME?

How can I avoid getting this MySQL error Incorrect column specifier for column topic_id ? MySQL Error... #1063 - Incorrect column specifier for column 'topic_id' SQL Schema... CREATE TABLE discussion_topics ( topic_id char(36) NOT NULL AUTO_INCREMENT, project_id …

Read More