PAYATHON 2026

MySQL 旧 database 迁移到新 database 时如何处理缺失 IDs

支付阿杰

结论

已经删除的 Primary key 不需要补齐，也不要认为 ID 必须连续。5,7,11,12 这类缺失值只是删除历史记录后留下的空号，不会影响 MySQL 或迁移结果。

建议这样处理：

  1. 按外键依赖顺序迁移。先迁移 Design，再迁移 OrderStatus、PaymentStatus，最后迁移 Comments、Conversation、Logs 和 Files。
  2. 可以按 Design 分批迁移，以控制单次事务的规模，但应避免为每个订单执行大量逐行查询。
  3. 如果新库为空，旧 ID 也不会发生冲突，优先保留原 Primary key。这样，现有的 comment_id、design_id 等关联可以直接迁移。
  4. 如果旧 ID 无法保留，就必须建立“旧 ID → 新 ID”映射表，并根据映射结果重建所有 Foreign key。
  5. 只迁移实际存在的记录。对于指向已删除记录的孤立引用，应先查明情况并确定处理规则，不要创建占位数据。

为什么不需要补齐缺失 ID

Primary key 用于唯一标识记录，不表示记录数量，也不要求体现顺序。下面这样的序列完全合法：

1, 2, 3, 6, 8, 10, 13

记录被删除后，AUTO_INCREMENT 通常不会自动复用原来的 ID。迁移时也不应通过 for loop 从 1 遍历到最大 ID，更不能为了填补空号而创建假记录。

直接读取实际存在的数据即可：

SELECT *
FROM old_db.Design_Comments
ORDER BY id;

不存在的 ID 不会出现在查询结果中，迁移程序无需额外处理。

优先方案：保留旧 Primary key

如果新数据库为空，且旧 ID 不会与新数据冲突，保留旧 Primary key 是最简单、风险也最低的方案。

例如：

INSERT INTO new_db.Design (
    id,
    order_no,
    client_id,
    created_at
)
SELECT
    id,
    order_no,
    client_id,
    created_at
FROM old_db.Design;

然后迁移 Comments：

INSERT INTO new_db.Design_Comments (
    id,
    design_id,
    order_no,
    comment_text,
    created_at
)
SELECT
    c.id,
    c.design_id,
    d.order_no,
    c.comment_text,
    c.created_at
FROM old_db.Design_comments AS c
JOIN old_db.Design AS d
    ON d.id = c.design_id;

最后迁移与 Comment 关联的 Files：

INSERT INTO new_db.Design_files (
    id,
    design_id,
    comment_id,
    order_no,
    file_path,
    uploaded_by,
    created_at
)
SELECT
    f.id,
    f.design_id,
    f.comment_id,
    d.order_no,
    f.file_path,
    f.uploaded_by,
    f.created_at
FROM old_db.Design_files AS f
JOIN old_db.Design AS d
    ON d.id = f.design_id
LEFT JOIN old_db.Design_comments AS c
    ON c.id = f.comment_id
WHERE f.comment_id IS NULL
   OR c.id IS NOT NULL;

只要 Design_Comments.id 没有变化，Design_files.comment_id 就可以原样保留。ID 中间缺少 5、7 或 11，不会影响表之间的关联。

迁移完成后，可以让 MySQL 从现有最大值之后继续生成新 ID：

SELECT MAX(id) + 1 AS next_id
FROM new_db.Design_Comments;

显式插入较大的 AUTO_INCREMENT 值后，MySQL 通常会自动调整后续序号。不过，仍应在目标环境中验证。必要时可以明确设置：

ALTER TABLE new_db.Design_Comments AUTO_INCREMENT = 10001;

这里的 10001 只是示例，实际值应根据 MAX(id) + 1 计算，不要直接使用固定值。

无法保留旧 ID 时：使用映射表

