How to convert all tables from MyISAM into InnoDB?

mysql sql innodb
Pentium10 picture Pentium10 · Oct 4, 2010 · Viewed 255.8k times · Source

I know I can issue an alter table individually to change the table storage from MyISAM to InnoDB.

I am wondering if there is a way to quickly change all of them to InnoDB?

Answer

Will Jones picture Will Jones · Feb 29, 2012

Run this SQL statement (in the MySQL client, phpMyAdmin, or wherever) 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;

Then, copy the output and run as a new SQL query.

Related questions

How to regex in a MySQL query

I have a simple task where I need to search a record starting with string characters and a single digit after them. What I'm trying is this SELECT trecord FROM `tbl` WHERE (trecord LIKE 'ALA[d]%') And SELECT trecord …

Read More
#1025 - Error on rename of './database/#sql-2e0f_1254ba7' to './database/table' (errno: 150)

So I am trying to add a primary key to one of the tables in my database. Right now it has a primary key like this: PRIMARY KEY (user_id, round_number) Where user_id is a foreign key. I …

Read More
How to enable INNODB in mysql

When I execute a query in MySQL it returns an error saying that InnoDB is not enabled. When I clicked the storage engine, the InnoDB was disabled. How do I enable InnoDB?

Read More