五種複製模式對照
| 模式 | SQL 語法 | 複製內容 | 索引/約束/主鍵 | 適用場景 |
| 1. 僅結構 | `CREATE TABLE new_tbl LIKE old_tbl;` | 無資料 | ✅ 完整保留 (PK, UK, FK, Index, Default, Auto_Inc) | 建立同結構空表、分區範本 |
| 2. 結構+資料 (CTAS) | `CREATE TABLE new_tbl AS SELECT * FROM old_tbl;` | 全量資料 | ❌ **僅保留欄位定義**,PK/UK/Index/FK/Auto_Inc **全部遺失** | 快速建立報表/暫存表、異構遷移 |
| 3. 完整複製 (推薦) | `CREATE TABLE new_tbl LIKE old_tbl; INSERT INTO new_tbl SELECT * FROM old_tbl;` | 全量資料 | ✅ 完整保留 | 備份、測試環境建立、版本基線 |
| 4. 指定欄位複製 | `CREATE TABLE new_tbl AS SELECT col1, col2 FROM old_tbl;` | 指定欄位資料 | ❌ 僅保留欄位定義與 NOT NULL | 脫敏導出、ETL 中間表、API 回應物件 |
| 5. 條件篩選複製 | `CREATE TABLE new_tbl AS SELECT * FROM old_tbl WHERE status=1;` | 符合條件資料 | ❌ 同上 | 歷史歸檔、分表、資料子集匯出 |
進階技巧與注意事項
CTAS (CREATE TABLE ... AS SELECT) 限制補充
- **不複製**:主鍵、外鍵、唯一索引、普通索引、觸發器、外鍵、預設值 (部分版本保留 `DEFAULT`)、`AUTO_INCREMENT` 屬性、註解、`CHECK` 約束 (8.0.16+)。
- **複製**:欄位名、資料型別、NULL/NOT NULL、字符集、排序規則、資料值。
- **別名處理**:`SELECT col1 AS new_name` → 新表欄位名為 `new_name`。
- **運算式欄位**:`SELECT id, price*qty AS total` → 新表含 `total` 欄位,型別由運算式推導。
大表複製效能優化
-- 1. 禁用索引/約束加速導入 (MyISAM)
ALTER TABLE new_tbl DISABLE KEYS;
INSERT INTO new_tbl SELECT * FROM old_tbl;
ALTER TABLE new_tbl ENABLE KEYS;
-- 2. InnoDB: 批次提交 + 關閉唯一檢查/外鍵檢查
SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;
INSERT INTO new_tbl SELECT * FROM old_tbl;
COMMIT;
SET autocommit=1; SET unique_checks=1; SET foreign_key_checks=1;
-- 3. 分批複製 (避免長交易/鎖升級/Undo Log 爆漲)
-- 使用主鍵範圍分批
INSERT INTO new_tbl SELECT * FROM old_tbl WHERE id BETWEEN 1 AND 100000;
-- 迴圈執行...
跨資料庫/跨伺服器複製
-- 同實例跨庫
CREATE TABLE db2.new_tbl LIKE db1.old_tbl;
INSERT INTO db2.new_tbl SELECT * FROM db1.old_tbl;
-- 異構/遠程: 配合 mysqldump / 連結伺服器 (FEDERATED) / CDC 工具 (Canal/Debezium)
驗證腳本
-- 結構對比
SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_KEY, EXTRA, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='db' AND TABLE_NAME IN ('old_tbl','new_tbl')
ORDER BY TABLE_NAME, ORDINAL_POSITION;
-- 行數/校驗和對比
SELECT 'old', COUNT(*), CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM old_tbl
UNION ALL
SELECT 'new', COUNT(*), CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM new_tbl;