MySQL Script – Alter/Convert All Tables in a Database from MyISAM to InnoDB

Pieced together from various tutorials on the web with a lot of my own additions, here is a snippet of SQL that will alter or convert all tables in a database from “MyISAM” to “InnoDB”. As long as your MySQL user account has privileges to create stored procedures, all you have to do is simply edit the @DATABASE_NAME variable at the top of the script and run it in PhpMyAdmin if you’ve got it. Enjoy!

# SQL Script: AlterTablesInnoDB
# Author: Nathan Thomas
# Date: 06/03/2015

SET @DATABASE_NAME='YourDBName';

CREATE TEMPORARY TABLE IF NOT EXISTS AlterTablesInnoDB_TEMP
(
	SELECT
		CONCAT('ALTER TABLE `',@DATABASE_NAME,'`.`', table_name, '` ENGINE=InnoDB; ') AS STMT
	FROM
		information_schema.tables
	WHERE
		table_schema=@DATABASE_NAME
	AND 
		`ENGINE`='MyISAM'
	AND
		`TABLE_TYPE`='BASE TABLE'
	ORDER BY
		table_name
	ASC
);

SET @NUMROWS=(SELECT COUNT(*) FROM AlterTablesInnoDB_TEMP);
ALTER TABLE AlterTablesInnoDB_TEMP ADD `ID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

DROP PROCEDURE IF EXISTS AlterTablesInnoDB;
DELIMITER $$
CREATE PROCEDURE AlterTablesInnoDB( IN NUMROWS INT(11) UNSIGNED )
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=NUMROWS DO
		SET @QRY='';
		SET @QRY=(SELECT `STMT` FROM AlterTablesInnoDB_TEMP WHERE ID=i);
		PREPARE stmt FROM @QRY;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
	    SET i=i+1;
	END WHILE;
END$$
CALL AlterTablesInnoDB(@NUMROWS);

And then after you have run that script, you can get rid of the stored procedure we created by running this command:

DROP PROCEDURE IF EXISTS AlterTablesInnoDB;

Leave a Reply