我知道我可以单独发出一个alter表,将表存储从MyISAM更改为InnoDB。
我想知道是否有一种方法可以快速将它们全部更改为InnoDB?
我知道我可以单独发出一个alter表,将表存储从MyISAM更改为InnoDB。
我想知道是否有一种方法可以快速将它们全部更改为InnoDB?
当前回答
一个简单的MySQL版本。
你可以简单地启动mysql可执行文件,使用数据库和复制粘贴查询。
这将把当前数据库中的所有MyISAM表转换为INNODB表。
DROP PROCEDURE IF EXISTS convertToInnodb;
DELIMITER //
CREATE PROCEDURE convertToInnodb()
BEGIN
mainloop: LOOP
SELECT TABLE_NAME INTO @convertTable FROM information_schema.TABLES
WHERE `TABLE_SCHEMA` LIKE DATABASE()
AND `ENGINE` LIKE 'MyISAM' ORDER BY TABLE_NAME LIMIT 1;
IF @convertTable IS NULL THEN
LEAVE mainloop;
END IF;
SET @sqltext := CONCAT('ALTER TABLE `', DATABASE(), '`.`', @convertTable, '` ENGINE = INNODB');
PREPARE convertTables FROM @sqltext;
EXECUTE convertTables;
DEALLOCATE PREPARE convertTables;
SET @convertTable = NULL;
END LOOP mainloop;
END//
DELIMITER ;
CALL convertToInnodb();
DROP PROCEDURE IF EXISTS convertToInnodb;
其他回答
<?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);
}
?>
cd / var / lib / mysql / DBNAME
Ls | grep ".frm" | cut -d"."-f1 | xargs -I{} -n1 mysql -D DBNAME -e "alter table {} ENGINE=INNODB;"-uroot -pXXXXX
这很简单。只有两步。
复制,粘贴并运行: SET @DATABASE_NAME = ' name_your_db '; SELECT CONCAT('ALTER TABLE ", table_name, ' ENGINE=InnoDB;') AS sql_statements FROM information_schema。TABLE AS tb WHERE ' ENGINE ' = 'MyISAM' AND ' TABLE_TYPE ' = 'BASE TABLE'
(复制粘贴所有结果在SQL选项卡)
将所有结果复制到SQL选项卡并在下面一行中粘贴。 开始事务; 提交;
例如:
START TRANSACTION;
ALTER TABLE `admin_files` ENGINE=InnoDB;
COMMIT;
<?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");
}
}
我是一个新手,必须找到自己的解决方案,因为网上的mysql命令通常充满了拼写错误,这对刚开始使用的人来说是一个现实生活的噩梦。这是我的解决方案....
我用excel一次准备了几十个命令(可以复制和粘贴),而不是每个表一个命令。
How? expand your putty window and enter mysql and then run the command "SHOW TABLE STATUS;" and the copy/paste the output to microsoft excel. Go to the Data tab and use the "text to columns" feature an delimit the columns by a space key. Then Sort the columns by whichever column shows your table types and delete all rows which the tables are already in InnoDb format (because we don't need to run commands against them, they are already done). Then add 2 columns to the left of the tables column, and 2 columns to the right. Then paste in the first part of the command in column-1 (see below). Column 2 should contain only a space. Column 3 is your tables column. Column 4 should contain only a space. Column 5 is the last part of your command. It should look like this:
column-1 column-2 column-3 column-4 column-5
ALTER TABLE t_lade_tr ENGINE=InnoDB;
ALTER TABLE t_foro_detail_ms ENGINE=InnoDB;
ALTER TABLE t_ljk_ms ENGINE=InnoDB;
然后每次复制粘贴5行到mysql中。这将一次转换大约5个。我注意到,如果我一次做了更多的操作,那么命令就会失败。