How to reset AUTO_INCREMENT in MySQL?

Question

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

Answer 1

You can simply truncate the table to reset the sequence

TRUNCATE TABLE TABLE_NAME

Answer 2

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!!

Answer 3

Best way is remove the field with AI and add it again with AI, works for all tables

Answer 4

ALTER TABLE tablename AUTO_INCREMENT = 1

Answer 5

I suggest you to go to Query Browser and do the following:

1. Go to schemata and find the table you want to alter.
2. Right click and select copy create statement.
3. Open a result tab and paste the create statement their.
4. Go to the last line of the create statement and look for the Auto_Increment=N, (Where N is a current number for auto_increment field.)
5. Replace N with 1.
6. Press ctrl+enter.

Auto_increment should reset to one once you enter new row int the table.

I don't know what will happen if you try to add a row where an auto_increment field value already exist.

Hope this help!

Answer 6

As of MySQL 5.6 the approach below works faster due to online DDL (note algorithm=inplace):

alter table tablename auto_increment=1, algorithm=inplace;