主页 / dba / 问题 / 64069
Accepted
Eilidh
Asked: 2014-04-29 05:12:05 +0800 CST

将数据移动到新表时如何保留从 PRIMARY KEY 生成的 ID？

  • 772

我有一个大表（约 400 万行，约 100 个字段），我需要按类型将其拆分为几个较小的表，这样它就不会随着它的增长而变得难以管理。

每个条目都有一个 ID，它是一个自动生成的主键。将数据移动到新表时需要保留这些键。此外，还需要生成新的唯一密钥；这些将需要在所有较小的表中都是唯一的，而不仅仅是在每个表本身内。

我在想一种方法是在将数据传输到新表时将 ID 传输到新字段（或非自动生成的主键字段？）。传输数据后，我可以剥离原始表，使其只是一个主键，因此它可以继续为任何其他数据自动生成键 - 即在将数据插入其中一个新表之前，从原表。

然而，这似乎相当笨拙！

它还留下了一个问题，如果用户在不知道数据是什么类型的情况下引用一个 ID（即它将在​​哪个表中找到），他们如何“定向”到正确的表？

我确定必须有更好的方法来做到这一点？

mysql

2 个回答

  1. Best Answer

    当你有一个带有 auto_increment的主键时，只有当你插入一个 NULL 值时它才会生成一个新的 ID。如果您在 中设置ID =4INSERT，则 ID 将为 4，因此您不会在“移动”操作期间丢失您的 ID。

    我们没有 Oracle 数据库中的“SEQUENCE”概念，因此您的“全局 ID”问题并不容易解决。

    也许你可以尝试这样的事情（但它只会增加 400 万行表的复杂性）

    创建一个用于生成“全局 ID”的表，其中一个 int 字段为 auto_incremented：

    CREATE TABLE test.sequence_table (next_id int primary key auto_increment);

    当您想在子表中插入新行时：

    SELECT解决方案1：在information_schema

    BEGIN; -- Start a new Transaction to ensure consistency

INSERT INTO test.sequence_table values (NULL); -- Generate a new ID

SELECT @next_ID:=(auto_increment - 1) FROM information_schema.tables WHERE table_schema="test" AND table_name="sequence_table"; -- Here I use a MySQL Variable but you can store it in PHP or whatever

INSERT INTO child_table values (null, @next_ID, "Max", "SQL"); -- Use your variable

COMMIT; -- Wonderfull :)

    在 ypercube 评论后编辑：

    解决方案 2：使用LAST_INSERT_ID()

    BEGIN; -- Start a new Transaction to ensure consistency

INSERT INTO test.sequence_table values (NULL); -- Generate a new ID

SELECT @next_ID:=LAST_INSERT_ID(); -- Use of the MySQL function LAST_INSERT_ID()

INSERT INTO child_table values (null, @next_ID, "Max", "SQL"); -- Use your variable

COMMIT; -- Wonderfull :)
    • 4

  2. 如果您不使用对应用程序透明的分区，则需要：

    1. 创建 2 个表以“按类型”保存值
    2. 再创建一张表来保存唯一的自动生成的键

    3. 为将隐藏上述所有内容的应用程序创建一个视图

      好消息是您可以在表之间使用内连接，因为您总是在一个或另一个中拥有记录

    4. 我相信 mysql 仍然缺少视图上的 INSTEAD OF 触发器，因此对于 DML 操作，您将需要存储过程。

    我认为不清楚，您将从 p2 在表中创建一个新键，并使用该值将记录从 p1 插入表中。

    • 1

相关问题