萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 資料表複製完整指南:結構、資料、部分欄位與條件篩選五種模式

五種複製模式對照

模式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;
copyright © 萬盛學電腦網 all rights reserved