MySQL 如何将 MyISAM 中的所有表转换为 InnoDB？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3856435/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert all tables from MyISAM into InnoDB?
提问by Pentium10
I know I can issue an alter table individually to change the table storage from MyISAM to InnoDB.
我知道我可以单独发出一个更改表来将表存储从 MyISAM 更改为 InnoDB。
I am wondering if there is a way to quickly change all of them to InnoDB?
我想知道是否有办法快速将它们全部更改为 InnoDB？
采纳答案by Gajendra Bang
<?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);
}
?>
回答by Will Jones
Run this SQL statement (in the MySQL client, phpMyAdmin, or wherever) to retrieve all the MyISAM tables in your database.
运行此 SQL 语句（在 MySQL 客户端、phpMyAdmin 或任何地方）以检索数据库中的所有 MyISAM 表。
Replace value of the name_of_your_dbvariable with your database name.
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;
Then, copy the output and run as a new SQL query.
然后，复制输出并作为新的 SQL 查询运行。
回答by Omkar Kulkarni
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;')
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='MyISAM'
AND table_schema = 'mydatabase';
Works like a charm.
奇迹般有效。
This will give you list of all tables with the alter queries that you can run in a batch
这将为您提供所有表的列表，其中包含可以批量运行的更改查询
回答by kuzea
One line:
一条线：
mysql -u root -p dbName -e
"show table status where Engine='MyISAM';" | awk
'NR>1 {print "ALTER TABLE "" ENGINE = InnoDB;"}' |
mysql -u root -p dbName
回答by Vijay Varadan
In the scripts below, replace <username>, <password> and <schema> with your specific data.
在下面的脚本中，将 <username>、<password> 和 <schema> 替换为您的特定数据。
To show the statements that you can copy-paste into a mysql client session type the following:
要显示可以复制粘贴到 mysql 客户端会话中的语句，请键入以下内容：
echo 'SHOW TABLES;' \
| mysql -u <username> --password=<password> -D <schema> \
| awk '!/^Tables_in_/ {print "ALTER TABLE `"echo 'SHOW TABLES;' \
| mysql -u <username> --password=<password> -D <schema> \
| awk '!/^Tables_in_/ {print "ALTER TABLE `"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=InnoDB;')
FROM information_schema.tables
WHERE engine = 'MyISAM';
"` ENGINE = InnoDB;"}' \
| column -t \
| mysql -u <username> --password=<password> -D <schema>
"` ENGINE = InnoDB;"}' \
| column -t \
To simply execute the change, use this:
要简单地执行更改，请使用以下命令：
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
CREDIT: This is a variation of what was outlined in this article.
信用：这是本文概述的变体。
回答by Zwarmapapa
Use this as a sql query in your phpMyAdmin
将此用作 phpMyAdmin 中的 sql 查询
mysql name-of-database < convert.sql
回答by Hendrik Brummermann
You can execute this statement in the mysql command line tool:
可以在mysql命令行工具中执行这条语句：
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;
You may need to specify username and password using: mysql -u username -p The result is an sql script that you can pipe back into mysql:
您可能需要使用以下命令指定用户名和密码： mysql -u username -p 结果是一个 sql 脚本，您可以将其通过管道传输回 mysql：
SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', table_name, ' ENGINE=InnoDB;') AS sql_statements
FROM information_schema.tables
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'innodb', 'sys', 'tmp')
AND `ENGINE` = 'MyISAM'
AND `TABLE_TYPE` = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, table_name DESC;
Replace "name-of-database" in the above statement and command line.
替换上述语句和命令行中的“name-of-database”。
回答by Sachin from Pune
It's Very simple there are only TWO step just copy and paste :
这很简单，只需复制和粘贴两步：
step 1.
第1步。
mysqldump --no-data DBNAME | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' > my_schema.sql;
mysqldump --no-create-info DBNAME > my_data.sql;
(copy and paste all result in in sql tab)
（将所有结果复制并粘贴到 sql 选项卡中）
step 2: (copy all result in in sql tab) and paste below in the line
第 2 步：（将所有结果复制到 sql 选项卡中）并粘贴到下面的行中
START TRANSACTION;
开始交易；
COMMIT;
犯罪;
eg. START TRANSACTION;
例如。开始交易；
ALTER TABLE admin_filesENGINE=InnoDB;
改变表admin_files引擎= InnoDB;
COMMIT;
犯罪;
回答by Lavi Avigdor
To generate ALTER statements for all tables in all the non-system schemas, ordered by those schemas/tables run the following:
要为所有非系统模式中的所有表生成 ALTER 语句，按这些模式/表排序，请运行以下命令：
mysql DBNAME < my_schema.sql && mysql DBNAME < my_data.sql
After that, run those queries via a client to perform the alteration.
之后，通过客户端运行这些查询以执行更改。
- Answer is based on above answers, but improves schema handling.
- 答案基于上述答案，但改进了模式处理。
回答by Quinn Comendant
It hasn't been mentioned yet, so I'll write it for posterity:
还没有提到，所以我会写给后代：
If you're migrating between DB servers (or have another reason you'd dump and reload your dta), you can just modify the output from mysqldump:
如果您要在数据库服务器之间迁移（或者有其他原因要转储并重新加载 dta），则只需修改以下输出mysqldump：
Then load it again:
然后再次加载：
##代码##(Also, in my limited experience, this can be a much faster process than altering the tables ‘live'. It probably depends on the type of data and indexes.)
（此外，以我有限的经验，这可能比更改“实时”表快得多。这可能取决于数据和索引的类型。）
