我知道我可以单独发出一个alter表,将表存储从MyISAM更改为InnoDB。
我想知道是否有一种方法可以快速将它们全部更改为InnoDB?
我知道我可以单独发出一个alter表,将表存储从MyISAM更改为InnoDB。
我想知道是否有一种方法可以快速将它们全部更改为InnoDB?
当前回答
试试这个shell脚本
DBENGINE='InnoDB' ;
DBUSER='your_db_user' ;
DBNAME='your_db_name' ;
DBHOST='your_db_host'
DBPASS='your_db_pass' ;
mysqldump --add-drop-table -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME > mtest.sql; mysql -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME -Nse "SHOW TABLES;" | while read TABLE ; do mysql -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME -Nse "ALTER TABLE $TABLE ENGINE=$DBENGINE;" ; done
其他回答
<?php
// connect your database here first
mysql_connect('host', 'user', 'pass');
$databases = mysql_query('SHOW databases');
while($db = mysql_fetch_array($databases)) {
echo "database => {$db[0]}\n";
mysql_select_db($db[0]);
$tables = mysql_query('SHOW tables');
while($tbl = mysql_fetch_array($tables)) {
echo "table => {$tbl[0]}\n";
mysql_query("ALTER TABLE {$tbl[0]} ENGINE=InnoDB");
}
}
运行此SQL语句(在MySQL客户端、phpMyAdmin或任何地方)检索数据库中的所有MyISAM表。
将name_of_your_db变量的值替换为您的数据库名称。
SET @DATABASE_NAME = 'name_of_your_db';
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;
然后,复制输出并作为一个新的SQL查询运行。
这是一个简单的php脚本。
<?php
@error_reporting(E_ALL | E_STRICT);
@ini_set('display_errors', '1');
$con = mysql_connect('server', 'user', 'pass');
$dbName = 'moodle2014';
$sql = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '".$dbName."';";
$rs = mysql_query($sql, $con);
$count = 0;
$ok = 0;
while($row = mysql_fetch_array($rs)){
$count ++;
$tbl = $row[0];
$sql = "ALTER TABLE ".$dbName.".".$tbl." ENGINE=INNODB;";
$resultado = mysql_query($sql);
if ($resultado){
$ok ++;
echo $sql."<hr/>";
}
}
if ($count == $ok){
echo '<div style="color: green"><b>ALL OK</b></div>';
}else{
echo '<div style="color: red"><b>ERRORS</b>Total tables: '.$count.', updated tables:'.$ok.'</div>';
}
你可以在mysql命令行工具中执行这个语句:
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
您可能需要指定用户名和密码:mysql -u username -p 结果是一个sql脚本,你可以管道回mysql:
mysql name-of-database < convert.sql
替换上述语句和命令行中的"name-of-database"。
当表很大时,最好从控制台进行操作
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