MySQL 舊表去重遷移至新表並重新生成主鍵 ID 單條 SQL 實現

MySQL 單條 SQL 實現：舊表去重遷移新表並自動生成遞增主鍵

資料治理常見需求：將舊表 (含臟資料、無主鍵或主鍵不連續) 依業務鍵去重，遷移至新結構表並重新生成連續自增 ID。本文提供單條 INSERT ... SELECT 完美解決，無需臨時表、程式迴圈。

業務場景

• 舊表 B：id, n1, n2, n3, n4, n5... 含重複資料、主鍵雜亂
• 新表 A：id (PK AI), n1, n2, n3 空表，需去重後資料
• 去重鍵：(n1, n2, n3) 組合唯一

單條 SQL 解決方案

INSERT INTO A (id, n1, n2, n3)
SELECT @row := @row + 1 AS id, t1.n1, t1.n2, t1.n3
FROM (
    SELECT DISTINCT n1, n2, n3 FROM B
) t1
CROSS JOIN (SELECT @row := 0) t2;

關鍵技術點解析

進階變體：指定排序後再編號

INSERT INTO A (id, n1, n2, n3)
SELECT @row := @row + 1, t1.n1, t1.n2, t1.n3
FROM (
    SELECT DISTINCT n1, n2, n3 FROM B ORDER BY n1, n2
) t1
CROSS JOIN (SELECT @row := 0) t2;

MySQL 8.0+ 現代寫法 (CTE + ROW_NUMBER)

WITH dedup AS (
    SELECT DISTINCT n1, n2, n3 FROM B
),
numbered AS (
    SELECT ROW_NUMBER() OVER (ORDER BY n1, n2) AS id, n1, n2, n3 FROM dedup
)
INSERT INTO A (id, n1, n2, n3)
SELECT id, n1, n2, n3 FROM numbered;

優勢：標準 SQL、可讀性高、支援複雜排序/分組編號、無用戶變數副作用。

大表效能優化建議

1. 舊表 (n1,n2,n3) 建立**複合索引**加速 DISTINCT 掃描
2. 分批遷移：WHERE n1 BETWEEN ... 分範圍執行多條 SQL
3. 關閉 Binlog (會話級) 減少主從延遲：SET SESSION sql_log_bin = 0;
4. 使用 LOAD DATA 先導出 CSV 再匯入 (最快)

驗證腳本

-- 行數一致性
SELECT COUNT(*) FROM A;
SELECT COUNT(DISTINCT n1,n2,n3) FROM B;

-- 主鍵連續性
SELECT MAX(id) - COUNT(*) + 1 AS gap_count FROM A; -- 應為 1

-- 業務鍵唯一性
SELECT n1,n2,n3, COUNT(*) FROM A GROUP BY n1,n2,n3 HAVING COUNT(*) > 1;

1. 上一篇文章： MySQL InnoDB 插入意向鎖機制深度解析：為何需要、如何避免幻讀與並發衝突
2. 下一篇文章： SQL 注入之基於錯誤回顯攻擊完整手冊：原理、Payload 與實戰演練

MySQL 綜合教學

MySQL source 指令匯入大型 SQL 檔案完整教學：參數調優與實戰範例

前言 使用 source 指令在 MySQL 客戶端直接匯入

MySQL 8.0 函數索引：函數列/表達式直接建索引，免去虛擬欄位繞彎

痛點回顧 5.7 若要對 LOWER(email)、YEAR

MySQL 批量插入、更新與刪除操作詳解：INSERT、UPDATE、DELETE 語法與實戰

MySQL 批量插入、更新與刪除操作詳解 在資料庫開發中，I

MySQL 插入/更新/刪除操作優化實戰指南

MySQL 插入/更新/刪除操作優化實戰指南 一、高效插入