如何从1更新ID集? [英] How to update id set from 1?

问题描述

我有一个id，即主键和自动递增.是否有任何查询来更新现有的id，并使我的id从1开始，下一个ID为2，依此类推.

I have an id i.e primary key and auto increment. Is there any query to update my existing id and make my id start from 1 and next id 2 and so on..

例如

id  name
3   ABC
5   XYZ
9   PQR

注意::id已经是主要文件，并且会自动递增，因此我不想截断我的ID.

NOTE: id is already primary and auto increment and I don't want truncate my id.

如果可能我想得到

id  name
1   ABC
2   XYZ
3   PQR

ALTER TABLE table AUTO_INCREMENT = 1;不是我的解决方案.

谢谢

推荐答案

是否有任何查询来更新我的现有ID，并使我的ID从1开始，下一个ID为2，依此类推

Is there any query to update my existing id and make my id start from 1 and next id 2 and so on

您可以做的是将表的内容传输到另一个表.重置自动增量计数器，将数据重新插入原始表中，但让MySQL分配主键.

What you can do is transfer the content of your table to another table. Reset the auto increment counter, insert your data back into the original table but let MySQL assign the primary key.

假设您的表名是mytable，您可以这样做:

Assuming your table name is mytable You do it like this:

CREATE TABLE mytable_tmp select * from mytable;
TRUNCATE TABLE mytable;
ALTER TABLE mytable AUTO_INCREMENT = 1;
INSERT INTO mytable(name) SELECT name FROM mytable_tmp ORDER BY id;
DROP TABLE mytable_tmp;

这篇关于如何从1更新ID集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！