MySQL轉換表的引擎

MySQL轉換表的引擎

有很多種方法可以將表的儲存引擎轉換成另一種引擎。每種方法都有其優缺點，在這裡介紹四種方法：

選擇優先順序（pt-online-schema-change > 建立與查詢 > 匯出和匯入 > ALTER TABLE）。
 
•ALTER TABLE

 　將表從一個引擎修改為另一個引擎最簡單的方法是使用ALTER TABLE語句。下面的語句將TB表的引擎修改為InnoDB：

mysql> ALTER TABLE TB ENGINE = InnoDB;

　　PS：該方法適用於任何儲存引擎。但有一個問題：需要很長的執行時間。

　　MySQL會按行將資料庫從原表複製到一張新的表中，在複製期間可能會消耗系統所有的I/O能力，同時原表上會加上讀鎖。
 
•匯出和匯入（mysqldump）

 　　為了更好的控制轉換的過程，可以使用mysqldump工具將資料匯出到檔案，然後修改檔案中CREATE TABLE語句的儲存引擎選項，注意同時修改表名，因為同一個資料庫中不能存在相同的表名，即使他們使用的是不同的儲存引擎。

　　PS：mysqldump預設會自動在CREATE TABLE語句前加上DROP TABLE語句，不注意這一點可能會導致資料丟失。

　　1、下面的語句將DB庫中的TB表資料匯出到tb.sql檔案中：

[root@desktop]# mysqldump -u root -p DB TB > tb.sql

　　2、修改tb.sql檔案（如果需要備份TB表，請更改表名）：

CREATE TABLE `TB` (
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

　　3、下面的語句將tb.sql檔案匯入到DB庫中：

[root@desktop]# mysql -u root -p DB < tb.sql

 
•建立與查詢（CREATE 和 SELECT）

　　這種方法綜合第一種方法的高效和第二種方法的安全。不需要匯出整個表的資料，而是首先建立一個新的儲存引擎的表，然後利用INSERT...SELECT文法來導資料：

　　資料量不大使用以下語句：

mysql > CREATE TABLE TB2 like TB1;
mysql > ALTER TABLE TB2 ENGINE=InnoDB;
mysql > INSERT INTO TB2 SELECT * FROM TB1;

　資料量大使用以下語句：

mysql > CREATE TABLE TB2 like TB1;
mysql > ALTER TABLE TB2 ENGINE=InnoDB;
mysql > START TRANSACTION;
mysql > INSERT INTO TB2 SELECT * FROM TB1 WHERE id BETWEEN x AND y;
mysql > COMMIT;

　　PS：這樣的操作完成後，新表是原表的一個全量複製，原表還在，如需要可以刪除原表。

　　如果有必要，可以在執行的過程中對原表進行加鎖，以確保新表和原表的資料一致。

•使用Percona Toolkit提供的pt-online-schema-change工具：

　　使用以下命令可將DB庫中的TB1錶轉換成InnoDB：

[root@desktop]# pt-online-schema-change -u root -h 127.0.0.1 -p 123456 --alter='ENGINE=Innodb' --execute A=utf8,D=DB,t=TB1

　　執行成功後會有如下提示：

Successfully altered `DB`.`TB1`.

　　PS：原理為建立暫存資料表->修改修改結構->記錄資料->刪掉原表->重新命名暫存資料表