MySQL 跨庫異構表數據導入：INSERT/REPLACE/IGNORE/ON DUPLICATE KEY 實戰選型

場景定義

源庫 A 表 users(uid, username, email, password, regdate, salt) → 目標庫 B 表 account(id, name, email, password, type, salt)。需導入 username, email, password, salt，且過濾 regdate > 1418313600。

核心前提：目標表需唯一鍵

所有「衝突處理」語法（REPLACE、IGNORE、ON DUPLICATE KEY）均要求目標表存在 PRIMARY KEY 或 UNIQUE INDEX。本例假設 account.name 有唯一索引。

四種導入策略對比

1. 純新增：INSERT INTO ... SELECT

INSERT INTO `B`.`account` (`name`, `password`, `email`, `salt`)
SELECT `username`, `password`, `email`, `salt`
FROM `A`.`users`
WHERE `regdate` > 1418313600;

• 適用：目標表確無重複數據
• 衝突行報錯中斷，需配合 IGNORE 或預清洗

2. 強制覆蓋：REPLACE INTO ... SELECT

REPLACE INTO `B`.`account` (`name`, `password`, `email`, `salt`)
SELECT `username`, `password`, `email`, `salt`
FROM `A`.`users`
WHERE `regdate` > 1418313600;

• 機制：遇唯一鍵衝突 → 刪除整行舊記錄 → 插入新記錄
• 副作用：未在 SELECT 中的列（如 type）將重置為默認值
• 慎用：數據完整性風險極高

3. 跳過衝突：INSERT IGNORE ... SELECT

INSERT IGNORE INTO `B`.`account` (`name`, `password`, `email`, `salt`)
SELECT `username`, `password`, `email`, `salt`
FROM `A`.`users`
WHERE `regdate` > 1418313600;

• 機制：遇唯一鍵衝突 → 忽略當前行，保留舊記錄不變
• 適用：歷史數據遷移，以目標表現有數據為準

4. 精準更新：INSERT ... ON DUPLICATE KEY UPDATE（推薦）

INSERT INTO `B`.`account` (`name`, `password`, `email`, `salt`)
SELECT `username`, `password`, `email`, `salt`
FROM `A`.`users`
WHERE `regdate` > 1418313600
ON DUPLICATE KEY UPDATE
  `password` = VALUES(`password`),
  `salt` = VALUES(`salt`);

• 機制：衝突時 僅更新指定列，保留 type 等其他列原值
• VALUES(col) 引用 INSERT 部分準備插入的值
• 最符合「增量同步、保留目標表擴展字段」的業務需求

決策流程圖

目標表有數據嗎？
├─ 無 → 直接 INSERT
└─ 有 → 業務允許覆蓋非導入列嗎？
    ├─ 允許 → REPLACE（慎用）
    ├─ 不允許，保留舊數據 → INSERT IGNORE
    └─ 需選擇性更新特定列 → ON DUPLICATE KEY UPDATE ✓

跨實例/跨服務器遷移方案

同一服務器跨庫可直接 SQL；跨服務器需中轉：

1. 物理遷移：源庫 mysqldump → 導入目標服務器臨時庫 → 執行上述跨庫 SQL
2. 邏輯遷移：源庫 SELECT ... INTO OUTFILE → scp 文件 → 目標庫 LOAD DATA INFILE → 執行 SQL
3. 同步工具：DataX、Canal、pt-archiver 等異構同步工具

最佳實踐總結

1. 目標表必須有業務唯一鍵（如 name）
2. 優先使用 ON DUPLICATE KEY UPDATE，顯式列出需更新列
3. 大批量導入前：SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; 導入後恢復並 COMMIT
4. 導入後驗證：行數對比、關鍵字段抽樣校驗、業務功能冒煙測試

掌握四種語法的語義差異，結合業務對「歷史數據保留」與「增量同步」的具體要求，即可安全高效完成異構表跨庫數據融合。