Convert MyISAM to InnoDB

Updated 9 hours ago by Ben Oates

This guide will explain how to convert your Database from MyISAM to InnoDB.

Run this SQL statement in the MySQL client or phpMyAdmin to retrieve all the MyISAM tables in your database.

Replace value of the name_of_your_db variable with your database name.

SET @DATABASE_NAME = 'name_of_your_db';

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

Once this has been done, copy the output and run it as a new SQL query. This will convert all tables to InnoDB

To convert a specific table replace the value of the my_table_name variable with your table name

ALTER TABLE my_table_name ENGINE = InnoDB;

To switch back to MyISAM, just change the engine:

ALTER TABLE my_table_name ENGINE = MyISAM;

How did we do?