MySQL 跨資料庫複製表資料完整指南 (同一實例)

同庫同結構表複製

-- 完整複製
INSERT INTO table1 SELECT * FROM table2;

-- 去重複製
INSERT INTO table1 SELECT DISTINCT * FROM table2;

-- 前 N 筆
INSERT INTO table1 SELECT * FROM table2 LIMIT 5;

跨庫同結構表複製

INSERT INTO db1.table1 SELECT * FROM db2.table2;
INSERT INTO db1.table1 SELECT DISTINCT * FROM db2.table2;
INSERT INTO db1.table1 SELECT * FROM db2.table2 LIMIT 5;

結構不同或部分欄位複製

方法：建立中間表轉換

-- 1. 建立目標結構中間表
CREATE TABLE dn_user_temp LIKE dn_user;
ALTER TABLE dn_user_temp ADD COLUMN new_col INT;

-- 2. 來源資料導入中間表 (欄位對應)
INSERT INTO dn_user_temp (id, name, email, new_col)
SELECT id, name, email, 0 FROM dn_user2;

-- 3. 中間表寫入目標表
INSERT INTO dn_user (id, name, email, new_col)
SELECT id, name, email, new_col FROM dn_user_temp;

MySQL 快速複製表結構與資料

-- 一鍵複製結構+資料
CREATE TABLE mytbl_new LIKE production.mytbl;
INSERT INTO mytbl_new SELECT * FROM production.mytbl;

production. 為來源資料庫前綴，若在同庫可省略。

常見錯誤

SELECT * INTO desTable FROM sourceTable — MySQL 不支援 (SQL Server/PostgreSQL 才有)
正確：INSERT INTO desTable SELECT * FROM sourceTable (需先建表)

大表複製優化

• 分批次：INSERT INTO target SELECT * FROM source WHERE id BETWEEN 1 AND 10000
• 關閉索引/外鍵/唯一檢查加速：SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0;
• 使用 pt-archiver 或 gh-ost 線上遷移無鎖