如果新库中已经有数据，或者新旧系统的 ID 可能冲突，就不要强行复用旧 ID。此时需要记录每条数据的新旧 ID 对应关系。

可以为迁移创建临时映射表：

CREATE TABLE migration_design_map (
    old_id BIGINT PRIMARY KEY,
    new_id BIGINT NOT NULL UNIQUE
);

CREATE TABLE migration_comment_map (
    old_id BIGINT PRIMARY KEY,
    new_id BIGINT NOT NULL UNIQUE
);

处理流程如下：

  1. 将一条旧 Design 插入新库，由新库生成 new_id。
  2. 把 old_design_id 和 new_design_id 写入 migration_design_map。
  3. 插入 Comment 时，通过映射表取得新的 design_id。
  4. 把 Comment 的新旧 ID 写入 migration_comment_map。
  5. 插入 File 时，通过这两个映射表取得新的 design_id 和 comment_id。

对应的 SQL 逻辑如下：

INSERT INTO new_db.Design_Comments (
    design_id,
    order_no,
    comment_text
)
SELECT
    dm.new_id,
    c.order_no,
    c.comment_text
FROM old_db.Design_comments AS c
JOIN migration_design_map AS dm
    ON dm.old_id = c.design_id;

迁移 Files 时，再对 Comment ID 进行映射：

INSERT INTO new_db.Design_files (
    design_id,
    comment_id,
    order_no,
    file_path
)
SELECT
    dm.new_id,
    cm.new_id,
    f.order_no,
    f.file_path
FROM old_db.Design_files AS f
JOIN migration_design_map AS dm
    ON dm.old_id = f.design_id
LEFT JOIN migration_comment_map AS cm
    ON cm.old_id = f.comment_id
WHERE f.comment_id IS NULL
   OR cm.new_id IS NOT NULL;

不能只修改 Comments 的 ID，却继续将旧 comment_id 写入 Files，否则表之间的关联会发生错乱。

应该按表迁移还是按 Design order 迁移

两种方式都可以。无论采用哪一种，都必须遵守依赖顺序。

建议按照下面的依赖关系安排迁移：

Design
├── Design_OrderStatus
├── Design_PaymentStatus
├── Design_Comments
│   └── Design_files（当文件属于 Comment）
├── Design_personalConversation
├── Design_Logs
└── Design_files（当文件直接属于 Design）

如果数据量不大，而且目标数据库为空，可以按表迁移：

Design
→ OrderStatus
→ PaymentStatus
→ Comments
→ Conversation
→ Logs
→ Files

这种方式适合使用 INSERT ... SELECT 批量处理，通常比逐订单、逐记录迁移更快。

如果数据量很大，或者希望某个订单迁移失败时可以单独回滚，可以按 Design 分批处理。每个批次都应放在事务中：

START TRANSACTION;

-- 迁移一个或一批 Design
-- 迁移对应的 OrderStatus 和 PaymentStatus
-- 迁移对应的 Comments、Conversation、Logs
-- 最后迁移依赖 Comments 的 Files

COMMIT;

发生错误时执行：

ROLLBACK;

实际项目中，每批可以处理几百或几千个 Design，不必限定为一次只处理一个订单。这样既能控制事务大小，也可以减少重复查询。

迁移前先检查孤立数据

旧数据库曾删除过 Comments 和 Files，因此迁移前需要确认是否还有数据指向已经删除的记录。

查找没有对应 Design 的 Comments：

SELECT c.*
FROM old_db.Design_comments AS c
LEFT JOIN old_db.Design AS d
    ON d.id = c.design_id
WHERE d.id IS NULL;

查找指向不存在 Comment 的 Files：

SELECT f.*
FROM old_db.Design_files AS f
LEFT JOIN old_db.Design_comments AS c
    ON c.id = f.comment_id
WHERE f.comment_id IS NOT NULL
  AND c.id IS NULL;

