The Chillidog Software Blog

Upgrade all tables in a MySQL database to InnoDB

August 31st, 2012 by Gregory - 1 min read

MySQL v5.5 changes the default storage engine to InnoDB. If you'd like to upgrade your tables from MyISM to InnoDB, here is a simple SQL query that will generate the necessary code to upgrade all tables in a single SQL database.

SET SESSION group_concat_max_len = 204800;
SELECT GROUP_CONCAT('ALTER TABLE ', table_schema, '.',
table_name, ' ENGINE = INNODB' SEPARATOR '; ')
FROM information_schema.tables
WHERE table_schema = 'databasePrefix_databaseName';

Please adjustdatabasePrefix anddatabaseName to the appropriate values. For each table in the database this query generats code that looks like:

ALTER TABLEdatabasePrefix_databaseName.tableName ENGINE = INNODB;

This code is based on solutions found here and here. Please run this code at your own risk.