如何在 MySQL 中重置自动增量？

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

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.

ALTER TABLE tablename AUTO_INCREMENT = 1

Simply like this:

ALTER TABLE tablename AUTO_INCREMENT = value;

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

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

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

The highest rated answers to this question all recommend "ALTER yourtable AUTO_INCREMENT= value". However, this only works when value in the alter is greater than the current max value of the autoincrement column. According to the MySQL documentation:

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, you can use ALTER TABLE ... AUTO_INCREMENT = value as of MySQL 5.0.3, but if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

In essence, you can only alter AUTO_INCREMENT to increase the value of the autoincrement column, not reset it to 1, as the OP asks in the second part of the question. For options that actually allow you set the AUTO_INCREMENT downward from its current max, take a look at Reorder / reset auto increment primary key.

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.

SET  @num := 0;

UPDATE your_table SET id = @num := (@num+1);

ALTER TABLE your_table AUTO_INCREMENT =1;

I think this will do it

The best solution that worked for me:

ALTER TABLE my_table MODIFY COLUMN ID INT(10) UNSIGNED;
COMMIT;
ALTER TABLE my_table MODIFY COLUMN ID INT(10) UNSIGNED AUTO_INCREMENT;
COMMIT;

Its fast, works with innoDB, and I don't need to know the current maximum value! This way the auto increment counter will reset and it will start automatically from the maximum value exists.

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

Here is my solution, but I will not advise to do this if your column has constraints or is connected as foreign key to other tables as it would have bad effects or will not even work.

> First : drop the column

ALTER TABLE tbl_name DROP COLUMN column_id

> Second : recreate the column and set it as FIRST if you want it as the first column I assume.

ALTER TABLE tbl_access ADD COLUMN `access_id` int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST

This works well!

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

ALTER TABLE tablename AUTO_INCREMENT = 1

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

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.

You can simply truncate the table to reset the sequence

TRUNCATE TABLE TABLE_NAME

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

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.