MySQL 表間數據複製全攻略：INSERT SELECT 實戰與不存在才插入

MySQL 表間數據複製：INSERT ... SELECT 完整指南

將一張表的數據高效複製到另一張表是常見運維與開發需求。MySQL 支援 INSERT ... SELECT 語法，可實現全量、指定列、條件過濾、去重等多種場景。

一、基礎語法

1. 結構完全一致，全量複製

INSERT INTO target_table SELECT * FROM source_table;

要求：兩表列數、順序、類型完全一致。

2. 指定列複製（推薦，解耦列順序）

INSERT INTO target_table (col1, col2, col3)
SELECT col1, col2, col3 FROM source_table;

注意： 目標列與來源列一一對應、順序一致。

3. 條件過濾

INSERT INTO target_table (id, name, status)
SELECT id, name, 1 FROM source_table WHERE create_time > '2023-01-01';

二、進階場景

1. 避免重複：僅插入目標表不存在的記錄

-- 方式 A：LEFT JOIN + IS NULL
INSERT INTO target_table (id, name)
SELECT s.id, s.name
FROM source_table s
LEFT JOIN target_table t ON s.id = t.id
WHERE t.id IS NULL;

-- 方式 B：NOT EXISTS（通常效能更好）
INSERT INTO target_table (id, name)
SELECT s.id, s.name
FROM source_table s
WHERE NOT EXISTS (SELECT 1 FROM target_table t WHERE t.id = s.id);

-- 方式 C：INSERT IGNORE（需目標表有唯一鍵）
INSERT IGNORE INTO target_table (id, name)
SELECT id, name FROM source_table;

2. 異構表結構映射

INSERT INTO new_articles (article_id, title, content, author_id, created_at)
SELECT id, subject, body, user_id, post_date FROM old_articles;

3. 複製同時轉換數據

INSERT INTO user_summary (user_id, name, level, total_orders)
SELECT u.id, u.username,
       CASE WHEN u.exp > 1000 THEN 'VIP' ELSE 'NORMAL' END,
       COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.exp;

三、大數據量優化技巧

1. 分批提交：避免單次事務過大導致鎖升級、Undo Log 膨脹。

   -- 存儲過程分批示例
   DELIMITER //
   CREATE PROCEDURE batch_copy()
   BEGIN
     DECLARE done INT DEFAULT 0;
     DECLARE v_max_id BIGINT DEFAULT 0;
     SELECT MAX(id) INTO v_max_id FROM source_table;
     WHILE done = 0 DO
       INSERT INTO target_table SELECT * FROM source_table
       WHERE id > v_max_id - 10000 AND id <= v_max_id;
       IF ROW_COUNT() = 0 THEN SET done = 1; END IF;
       SET v_max_id = v_max_id - 10000;
       COMMIT;
     END WHILE;
   END//
   DELIMITER ;
   

2. 暫停索引/約束（MyISAM/InnoDB 導入前）：

   ALTER TABLE target_table DISABLE KEYS;  -- MyISAM
   SET FOREIGN_KEY_CHECKS = 0;
   SET UNIQUE_CHECKS = 0;
   SET AUTOCOMMIT = 0;
   -- 導入後恢復
   

3. 並行導入：按主鍵範圍分片，多線程並行 INSERT ... SELECT。

四、常見錯誤與避坑