如何更改 MySQL 中的自动增量计数器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8161788/
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 do I change the Auto Increment counter in MySQL?
提问by Chris Olson
I have an ID field that is my primary key and is just an int field.
我有一个 ID 字段，它是我的主键，只是一个 int 字段。
I have less than 300rows but now every time someone signs up that ID auto inc is inputted really high like 11800089, 11800090, etc....Is there a way to get that to come back down so it can follow the order (310,311,312).
我有不到300行，但现在每次有人注册时，ID auto inc 的输入都非常高，例如11800089、11800090 等......有没有办法让它回来以便它可以遵循顺序（310,311,312 )。
Thanks!
谢谢！
回答by Brad
ALTER TABLE table_name AUTO_INCREMENT=310;
Beware though, you don't want to repeat an ID. If the numbers are that high, they got that way somehow. Be very sure you don't have associated data with the lower ID numbers.
但请注意，您不想重复 ID。如果数字那么高，他们以某种方式得到了这种方式。请确保您没有与较低 ID 号相关联的数据。
https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html
https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html
回答by ctrl_freak
There may be a quicker way, but this is how I would do it to be sure I am recreating the IDs;
可能有一种更快的方法，但我会这样做以确保我正在重新创建 ID；
If you are using MySQL or some other SQL server, you will need to:
如果您使用 MySQL 或其他一些 SQL 服务器，您将需要：
- Backup your database
- Drop the id column
- Export the data
TRUNCATEor 'Empty' the table- Recreate the
idcolumn asauto_increment - Reimport the data
- 备份你的数据库
- 删除 id 列
- 导出数据
TRUNCATE或“清空”表格- 将
id列重新创建为auto_increment - 重新导入数据
This will destroy the IDs of the existing rows, so if these are important, it is not a viable option.
这将破坏现有行的 ID，因此如果这些很重要，则它不是一个可行的选择。
回答by Kristijan Iliev
The auto increment counter for a table can be (re)set two ways:
可以通过两种方式（重新）设置表的自动递增计数器：
By executing a query, like others already explained:
ALTER TABLE <table_name> AUTO_INCREMENT=<table_id>;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 tablefrom the context menu. On the bottom you can see all the available options for altering a table. ChooseOptionsand you will get this form:
Then just set the desired value in the field
Auto incrementas shown in the image. This will basically execute the query shown in the first option.
通过执行查询，就像其他人已经解释过的那样：
ALTER TABLE <table_name> AUTO_INCREMENT=<table_id>;使用 Workbench 或其他可视化数据库设计工具。我将在 Workbench 中展示它是如何完成的 - 但它在其他工具中也不应该有太大不同。通过右键单击所需的表并
Alter table从上下文菜单中进行选择。在底部，您可以看到用于更改表格的所有可用选项。选择Options，你会得到这个表格：然后只需在字段中设置所需的值，
Auto increment如图所示。这将基本上执行第一个选项中显示的查询。
回答by Adrian Cornish
Guessing that you are using mysql because you are using PHP. You can reset the auto_increment with a statement like
猜测您使用的是mysql，因为您使用的是PHP。您可以使用类似的语句重置 auto_increment
alter table mytable autoincrement=301;
Be careful though - because things will break when the auto inc value overlaps
不过要小心 - 因为当 auto inc 值重叠时，事情会崩溃
回答by Harald Wirth
I was playing around on a similar problem and found this solution:
我正在解决类似的问题并找到了这个解决方案：
SET @newID=0;
UPDATE `test` SET ID=(@newID:=@newID+1) ORDER BY ID;
SET @c = (SELECT COUNT(ID) FROM `test`);
SET @s = CONCAT("ALTER TABLE `test` AUTO_INCREMENT = ",@c);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
I hope that helps someone in a similar situation!
我希望能帮助处于类似情况的人！
回答by marspzb
I believe that mysql does a select max on the id and puts the next. Try updating the ids of your table to the desired sequence. The problem you will have is if they're linked you should put a Cascade on the update on the fk. A query that comes to my mind is:
我相信 mysql 在 id 上做了一个 select max 并放置下一个。尝试将表的 ID 更新为所需的序列。您将遇到的问题是，如果它们已链接，您应该在 fk 的更新上放置一个级联。我想到的一个问题是：
UPDATE Table SET id=(SELECT max(id)+1 FROM TAble WHERE id<700)
700 something less than the 11800090 you have and near to the 300 WHERE id>0;
700 比您拥有的 11800090 少并且接近 300 WHERE id>0；
I believe that mysql complaints if you don't put a where
我相信如果你不放一个地方，mysql会抱怨
