我知道可以单独执行alter table语句将表的存储引擎从MyISAM更改为InnoDB。
我想知道是否有一种快速将所有表更改为InnoDB的方法？
32个回答
635
在MySQL客户端、phpMyAdmin或其他地方运行此SQL语句，以检索数据库中的所有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 查询运行。
- Will Jones
14
4做得很好！我已经将它放入了一个示例shell脚本中：http://shrubbery.mynetgear.net/c/display/W/Shell+and+MySQL#ShellandMySQL-ConvertMyISAMtablestoInnoDB - Joshua Davis
5我理解您遇到了错误信息 "#1267 illegal mix of collations..."，希望我能为您提供翻译。这个错误提示可能是因为在不同的字符编码格式之间混合使用导致的，请检查您的代码并确认所有字符串都使用相同的字符编码格式。 - Rápli András
2只是出于好奇，显式降序排序的意义是什么？(
ORDER BY table_name DESC) - rinogo14如果您要处理多个数据库并且不想每次更改数据库，请将“CONCAT（'ALTER TABLE'，table_name，'ENGINE = InnoDB;'）”更改为“CONCAT（'ALTER TABLE'，@DATABASE_NAME，'。'，table_name，'ENGINE = InnoDB;'）”。 - SameOldNick
3如果你想获取除MySQL系统数据库外的所有数据库的语句：
SELECT CONCAT('ALTER TABLE \', table_schema, '`.`', table_name, '` ENGINE=InnoDB;') AS sql_statements FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql') AND engine = 'MyISAM' AND table_type = 'BASE TABLE' ORDER BY table_schema,table_name` - dr fu manchu显示剩余9条评论
186
<?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);
}
?>
- Gajendra Bang
8
5最好限制这个操作只针对你所专注的数据库。添加一个“AND TABLE_SCHEMA ='dbname'”，否则这可能会/将改变所有互联网MySQL表格为innodb（有些应该是memory）。 - Noodles
10PHP的
mysql_*接口已被弃用并在7版本中移除。不要直接使用此代码。 - Rick James4@GajendraBang - 是的，当它被呈现时，答案是有效的。但对于新手来说，它不再有效。我的意图是警告不要直接使用它。 - Rick James
1这个问题并没有提到 PHP。 - phil294
2最近的编辑为什么没有被标记？MySQL 部分是 Will Jones 回答的直接复制。查看每个编辑历史记录，可以发现 Will 的回答出现在 2013 年，而这个回答出现在 2019 年。因此，这个问题的完整性受到了损害。 - rmutalik
显示剩余3条评论
76
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;')
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='MyISAM'
AND table_schema = 'mydatabase';
运行得非常好。
这将为您提供所有表的列表，以及您可以批量运行的修改查询。
- Omkar Kulkarni
4
6运行此脚本后，首先需要执行以下查询：
USE databasename;然后您就可以使用上述脚本提供的查询了。 - gijs007
你如何运行批处理？ - Marc Alexander
以上查询将为您提供修改表的查询。只需选择它们并一起执行，或者如果结果集中存在太多表，则将其分成50个查询一组并运行它们。 - Omkar Kulkarni
2即使在2018年和Percona群集上也可以工作。如果从PHPMyAdmin使用它，您只会得到20个左右的名称，然后是“…”或分页>>符号。这意味着您必须点击并继续复制所有下一页，以便不会错过任何表格。如果您忘记了，可以安全地重新应用上面的查询，它将为您提供要转换的下一个MyISAM表。 - Dario Fumagalli
28
一行：
mysql -u root -p dbName -e
"show table status where Engine='MyISAM';" | awk
'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}' |
mysql -u root -p dbName
- kuzea
3
1最好和最聪明的答案！ - biniam
当我在bash脚本中运行这个命令时，它将$1解释为bash脚本变量，覆盖了NR的定义。有什么解决方法吗？ - Works for a Living
@WorksforaLiving，将
"$1"用反引号括起来，就像我的答案中所示的那样：\"$1"``。 - Vijay Varadan24
在下面的脚本中，将<username>、<password>和<schema>替换为您的具体数据。
要显示可复制粘贴到mysql客户端会话中的语句，请输入以下内容：
要显示可复制粘贴到mysql客户端会话中的语句，请输入以下内容：
echo 'SHOW TABLES;' \
| mysql -u <username> --password=<password> -D <schema> \
| awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
| column -t \
要执行此更改，只需使用以下命令：
echo 'SHOW TABLES;' \
| mysql -u <username> --password=<password> -D <schema> \
| awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
| column -t \
| mysql -u <username> --password=<password> -D <schema>
版权归属：这是此文章中概述的变体。
- Vijay Varadan
21
在phpMyAdmin中将其作为SQL查询使用
SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=InnoDB;')
FROM information_schema.tables
WHERE engine = 'MyISAM';
- Zwarmapapa
8
4这似乎并没有真正将表转换为InnoDB。 - Charlie Schliesser
4这会输出一个脚本，你需要运行它来转换表格——这是两个步骤。它试图转换INFORMATION_SCHEMA表格，这是不好的事情。需要将其限制在正确的数据库中。 - Brilliand
1你需要过滤掉内部的MySQL表 - 根据文档所述，“不要将mysql数据库中的MySQL系统表（例如user或host）转换为InnoDB类型。这是一项不受支持的操作。系统表必须始终是MyISAM类型。” [链接]（http://dev.mysql.com/doc/refman/5.7/en/converting-tables-to-innodb.html） - eug
这个很好用，而且很安全。用户可以复制并粘贴“Alter Table”。 - johnny
1非常好。我还使用以下查询转义了表名并忽略了系统表：
SELECT CONCAT('ALTER TABLE `',table_schema,'`.`',table_name,'` engine=InnoDB;') FROM information_schema.tables WHERE engine = 'MyISAM' and table_schema not in ('information_schema','mysql','performance_schema','sys'); - Marty Sama显示剩余3条评论
20
你可以在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用户名 -p。 结果是一个 SQL 脚本，您可以将其传回 MySQL：
mysql name-of-database < convert.sql
在上述语句和命令行中替换"name-of-database"。
- Hendrik Brummermann
7
2没错。但是你提到了“MySQL命令行工具”。 - itsraja
1此外，echo "SELECT concat(concat('ALTER TRABLE ', TABLE_NAME), ' ENGINE=InnoDB;')
FROM TABLES
WHERE ENGINE != 'InnoDB' AND TABLE_TYPE='BASE TABLE'
AND TABLE_SCHEMA='testinno'" | mysql -u root --sock=/opt/lampp/var/mysql/mysql.sock --database=testinno > convert.sql
ERROR 1146 (42S02) at line 1: Table 'testinno.TABLES' doesn't exist - itsraja
我已经将此放入示例shell脚本中：http://shrubbery.mynetgear.net/c/display/W/Shell+and+MySQL#ShellandMySQL-ConvertMyISAMtablestoInnoDB - Joshua Davis
1我们如何将 SQL 语句正确地转义为字符串？目前的情况是，我得到了“-bash: ,TABLE_NAME,: command not found”这个错误。 - arjan
纯SQL在MySQL Workbench中表现很好。复制所有“未引用”的行并在新编辑器中运行这些行。这样你就可以先看到所有的行并进行检查，然后再运行。 - Sarah Trees
显示剩余2条评论
17
非常简单，只有两个步骤。
复制、粘贴并运行以下代码：
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选项卡中，然后在下面的行中粘贴。)
START TRANSACTION;
COMMIT;
例如:
START TRANSACTION;
ALTER TABLE `admin_files` ENGINE=InnoDB;
COMMIT;
- Sachin from Pune
14
要为所有非系统模式中的所有表生成ALTER语句，并按这些模式/表的顺序运行以下命令：
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;
之后，通过客户端运行这些查询以执行更改。
- 答案基于上面的答案，但改进了架构处理。
- Lavi Avigdor
11
还没有提到，所以我会为后人写下：
如果您正在迁移数据库服务器（或有其他原因需要转储和重新加载数据），您可以修改 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
（另外，根据我的有限经验，这可能比实时修改表格要快得多。这可能取决于数据和索引的类型。）
- Quinn Comendant
1
1谢谢！这正是我所需要的。我会在几天内测试它。 - Rainer
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接