如何在mysql中重新填充/重新创建自动增量

Question

我想知道我是否可以在mysql中重新填充自动增量值。

因为，我的记录看起来很像：

ID Name
1  POP
3  OLO
12 lku

基本上，我希望有一种方法可以将ID更新为

ID Name
1  POP
2  OLO
3  lku

在mysql中有什么方法可以做到这一点吗？

谢谢。

Answer 1

摆弄你的主键不是最好的做法--最好让你的DB自己处理它。如果在UPDATE和ALTER之间添加了另一个记录，则可能会出现问题。因此，您必须对表执行LOCK操作，这可能会挂起其他查询，并在繁忙的生产服务器上达到负载峰值。

LOCK TABLES table WRITE
UPDATE table SET id=3 WHERE id=12;
ALTER TABLE table AUTO_INCREMENT=4;
UNLOCK TABLES 

或者-对于数千行(没有外键依赖关系)：

CREATE TEMPORARY TABLE nameTemp( name varchar(128) not null )
INSERT INTO name SELECT name FROM firstTable
TRUNCATE firstTable
INSERT INTO firstTable SELECT name FROM nameTemp

后一种方法仅适用于没有外键的情况。如果这样做，您将需要一个查找表。

CREATE TEMPORARY TABLE lookup( newId INTEGER AUTO_INCREMENT, oldId INTEGER, PRIMARY KEY newId( newId ) );
INSERT INTO lookup (oldId) SELECT id FROM firstTable
[do temp table queries above]

现在，您已经有了一个包含新旧ids的lookup表，您可以使用它来更新其他表(在测试服务器上！)上的外键依赖关系。

Answer 2

更改主键是一个非常糟糕的主意，因为它会危及您的引用完整性(如果另一个表使用id而没有适当的"on change“外键怎么办？)。

如果你真的真的必须这样做，并且不关心不良的副作用：

SELECT NULL创建第二个表，该表具有相同的structure

• INSERT id new_table (id，其他字段)SELECT NULL，other field FROM
• new_table old_table;

警告：

这将破坏在这个表上有外键的所有其他表(但是如果你有这样的外键，那么你无论如何都不会这样做)。

Answer 3

你可能想试试像这样的..。

Create Temporary table MyBackup
(  ID as your autoincrement,
   OldID as Int for backlinking/retention,
   RestOfFields as their type )

insert into MyBackup
(    OldID
     RestOfFields )
select 
     ID as OldID,
     RestOfFields
  from
     YourOriginalTable
  order by
     ID  (this is your original ID)

然后，您将拥有一个具有分配了新ID的自动增量的新表，并且拥有其原始ID的完整副本。然后，您可以对其他表执行相关更新，并设置ID = ID where ID= OldID。通过保持您的插入通过原始ID排序，它将防止数字替换无序。例如:如果您的表的顺序为旧ID = 3，新ID =1旧ID = 1，新ID =3旧ID = 12，新ID =2

原来的3会变成1，然后1会变成3，12会变成2

旧ID = 1，新ID =1旧ID = 3，新ID =2旧ID = 12，新ID =3

你的3不会覆盖更高的数字，12也不会与3冲突，因为3已经降到了2。