萬盛學電腦網

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

MySQL 表結構與資料複製完全指南:CREATE TABLE LIKE/SELECT、INSERT SELECT、臨時表三大類方法

方法一: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 ... SELECTN/A(目標表已有)目標表自增邏輯N/A目標表加排他鎖

最佳實踐建議

  1. 需完全克隆表結構(含索引、約束)→ CREATE TABLE new LIKE old; INSERT INTO new SELECT * FROM old;
  2. 僅需資料子集/欄位映射 → CREATE TABLE new SELECT ... 或 INSERT SELECT
  3. 大表複製 → 分批次 INSERT ... SELECT ... LIMIT 10000 OFFSET ... 避免長鎖/大事務
  4. 跨庫遷移 → mysqldump --no-data 導結構 + mysqldump --no-create-info 導資料,或使用 pt-table-sync
copyright © 萬盛學電腦網 all rights reserved