X

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 進去.
Tsung: 對新奇的事物都很有興趣, 喜歡簡單的東西, 過簡單的生活.
Related Post