如何在 MySQL 中重置 AUTO_INCREMENT？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8923114/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to reset AUTO_INCREMENT in MySQL?
提问by homerun
How can I resetthe AUTO_INCREMENTof a field?
I want it to start counting from 1again.
我怎样才能重新在AUTO_INCREMENT一个领域？
我希望它重新开始计数1。
回答by Niels
You can reset the counter with:
您可以使用以下方法重置计数器：
ALTER TABLE tablename AUTO_INCREMENT = 1
For InnoDB you cannot set the auto_incrementvalue lower or equal to the highest current index. (quote from ViralPatel):
对于 InnoDB，您不能将该auto_increment值设置为低于或等于当前最高索引。（引自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.
请注意，您不能将计数器重置为小于或等于任何已使用值的值。对于 MyISAM，如果该值小于或等于当前 AUTO_INCREMENT 列中的最大值，则将该值重置为当前最大值加一。对于 InnoDB，如果该值小于列中当前的最大值，则不会发生错误并且不会更改当前序列值。
See How to Reset an MySQL AutoIncrement using a MAX value from another table?on how to dynamically get an acceptable value.
请参阅如何使用另一个表中的 MAX 值重置 MySQL AutoIncrement？关于如何动态获得可接受的值。
回答by boobiq
ALTER TABLE tablename AUTO_INCREMENT = 1
回答by nghiepit
SET @num := 0;
UPDATE your_table SET id = @num := (@num+1);
ALTER TABLE your_table AUTO_INCREMENT =1;
I think this will do it
我认为这会做到
回答by fyr
Simply like this:
就像这样：
ALTER TABLE tablename AUTO_INCREMENT = value;
reference: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
回答by Miles M.
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.
phpmyadmin 在“操作”选项卡下有一个非常简单的方法，您可以在表选项中设置自动递增到您想要的数字。
回答by trust_words
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.
它的速度很快，适用于 innoDB，而且我不需要知道当前的最大值！这样自动递增计数器将重置，它会自动从存在的最大值开始。
回答by lreeder
The highest rated answers to this question all recommend "ALTER yourtable AUTO_INCREMENT= value". However, this only works when valuein the alter is greater than the current max value of the autoincrement column. According to the MySQL 8 documentation:
这个问题评分最高的答案都推荐“ALTER yourtable AUTO_INCREMENT= value”。但是，这仅value在更改大于自动增量列的当前最大值时才有效。 根据 MySQL 8 文档：
You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and 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 AUTO_INCREMENT column value plus one.
您不能将计数器重置为小于或等于当前使用的值的值。对于 InnoDB 和 MyISAM，如果该值小于或等于当前 AUTO_INCREMENT 列中的最大值，则该值将重置为当前最大 AUTO_INCREMENT 列值加一。
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.
本质上，您只能更改 AUTO_INCREMENT 以增加自动增量列的值，而不是将其重置为 1，正如 OP 在问题的第二部分中所要求的那样。对于实际上允许您从当前最大值向下设置 AUTO_INCREMENT 的选项，请查看Reorder / reset auto increment primary key。
回答by SeanN
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:
添加更新，因为功能在 MySQL 5.6 中发生了变化。从 MySQL 5.6 开始，您可以在 InnoDB 中使用简单的 ALTER TABLE：
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
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.
我的测试还表明表没有被复制，值只是改变了。
回答by Naz
There are good options given in How To Reset MySQL Autoincrement Column
How To Reset MySQL Autoincrement Column 中给出了很好的选择
Note that ALTER TABLE tablename AUTO_INCREMENT = value;does notwork for InnoDB
请注意，ALTER TABLE tablename AUTO_INCREMENT = value;并没有对InnoDB的工作
回答by Alin Razvan
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 .
我在我的一些脚本中使用了这个，id 字段被删除，然后用以前的设置重新添加，数据库表中的所有现有字段都用新的自动增量值填充，这也适用于 InnoDB。
Note that all the fields within the table will be recounted and will have other ids !!!.
请注意，表中的所有字段都将被重新计算并具有其他 ID !!!。
