How to change database engine of many Mysql tables

For some reasons, you have to change the default engine of your mysql database tables to another engine. For example, MyISAM to InnoDB. MySql allows us to change by using command line and if you’re using phpMyAdmin, you just need a few clicks.

ALTER TABLE tablename ENGINE = InnoDB

But the problem is that MySQL doesn’t allow us to use ALTER for so many tables at once. Thinking about you have more than 300 tables in your database. You have to type again and again until you reach the last table in your database. Here is a solution that you can do with PHP if you’re using MySQL with PHP.

$sq = mysql_connect("localhost","root","root");
$db = mysql_select_db("yourdb"); // your db
$rs = mysql_query("SHOW TABLES");

while($row=mysql_fetch_array($rs)){
 $k=mysql_query("ALTER TABLE {$row[0]} ENGINE = INNODB");
}

I think I don’t need to go deeper to explain for this very simple code. First and second lines are trying to connect to your MySQL server and database. Third line executes the query to list the tables in your database. And loop until it reach at the end of the record. The record $rs carries the list of your table names and array zero index of each record ($row) has the table name.