MySQL 重新排序/重置自动增量主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/740358/
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
Reorder / reset auto increment primary key
提问by Jonathan
I have a MySQL table with an auto increment primary key. I deleted some rows in the middle of the table. Now I have, for example, something like this in the ID column: 12, 13, 14, 19, 20. I deleted the 15, 16, 17 and 18 rows.
我有一个带有自动增量主键的 MySQL 表。我删除了表格中间的一些行。例如，现在我在 ID 列中有这样的内容：12、13、14、19、20。我删除了 15、16、17 和 18 行。
I want to reassign / reset / reorder the primary key so that I have continuity, i.e. make the 19 a 15, the 20 a 16, and so on.
我想重新分配/重置/重新排序主键，以便我具有连续性，即使 19 变为 15，使 20 变为 16，依此类推。
How can I do it?
我该怎么做？
采纳答案by Tom Haigh
You could drop the primary key column and re-create it. All the ids should then be reassigned in order.
您可以删除主键列并重新创建它。然后应按顺序重新分配所有 ID。
However this is probably a bad idea in most situations. If you have other tables that have foreign keys to this table then it will definitely not work.
然而，在大多数情况下这可能是一个坏主意。如果您有其他表具有该表的外键，那么它肯定不起作用。
回答by Anshul
Even though this question seems to be quite old, will post an answer for someone who reaches in here searching.
即使这个问题似乎很老，也会为那些在这里搜索的人发布答案。
SET @count = 0;
UPDATE `users` SET `users`.`id` = @count:= @count + 1;
If the column is used as a foreign key in other tables, make sure you use ON UPDATE CASCADEinstead of the default ON UPDATE NO ACTIONfor the foreign key relationship in those tables.
如果该列在其他表中用作外键，请确保使用这些表中的外键关系ON UPDATE CASCADE而不是默认值ON UPDATE NO ACTION。
Further, in order to reset the AUTO_INCREMENTcount, you can immediately issue the following statement.
此外，为了重置AUTO_INCREMENT计数，您可以立即发出以下语句。
ALTER TABLE `users` AUTO_INCREMENT = 1;
For MySQLs it will reset the value to MAX(id) + 1.
对于 MySQL，它会将值重置为MAX(id) + 1.
回答by Ryan
To reset the IDs of my User table, I use the following SQL query. It's been said above that this will ruin any relationships you may have with any other tables.
要重置我的用户表的 ID，我使用以下 SQL 查询。上面已经说过，这会破坏您与任何其他表之间的任何关系。
ALTER TABLE `users` DROP `id`;
ALTER TABLE `users` AUTO_INCREMENT = 1;
ALTER TABLE `users` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
回答by Ryan
You may simply use this query
您可以简单地使用此查询
alter table abc auto_increment = 1;
回答by bagyei37
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 lbrutti
Or, from PhpMyAdmin, remove "AutoIncrement" flag, save, set it again and save.this resets it.
或者，从 PhpMyAdmin 中删除“AutoIncrement”标志，保存，再次设置并保存。这将重置它。
回答by Shubham Malik
SELECT * from `user` ORDER BY `user_id`;
SET @count = 0;
UPDATE `user` SET `user_id` = @count:= @count + 1;
ALTER TABLE `user_id` AUTO_INCREMENT = 1;
if you want to order by
如果你想 order by
回答by Anjani Barnwal
in phpmyadmin
在 phpmyadmin 中
note: this will work if you delete last rows not middle rows.
注意：如果您删除最后一行而不是中间行，这将起作用。
goto your table-> click on operations menu-> goto table options->change AUTO_INCREMENT to that no from where you want to start.
转到您的表格-> 单击操作菜单-> 转到表格选项-> 将 AUTO_INCREMENT 更改为您要开始的位置。
your table autoincrement start from that no.
您的表自动增量从该编号开始。
try it.
尝试一下。
回答by Rodrigo Prazim
I had the same doubts, but could not make any changes on the table, I decided doing the following having seen my ID did not exceed the maximum number setted in the variable @count:
我有同样的疑问，但无法对表进行任何更改，我决定执行以下操作，因为我的 ID 没有超过变量 @count 中设置的最大数量：
SET @count = 40000000;
UPDATE `users` SET `users`.`id` = @count:= @count + 1;
SET @count = 0;
UPDATE `users` SET `users`.`id` = @count:= @count + 1;
ALTER TABLE `users` AUTO_INCREMENT = 1;
The solution takes, but it's safe and it was necessary because my table owned foreign keys with data in another table.
该解决方案需要，但它是安全的并且是必要的，因为我的表拥有另一个表中的数据的外键。
回答by Grwww
The best choice is to alter the column and remove the auto_increment attribute. Then issue another alter statement and put auto_increment back onto the column. This will reset the count to the max+1 of the current rows and thus preserve foreign key references back to this table, from other tables in your database, or any other key usage for that column.
最好的选择是更改列并删除 auto_increment 属性。然后发出另一个alter 语句并将auto_increment 放回列中。这会将计数重置为当前行的 max+1，从而保留对该表、来自数据库中其他表或该列的任何其他键用法的外键引用。