对于这些孤立的 Files，需要根据业务含义确定一种明确的处理方式：

  • 如果文件只有在 Comment 存在时才有意义，就跳过该文件，并记录迁移错误。
  • 如果文件仍然属于 Design，可以迁移文件，同时将 comment_id 设置为 NULL。
  • 如果审计要求必须保留，可以将记录放入专门的异常表，等待人工处理。

不要为了通过 Foreign key 检查而创建虚假的 Comment。

如果允许文件脱离 Comment，但文件仍归属于 Design，可以这样处理：

INSERT INTO new_db.Design_files (
    id,
    design_id,
    comment_id,
    order_no,
    file_path
)
SELECT
    f.id,
    f.design_id,
    CASE
        WHEN c.id IS NULL THEN NULL
        ELSE f.comment_id
    END,
    f.order_no,
    f.file_path
FROM old_db.Design_files AS f
JOIN old_db.Design AS d
    ON d.id = f.design_id
LEFT JOIN old_db.Design_comments AS c
    ON c.id = f.comment_id;

这种处理方式有两个前提：新表允许 comment_id 为 NULL，并且文件本身仍有有效的 design_id。

新增 OrderStatus 和 PaymentStatus 的处理

旧数据库没有独立的状态表，因此迁移时需要根据旧 Design 中的相关字段生成记录。具体如何映射，取决于旧系统实际保存的状态值，不能根据 ID 推测。

例如：

INSERT INTO new_db.Design_OrderStatus (
    design_id,
    status,
    created_at
)
SELECT
    id,
    CASE old_status
        WHEN 'complete' THEN 'completed'
        WHEN 'cancel'   THEN 'cancelled'
        ELSE 'pending'
    END,
    created_at
FROM old_db.Design;

PaymentStatus 也应使用明确的状态映射表或 CASE 规则。对于无法确定的旧状态，最好写入 unknown 或迁移异常表，不要直接默认为“已支付”。

不要只依赖 order_no 建立关系

在所有表中加入 order_no，确实有助于查询和核对，但它不能取代正式的 Foreign key。

建议保留以下关联：

Design_Comments.design_id  -> Design.id
Design_files.design_id     -> Design.id
Design_files.comment_id    -> Design_Comments.id

同时保证 order_no 唯一：

ALTER TABLE new_db.Design
ADD CONSTRAINT uq_design_order_no UNIQUE (order_no);

子表中的 order_no 是冗余字段。如果决定保留，就必须确保它始终与对应的 Design.order_no 一致，否则可能出现 design_id 指向一个订单，而 order_no 表示另一个订单的情况。

迁移完成后的校验

至少要检查以下记录数量：

SELECT COUNT(*) FROM old_db.Design;
SELECT COUNT(*) FROM new_db.Design;

SELECT COUNT(*) FROM old_db.Design_comments;
SELECT COUNT(*) FROM new_db.Design_Comments;

SELECT COUNT(*) FROM old_db.Design_files;
SELECT COUNT(*) FROM new_db.Design_files;

如果迁移过程中跳过了孤立记录，新旧数量可能不同，因此还需要保存被跳过记录的 ID 和原因。

接着检查新库中是否存在孤立关联：

SELECT c.id
FROM new_db.Design_Comments AS c
LEFT JOIN new_db.Design AS d
    ON d.id = c.design_id
WHERE d.id IS NULL;
SELECT f.id
FROM new_db.Design_files AS f
LEFT JOIN new_db.Design_Comments AS c
    ON c.id = f.comment_id
WHERE f.comment_id IS NOT NULL
  AND c.id IS NULL;

正常情况下，这两项查询都应返回空结果。

缺失的 ID 可以忽略，但已经存在的关联不能靠猜测处理。新库为空时，保留旧 Primary key 最省事；如果存在 ID 冲突，就使用映射表。无论采用哪种方式，都应先迁移父记录，再迁移子记录，并在迁移前后检查孤立数据和记录数量。

备注：内容仅供参考。