如果還沒有，是否有查詢將表的引擎設置為 innodb？

我可以使用以下查詢將（MyISAM）表的引擎設置為 InnoDB：

alter table tablename engine=InnoDB

對於大型表，這需要一些時間來完成。不過，如果我在同一張表上再次執行相同的查詢，似乎又需要一些時間（少得多但仍然相當可觀）。我希望查詢是即時的，因為引擎已經設置為 innodb。

我的問題是：

• 是否有一個查詢有條件地設置引擎，例如

alter table tablename engine=InnoDB <如果還沒有 innodb>

• 為什麼第二個查詢會有這樣的延遲？（主要是出於好奇）

這會將所有 MyISAM 表批量轉換為 InnoDB

SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQLSTMT="${SQLSTMT} table_schema NOT IN "
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')"
SQLSTMT="${SQLSTMT} ORDER BY (data_length+index_length)"
echo "SET SQL_LOG_BIN = 0;" > ConvertMyISAMToInnoDB.sql
mysql -uroot -p -ANe"${SQLSTMT}" > ConvertMyISAMToInnoDB.sql
mysql -uroot < ConvertMyISAMToInnoDB.sql

為什麼跑步ALTER TABLE mydb.mytable ENGINE=InnoDB;需要很長時間？

在引擎蓋下它執行如下：

CREATE TABLE mydb.mytabletmp LIKE mydb.mytable;
INSERT INTO mydb.mytabletmp SELECT * FROM mydb.mytable;
DROP TABLE mydb.mytable;
ALTER TABLE mydb.mytabletmp RENAME mydb.mytable;

建構全新的聚集索引（主鍵條目的首頁）時出現延遲

如果你想完全自動化一個表的轉換，你需要這個：

SET @mydb = 'mydb';
SET @mytb = 'mytable';
SET @newengine = 'InnoDB';
SELECT IF(dbtb2='.',CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
CONCAT('SELECT ''',dbtb1,' is Already ',engine,''' as \"No Need to Convert\"'))
INTO @ConvertEngineSQL FROM (SELECT CONCAT(A.db,'.',A.tb) dbtb1,
CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
FROM (SELECT table_schema db,table_name tb,engine FROM information_schema.tables
WHERE table_schema=@mydb and table_name=@mytb) A LEFT JOIN
(SELECT table_schema db,table_name tb FROM information_schema.tables
WHERE table_schema=@mydb and table_name=@mytb AND engine=@newengine) B
USING (db,tb)) AA;
SELECT @ConvertEngineSQL;
PREPARE st FROM @ConvertEngineSQL; EXECUTE st; DEALLOCATE PREPARE st;

這是我 PC 的 MySQL 設置的範例

mysql> show create table codewaggle.mytable\G
*************************** 1. row ***************************
      Table: mytable
Create Table: CREATE TABLE `mytable` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SET @mydb = 'codewaggle';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @mytb = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @newengine = 'InnoDB';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(dbtb2='.',CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
   -> CONCAT('SELECT ''',dbtb1,' is Already ',engine,''' as \"No Need to Convert\"'))
   -> INTO @ConvertEngineSQL
   -> FROM (SELECT CONCAT(A.db,'.',A.tb) dbtb1,
   -> CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
   -> FROM (SELECT table_schema db,table_name tb,engine FROM information_schema.tables
   -> WHERE table_schema=@mydb and table_name=@mytb) A LEFT JOIN
   -> (SELECT table_schema db,table_name tb FROM information_schema.tables
   -> WHERE table_schema=@mydb and table_name=@mytb AND engine=@newengine) B
   -> USING (db,tb)) AA;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @ConvertEngineSQL;
+----------------------------------------------+
| @ConvertEngineSQL                            |
+----------------------------------------------+
| ALTER TABLE codewaggle.mytable ENGINE=InnoDB |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE st FROM @ConvertEngineSQL; EXECUTE st; DEALLOCATE PREPARE st;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Query OK, 0 rows affected (1.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

mysql> SET @mydb = 'codewaggle';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @mytb = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @newengine = 'InnoDB';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(dbtb2='.',CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
   -> CONCAT('SELECT ''',dbtb1,' is Already ',engine,''' as \"No Need to Convert\"'))
   -> INTO @ConvertEngineSQL
   -> FROM (SELECT CONCAT(A.db,'.',A.tb) dbtb1,
   -> CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
   -> FROM (SELECT table_schema db,table_name tb,engine FROM information_schema.tables
   -> WHERE table_schema=@mydb and table_name=@mytb) A LEFT JOIN
   -> (SELECT table_schema db,table_name tb FROM information_schema.tables
   -> WHERE table_schema=@mydb and table_name=@mytb AND engine=@newengine) B
   -> USING (db,tb)) AA;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @ConvertEngineSQL;
+-----------------------------------------------------------------------+
| @ConvertEngineSQL                                                     |
+-----------------------------------------------------------------------+
| SELECT 'codewaggle.mytable is Already InnoDB' as "No Need to Convert" |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE st FROM @ConvertEngineSQL; EXECUTE st; DEALLOCATE PREPARE st;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+--------------------------------------+
| No Need to Convert                   |
+--------------------------------------+
| codewaggle.mytable is Already InnoDB |
+--------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

試一試 ！！！

引用自：https://dba.stackexchange.com/questions/39188