Sometimes it is necessary to convert a MySQL table in an existing database. For example, I ran into this with the need to create relationships between tables through the foreign key. As it turned out, the default MySQL MyISAM engine does not support this. To create a link table must be of type InnoDB. But this is not a big problem, because you can convert the table. To convert a single table SQL query is enough (which can be done, for example, through phpmyadmin):
1 ALTER TABLE table_name ENGINE=InnoDB;
If you need to batch convert all tables in the database, then you can get a list of queries as follows:
SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') as ExecuteTheseSQLCommands
FROM information_schema.tables WHERE table_schema = 'db_name'
ORDER BY table_name DESC;
And then execute them.
Or do this with the MySQL interpreter (via the console):
echo "SELECT concat('ALTER TABLE `',TABLE_NAME,'` ENGINE=InnoDB;')
WHERE ENGINE != 'InnoDB' AND TABLE_TYPE='BASE TABLE'
AND TABLE_SCHEMA='name-of-database'" | mysql > convert.sql
Or just make everything a script on php:
// connect to DB
<p> $rs = mysql_query("SHOW tables");</p>
<p> while($row = mysql_fetch_array($rs))
$tbl = $row;
$sql = "ALTER TABLE $tbl ENGINE=INNODB";
In all the examples was converted into a InnoDB , of course, you can change to your preferred type.