萬盛學電腦網

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

MySQL 表克隆:複製結構、數據、索引完整方案

MySQL 表克隆完整指南:結構、數據、索引一鍵複製

業務中常需複製表結構、數據或兩者兼備。本文介紹 SHOW CREATE TABLE + CREATE TABLE + INSERT SELECT 標準克隆流程,以及 CREATE TABLE ... LIKE、CREATE TABLE ... SELECT 速成法。

標準克隆流程(保留索引、約束、默認值)

步驟 1:獲取完整建表語句

mysql> SHOW CREATE TABLE tutorials_tbl\G
*************************** 1. row ***************************
       Table: tutorials_tbl
Create Table: CREATE TABLE `tutorials_tbl` (
  `tutorial_id` int(11) NOT NULL auto_increment,
  `tutorial_title` varchar(100) NOT NULL default '',
  `tutorial_author` varchar(40) NOT NULL default '',
  `submission_date` date default NULL,
  PRIMARY KEY  (`tutorial_id`),
  UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

步驟 2:修改表名並創建

CREATE TABLE `clone_tbl` (
  `tutorial_id` int(11) NOT NULL auto_increment,
  `tutorial_title` varchar(100) NOT NULL default '',
  `tutorial_author` varchar(40) NOT NULL default '',
  `submission_date` date default NULL,
  PRIMARY KEY  (`tutorial_id`),
  UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

步驟 3:複製數據

INSERT INTO clone_tbl (tutorial_id, tutorial_title, tutorial_author, submission_date)
SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
FROM tutorials_tbl;

速成方案對比

語句複製結構複製索引/約束複製數據適用場景
CREATE TABLE t2 LIKE t1✅✅ (含索引、FK、默認值)❌結構克隆
CREATE TABLE t2 SELECT * FROM t1✅ (僅列定義)❌ (無索引、PK、FK、默認值)✅數據導出、歸檔
CREATE TABLE t2 LIKE t1; INSERT INTO t2 SELECT * FROM t1;✅✅✅完整克隆(推薦)
標準流程✅✅✅需調整結構再克隆

實戰變體

僅複製結構(不含數據)

CREATE TABLE new_table LIKE old_table;

條件複製數據

CREATE TABLE new_table LIKE old_table;
INSERT INTO new_table SELECT * FROM old_table WHERE status = 1 AND create_time > '2024-01-01';

選取特定列並重命名

CREATE TABLE user_summary AS
SELECT id AS user_id, CONCAT(first_name, ' ', last_name) AS full_name, email
FROM users;

跨數據庫克隆

CREATE TABLE db2.new_table LIKE db1.old_table;
INSERT INTO db2.new_table SELECT * FROM db1.old_table;

大表克隆性能優化

  • 分批插入:每批 1-5 萬行,提交事務,避免長事務與 Undo Log 膨脹
  • 禁用索引後重建:
    ALTER TABLE clone_tbl DROP INDEX uk_email, DROP INDEX idx_status;
    INSERT INTO clone_tbl SELECT * FROM tutorials_tbl;
    ALTER TABLE clone_tbl ADD UNIQUE INDEX uk_email (email), ADD INDEX idx_status (status);
  • 並行導入:多線程按主鍵範圍分段 INSERT
  • 物理複製:停庫下拷貝 .ibd 文件 + IMPORT TABLESPACE (InnoDB Transportable Tablespaces)

驗證克隆結果

-- 結構對比
SHOW CREATE TABLE tutorials_tbl\G
SHOW CREATE TABLE clone_tbl\G

-- 行數核對
SELECT COUNT(*) FROM tutorials_tbl;
SELECT COUNT(*) FROM clone_tbl;

-- 抽樣數據核對
SELECT * FROM tutorials_tbl ORDER BY RAND() LIMIT 10;
SELECT * FROM clone_tbl ORDER BY RAND() LIMIT 10;

-- 校驗和對比 (MySQL 8.0+)
SELECT CHECKSUM TABLE tutorials_tbl, clone_tbl;

注意事項

  • AUTO_INCREMENT 值會複製,新表插入從現有最大值+1 開始
  • 外鍵約束需目標表存在,跨庫克隆時需先建父表
  • 分區表克隆需保持分區定義一致
  • 視圖/觸發器/存儲過程不會被複製
  • 權限/統計信息/註釋需手動同步
copyright © 萬盛學電腦網 all rights reserved