Update tables Engine to InnoDB

将指定数据库 dbname 中 所有表 Engine 改为 InnonDB 类型

DELIMITER //
DROP PROCEDURE IF EXISTS transferTableEngine//
CREATE PROCEDURE transferTableEngine (IN dbName VARCHAR(100))
READS SQL DATA
BEGIN
DECLARE isEnd BOOL DEFAULT 0;
DECLARE tmpTableName VARCHAR(255) DEFAULT NULL;
DECLARE cur CURSOR FOR SELECT ts.TABLE_NAME FROM information_schema.TABLES AS ts WHERE ts.TABLE_SCHEMA LIKE dbName;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET isEnd=1;
OPEN cur;
REPEAT
FETCH cur INTO tmpTableName;
SET @tmpStmt=CONCAT('ALTER TABLE `', dbName, '`.`', tmpTableName, '` ENGINE=INNODB');
PREPARE stmt FROM @tmpStmt;
EXECUTE stmt;
UNTIL isEnd
END REPEAT;
CLOSE cur;
END;
//
DELIMITER ;

 

使用 以下代码调用

CALL transferTableEngine('dbname');