方法一:CREATE TABLE ... LIKE / SELECT(DDL 層面,最快)
1. 複製結構 + 資料(一步到位)
CREATE TABLE new_table SELECT * FROM old_table;
- 新表自動獲得舊表欄位定義、資料
- 不複製:索引、主鍵、外鍵、觸發器、AUTO_INCREMENT 值、DEFAULT 值、COMMENT
2. 僅複製結構(無資料)
-- 方法 A:WHERE 條件永假
CREATE TABLE new_table SELECT * FROM old_table WHERE 1<>1;
-- 方法 B:LIMIT 0(推薦,語意清晰)
CREATE TABLE new_table SELECT * FROM old_table LIMIT 0;
-- 方法 C:LIKE 子句(最標準,保留索引/約束/屬性)
CREATE TABLE new_table LIKE old_table;
3. 複製指定欄位/條件資料
CREATE TABLE new_table SELECT col1, col2 FROM old_table WHERE id > 100;
4. 使用 SHOW CREATE TABLE 手動重建(完全可控)
SHOW CREATE TABLE old_table\G
-- 複製輸出 SQL,修改表名、AUTO_INCREMENT 起始值、ENGINE/CHARSET 後執行
方法二:INSERT INTO ... SELECT(DML 層面,目標表已存在)
1. 結構相同:全量複製
INSERT INTO new_table SELECT * FROM old_table;
2. 結構不同:欄位映射複製
INSERT INTO new_table (col_a, col_b, col_c)
SELECT col_x, col_y, col_z FROM old_table;
3. 條件複製
INSERT INTO new_table SELECT * FROM old_table WHERE create_time > '2024-01-01';
方法三:CREATE TEMPORARY TABLE(會話級臨時表,自動清理)
CREATE TEMPORARY TABLE tmp_table SELECT * FROM old_table WHERE ...;
-- 當前連線關閉自動銷毀,不佔用永久空間,適合中間計算
進階場景:跨資料庫/跨伺服器複製
-- 跨庫同實例
INSERT INTO db2.new_table SELECT * FROM db1.old_table;
-- 跨伺服器(需 FEDERATED 引擎或雙向連線)
-- 推薦:mysqldump 導出 + 導入,或使用 pt-archiver / CDC 工具
注意事項速查
| 操作 | 複製索引/主鍵 | 複製 AUTO_INCREMENT | 複製外鍵/觸發器 | 鎖表行為 |
| CREATE TABLE ... LIKE | ✅ | ❌ (重置為 1) | ✅ | 無(元數據操作) |
| CREATE TABLE ... SELECT | ❌ | ❌ | ❌ | 讀取舊表加共享鎖 |
| INSERT ... SELECT | N/A(目標表已有) | 目標表自增邏輯 | N/A | 目標表加排他鎖 |
最佳實踐建議
- 需完全克隆表結構(含索引、約束)→
CREATE TABLE new LIKE old; INSERT INTO new SELECT * FROM old;
- 僅需資料子集/欄位映射 →
CREATE TABLE new SELECT ... 或 INSERT SELECT
- 大表複製 → 分批次
INSERT ... SELECT ... LIMIT 10000 OFFSET ... 避免長鎖/大事務
- 跨庫遷移 →
mysqldump --no-data 導結構 + mysqldump --no-create-info 導資料,或使用 pt-table-sync