MySQL 跨庫表數據遷移：INSERT SELECT 實戰與常見錯誤避坑

跨庫/跨表數據遷移：INSERT SELECT 模式最佳實踐

同實例跨庫遷移，INSERT ... SELECT 是最高效、原子性最強的方式。

一、正確語法範例

-- 目標表指定列，源表 SELECT 對應列（含常量）
INSERT INTO `game_2015newterm_user` 
    (`uid`, `lq`, `xm`, `ct`, `by`, `lj`, `xmlbq`, `ctlbq`, `bylbq`, `ljlbq`, `tdate`)
SELECT 
    uid, '0', '3', '0', '0', '0', '0', '0', '0', '0', '2015-03-16'
FROM `game_2015school_user`;

• 目標表列名顯式指定，防止結構變更導致錯位
• SELECT 列順序、類型與目標表嚴格對應
• 常量值直接在 SELECT 中書寫，避免應用層拼接
• 大數據量分批次：WHERE uid BETWEEN 1 AND 10000 循環遷移

二、錯誤寫法剖析

-- 錯誤 1：INSERT SELECT 不能配合 VALUES
INSERT INTO `event_2125`.`game_2015newterm_user` (...) 
VALUES ((SELECT uid FROM game_2015school_user WHERE uid=608035003), '0', ...);
-- ERROR: Subquery returns more than 1 row / VALUES 與 SELECT 不能混用

-- 錯誤 2：子查詢返回多行用於單值位置
INSERT INTO target (col) VALUES ((SELECT id FROM source));  -- 若 source 多行報錯

三、進階遷移場景

1. 去重遷移

INSERT INTO target (id, name)
SELECT DISTINCT id, name FROM source
ON DUPLICATE KEY UPDATE name=VALUES(name);

2. 分區表遷移

INSERT INTO target_partitioned
SELECT * FROM source_non_partitioned;
-- MySQL 自動按分區鍵路由，無需指定分區

3. 大表分批遷移腳本模板

#!/bin/bash
BATCH=10000
MAX_ID=$(mysql -u root -p -N -e "SELECT MAX(uid) FROM game_2015school_user;")
for ((start=1; start<=MAX_ID; start+=BATCH)); do
    end=$((start+BATCH-1))
    mysql -u root -p -e "
        INSERT INTO game_2015newterm_user (uid, lq, xm, ct, by, lj, xmlbq, ctlbq, bylbq, ljlbq, tdate)
        SELECT uid, '0', '3', '0', '0', '0', '0', '0', '0', '0', '2015-03-16'
        FROM game_2015school_user
        WHERE uid BETWEEN ${start} AND ${end};
    "
    echo "Batch ${start}-${end} done"
done

四、跨實例遷移方案

• mysqldump + 管道：mysqldump -h src_host db tbl | mysql -h dst_host db
• Percona XtraBackup：物理備份恢復到新實例，再 INSERT SELECT 合併
• CDC 工具：Canal、Debezium、MaxWell 實時同步

核心原則：顯式列名、類型匹配、分批提交、驗證行數、備份回滾方案。遷移前必做 SELECT COUNT(*) 基線，遷移後核對。