php批量转换Mysql表引擎

有些时候可能需要批量转换Mysql表的引擎,如下为PHP操作实现

<?php
/**
 * 批量转换Mysql表引擎
 */

error_reporting(E_ALL);

// 数据库连接配置
$host     = 'localhost';
$username = 'root';
$passwd   = '';
$database = 'test';

// 要转换的库名配置,多库转换增加配置元素即可
$configs  = array($database);

// 转换配置
$convert_rule = array(
    'from' => 'InnoDB',
    'to'   => 'MyISAM'
);

mysql_engine_convert();

/**
 * 转换函数
 */
function mysql_engine_convert()
{
    global $host,$username,$passwd,$configs,$convert_rule;

    if ( ($conn = mysql_connect($host, $username, $passwd)) !== false)
    {
        foreach ($configs as $db_name)
        {
            mysql_select_db($db_name) or exit('Not found db: '. $db_name);
            $tables = mysql_query("SHOW FULL TABLES");
            while ($table = mysql_fetch_row($tables))
            {
                if ($table[1] === 'VIEW') continue;

                $sql = "SHOW TABLE STATUS from {$db_name} where Name='{$table[0]}' ";
                if ($result = mysql_query($sql))
                {
                    $table_status = mysql_fetch_row($result);
                    
                    if (strtolower($table_status[1]) == strtolower($convert_rule['from']))
                        mysql_query("ALTER TABLE {$table[0]} ENGINE = {$convert_rule['to']}");
                }
            }
            echo $db_name,':All tables ENGINE is ',$convert_rule['to'],"\n";
        }
        
    } else {
        echo "db error\n";
    }

}