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

ALTER TABLE news_feed DROP id

ALTER TABLE news_feed ADD  id BIGINT( 200 ) NOT NULL AUTO_INCREMENT FIRST ,ADD PRIMARY KEY (id)

I used this in some of my scripts , the id field is droped and then added back with previous settings , all the existent fields within the database table are filled in with new auto increment values , this should also work with InnoDB .

Note that all the fields within the table will be recounted and will have other ids !!!.

Answer 2

it is for empty table:

ALTER TABLE `table_name` AUTO_INCREMENT = 1;

if you have data but you want to tidy up it, i recommend use this :

ALTER TABLE `table_name` DROP `auto_colmn`;
ALTER TABLE `table_name` ADD  `auto_colmn` INT( {many you want} ) NOT NULL AUTO_INCREMENT FIRST ,ADD PRIMARY KEY (`auto_colmn`);

Answer 3

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.

Answer 4

The auto increment counter for a table can be (re)set in two ways:

1. By executing a query, like others already explained:

   ALTER TABLE <table_name> AUTO_INCREMENT=<table_id>;

2. Using Workbench or other visual database design tool. I am gonna show in Workbench how it is done - but it shouldn't be much different in other tool as well. By right click over the desired table and choosing Alter table from the context menu. On the bottom you can see all the available options for altering a table. Choose Options and you will get this form:

   Then just set the desired value in the field Auto increment as shown in the image. This will basically execute the query shown in the first option.