MySQL 快速複製 Table 的方法

MySQL 要複製 整個 Table, 要怎麼做呢?

MySQL 快速複製 Table 的方法

以下範例都是把 old_table 複製到 new_table.

先講講結論, 最推薦的作法是下述兩行:

• CREATE TABLE new_table LIKE old_table;
• INSERT new_table SELECT * FROM old_table;
  • 註：這段語法可能會造成 innodb lock timeout，若 timeout 要調整這個參數
  • show variables like 'innodb_lock_wait_timeout';
  • set innodb_lock_wait_timeout = 50; // default
  • set innodb_lock_wait_timeout = 1000000; // 直接設大一點~

以下來講講幾種作法 和 優缺點.

MyISAM 的作法若比較暴力點的話, 可以用下述方式做:

1. CREATE TABLE new_table;
2. /etc/init.d/mysql stop
3. cd /var/lib/mysql/database_name
4. cp old_table.MYI new_table.MYI
5. cp old_table.MYD new_table.MYD
6. /etc/init.d/mysql start

這樣子也可以複製完成, 但是這麼暴力有可能會有些小問題要解決.

下述作法會比較建議(參考自此篇: sql - fastest way to copy a table in mysql), 不過有下述兩種作法, 有些不同, 先寫出作法, 再來解釋差異.

註: 以下 old_table 若跨 DB, 都可以寫成 old_db.old_table 來指定.

第一種作法: 一行語法複製 Table + Data, 不過需要手動增加 Primay、index key 等.

1. CREATE TABLE new_table SELECT * FROM old_table; # 這個作法 Primay、index key 都不會複製, 需要手動加
2. ALTER TABLE new_table ADD PRIMARY KEY (id);

第二種作法: 先複製 Table schema, 再來 INSERT Data. (建議使用此作法, Schema 一定是一模一樣的)

1. CREATE TABLE new_table LIKE old_table;
2. INSERT new_table SELECT * FROM old_table;

先來講講第一種作法: (此作法 Schema 可能不同, Data 是會正確複製過來的)

• CREATE TABLE new_table SELECT * FROM old_table;
• 此行會把 Table 和 Data 都複製到 new_table, 但是 Table 使用的 Engine、語系編碼 會跟 MySQL 預設的一樣, 而不是 Copy old_table 的. 結果可能就會是下述的狀況: (要看你的系統設定而定, old_table 和 new_table 的 schema 可能會不同, 但是 Data 是一致的)

• CREATE TABLE `old_table` (
  `no` int(8) NOT NULL,
  `cname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`no`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

• CREATE TABLE `new_table` (
  `no` int(8) NOT NULL,
  `cname` varchar(255) CHARACTER SET utf8 DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

第二種作法: (此作法可以將 Schema 和 Data 都是一模一樣的複製過來)

• CREATE TABLE new_table LIKE old_table;
• 此行會完整複製 Table Schema, 先複製完 Table Schema 後, 再來把資料一筆一筆 INSERT 進去.