我知道我可以单独发出一个alter表,将表存储从MyISAM更改为InnoDB。
我想知道是否有一种方法可以快速将它们全部更改为InnoDB?
我知道我可以单独发出一个alter表,将表存储从MyISAM更改为InnoDB。
我想知道是否有一种方法可以快速将它们全部更改为InnoDB?
当前回答
<?php
// connect your database here first
//
// Actual code starts here
$sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
AND ENGINE = 'MyISAM'";
$rs = mysql_query($sql);
while($row = mysql_fetch_array($rs))
{
$tbl = $row[0];
$sql = "ALTER TABLE `$tbl` ENGINE=INNODB";
mysql_query($sql);
}
?>
其他回答
您可以用您最喜欢的脚本语言编写一个脚本来完成它。该脚本将执行以下操作:
Issue显示满表。对于返回的每一行,检查第二列是否显示为“BASE TABLE”而不是“VIEW”。如果它不是'VIEW',发出适当的ALTER TABLE命令。
它还没有被提及,所以我将为后人写下来:
如果你在DB服务器之间迁移(或者有其他原因你需要转储和重新加载你的dta),你可以修改mysqldump的输出:
mysqldump --no-data DBNAME | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' > my_schema.sql;
mysqldump --no-create-info DBNAME > my_data.sql;
然后再次加载:
mysql DBNAME < my_schema.sql && mysql DBNAME < my_data.sql
(此外,根据我有限的经验,这可能是一个比“实时”修改表快得多的过程。这可能取决于数据和索引的类型。)
<?php
// connect your database here first
//
// Actual code starts here
$sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
AND ENGINE = 'MyISAM'";
$rs = mysql_query($sql);
while($row = mysql_fetch_array($rs))
{
$tbl = $row[0];
$sql = "ALTER TABLE `$tbl` ENGINE=INNODB";
mysql_query($sql);
}
?>
当表很大时,最好从控制台进行操作
convert-to-innodb.sh
#!/usr/bin/env bash
# Usage: ./convert-to-innodb.sh 'db' 'user' 'password' | mysql 'db' -u user -p password
set -eu
db="$1"
user="$2"
pass="$3"
sql="SET @DATABASE_NAME = '${db}';"
sql+="SELECT CONCAT('ALTER TABLE \`', table_name, '\` ENGINE=InnoDB;') AS sql_statements
FROM information_schema.tables AS tb
WHERE table_schema = @DATABASE_NAME
AND \`ENGINE\` = 'MyISAM'
AND \`TABLE_TYPE\` = 'BASE TABLE'
ORDER BY table_name DESC;"
echo $sql | mysql -u${user} -p${pass} | tail -n +2
<?php
// Convert all MyISAM tables to INNODB tables in all non-special databases.
// Note: With MySQL less than 5.6, tables with a fulltext search index cannot be converted to INNODB and will be skipped.
if($argc < 4)
exit("Usage: {$argv[0]} <host> <username> <password>\n");
$host = $argv[1];
$username = $argv[2];
$password = $argv[3];
// Connect to the database.
if(!mysql_connect($host, $username, $password))
exit("Error opening database. " . mysql_error() . "\n");
// Get all databases except special ones that shouldn't be converted.
$databases = mysql_query("SHOW databases WHERE `Database` NOT IN ('mysql', 'information_schema', 'performance_schema')");
if($databases === false)
exit("Error showing databases. " . mysql_error() . "\n");
while($db = mysql_fetch_array($databases))
{
// Select the database.
if(!mysql_select_db($db[0]))
exit("Error selecting database: {$db[0]}. " . mysql_error() . "\n");
printf("Database: %s\n", $db[0]);
// Get all MyISAM tables in the database.
$tables = mysql_query("SHOW table status WHERE Engine = 'MyISAM'");
if($tables === false)
exit("Error showing tables. " . mysql_error() . "\n");
while($tbl = mysql_fetch_array($tables))
{
// Convert the table to INNODB.
printf("--- Converting %s\n", $tbl[0]);
if(mysql_query("ALTER TABLE `{$tbl[0]}` ENGINE = INNODB") === false)
printf("--- --- Error altering table: {$tbl[0]}. " . mysql_error() . "\n");
}
}
mysql_close();
?>