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.

Copyright © 1998 - 2024 Up level work support of Linux servers. Modern Clouds - architecture, deploy and maintenance..Theme by Puro
Scroll to top