如何填补自增字段的“洞”？

我读过一些关于此的文章，但没有一篇涉及这个问题。

我想这是不可能的，但我还是会问。

我有一个包含 50.000 多个寄存器的表。这是一个旧表，其中发生了各种插入/删除操作。

也就是说，大约 300 个寄存器中存在各种“漏洞”。即：...、1340、1341、1660、1661、1662、...

问题是。有没有一种简单/容易的方法来让新刀片填补这些“洞”？

我同意@Aaron Digulla 和@Shane N 的观点。这些差距毫无意义。如果他们DO这意味着数据库设计有缺陷。时期。

话虽这么说，如果你绝对需要填补这些漏洞，AND如果您至少运行 MySQL 3.23，则可以利用临时表创建一组新的 ID。这里的想法是，您将按顺序选择所有当前的 ID 到临时表中，如下所示：

CREATE TEMPORARY TABLE NewIDs
(
    NewID INT UNSIGNED AUTO INCREMENT,
    OldID INT UNSIGNED
)

INSERT INTO NewIDs (OldId)
SELECT
    Id
FROM
    OldTable
ORDER BY
    Id ASC

这将为您提供一个将旧 Id 映射到全新 Id 的表，由于 NewId 列的自动增量属性，该表本质上是连续的。

完成此操作后，您需要更新对“OldTable”中的 Id 的任何其他引用及其使用的任何外键。为此，您可能需要删除拥有的所有外键约束，将表中的所有引用从 OldId 更新为 NewId，然后重新建立外键约束。

但是，我认为你不应该这样做ANY对此，只需了解您的 Id 字段存在的唯一目的是引用记录，并且应该NOT有任何特定的相关性。

更新：添加更新 Id 的示例

例如：

假设您有以下 2 个表模式：

CREATE TABLE Parent
(
    ParentId INT UNSIGNED AUTO INCREMENT,
    Value INT UNSIGNED,
    PRIMARY KEY (ParentId)
)

CREATE TABLE Child
(
    ChildId INT UNSIGNED AUTO INCREMENT,
    ParentId INT UNSIGNED,
    PRIMARY KEY(ChildId),
    FOREIGN KEY(ParentId) REFERENCES Parent(ParentId)
)

现在，间隙出现在您的父表中。

为了更新 Parent 和 Child 中的值，首先使用映射创建一个临时表：

CREATE TEMPORARY TABLE NewIDs
(
    Id INT UNSIGNED AUTO INCREMENT,
    ParentID INT UNSIGNED
)

INSERT INTO NewIDs (ParentId)
SELECT
    ParentId
FROM
    Parent
ORDER BY
    ParentId ASC

接下来，我们需要告诉 MySQL 忽略外键约束，以便我们可以正确更新我们的值。我们将使用这个语法：

SET foreign_key_checks = 0;

这会导致 MySQL 在更新值时忽略外键检查，但它仍然会强制使用正确的值类型（请参阅MySQL 参考了解详情）。

接下来，我们需要使用新值更新父表和子表。为此，我们将使用以下 UPDATE 语句：

UPDATE
    Parent,
    Child,
    NewIds
SET
    Parent.ParentId = NewIds.Id,
    Child.ParentId = NewIds.Id
WHERE
    Parent.ParentId = NewIds.ParentId AND
    Child.ParentId = NewIds.ParentId

现在，我们已将所有 ParentId 值正确更新为临时表中新的有序 Id。完成后，我们可以重新进行外键检查以保持引用完整性：

SET foreign_key_checks = 1;

最后，我们将删除临时表来清理资源：

DROP TABLE NewIds

就是这样。