mysql - 自增字段 "holes"如何填写？

Question

我已经阅读了一些关于此的帖子，但没有一篇涉及此问题。

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

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

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

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

Answer 1

我同意@Aaron Digulla 和@Shane N 的观点。这些差距毫无意义。如果他们确实有意义，那就是有缺陷的数据库设计。期间。

话虽如此，如果您绝对需要填补这些漏洞，并且您至少运行 MySQL 3.23，您可以利用 TEMPORARY TABLE 创建一组新的 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

由于 NewId 列的 AUTO INCREMENT 属性，这将为您提供一个将旧 Id 映射到本质上将是顺序的全新 Id 的表。

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

但是，我认为您不应该这样做任何，只需了解您的 Id 字段的存在仅用于引用记录，并且应该不> 有任何特定的相关性。

更新:添加更新 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 reference)。

接下来，我们需要使用新值更新父表和子表。我们将为此使用以下 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

就是这样。