Conversion table types in MySQL #2

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:

1
2
3
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):

1
2
3
4
echo "SELECT concat('ALTER TABLE `',TABLE_NAME,'` ENGINE=InnoDB;')

FROM Information_schema.TABLES 

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:

1
2
3
4
5
6
7
8
9
10
<?php

    // connect to DB 

    <p>    $rs = mysql_query("SHOW tables");</p>

   <p>    while($row = mysql_fetch_array($rs))

    {

        $tbl = $row[0];

        $sql = "ALTER TABLE $tbl ENGINE=INNODB";

        mysql_query($sql);

    }

?&gt;</p>

In all the examples was converted into a InnoDB , of course, you can change to your preferred type.