entityMap|blocks|key|4uuq9|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data^0^^$0|$]|1|@$2|3|4|-4|5|6|7|B|8|@]|9|@]|A|$]]]]

<pre><code>&lt;?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);
 }
?&gt;
</code></pre>

entityMap|blocks|key|412qf|text|运行此SQL语句(在MySQL客户端、phpMyAdmin或任何地方)以检索数据库中的所有MyISAM表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|a6hio|属性的值替换|1clg|变量与您的数据库名称。|6fbar|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;|code-block|syntax|javascript|edlhh|然后，复制输出并作为新的SQL查询运行。^0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|N|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|O|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|P|9|@]|A|@]|B|$]]|$2|G|4|H|6|I|8|Q|9|@]|A|@]|B|$J|K]]|$2|L|4|M|6|7|8|R|9|@]|A|@]|B|$]]]]

Run this SQL statement (in the MySQL client, phpMyAdmin, or wherever) to retrieve all the MyISAM tables in your database.

Replace value of the <code>name_of_your_db</code> variable with your database name.

<pre><code>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;
</code></pre>

Then, copy the output and run as a new SQL query.

entityMap|blocks|key|65u9l|text|SELECT+CONCAT('ALTER+TABLE+',TABLE_NAME,'+ENGINE=InnoDB;')+
FROM+INFORMATION_SCHEMA.TABLES
WHERE+ENGINE='MyISAM'
AND+table_schema+=+'mydatabase';|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|5uhqk|就像一个护身符。|unstyled|a2r1h|这将为您提供包含可在批处理中运行的alter查询的所有表的列表^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|J|9|@]|A|@]|B|$C|D]]|$2|E|4|F|6|G|8|K|9|@]|A|@]|B|$]]|$2|H|4|I|6|G|8|L|9|@]|A|@]|B|$]]]]

<pre><code>SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') 
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='MyISAM'
AND table_schema = 'mydatabase';
</code></pre>

Works like a charm.

This will give you list of all tables with the alter queries that you can run in a batch

entityMap|blocks|key|1flqg|text|一行：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|aars0|mysql+-u+root+-p+dbName+-e+
+"show+table+status+where+Engine='MyISAM';"+%7C+awk+
+'NR>1+{print+"ALTER+TABLE+"$1"+ENGINE+=+InnoDB;"}'++%7C+
++mysql+-u+root+-p+dbName|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

One line:

<pre><code> mysql -u root -p dbName -e 
 "show table status where Engine='MyISAM';" | awk 
 'NR&gt;1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}' | 
 mysql -u root -p dbName
</code></pre>

entityMap|blocks|key|86dr7|text|在下面的脚本中，将和替换为您的特定数据。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1h9r|要显示可以复制粘贴到mysql客户端会话中的语句，请键入以下内容：|2c41o|echo+'SHOW+TABLES;'+\
+%7C+mysql+-u++--password=+-D++\
+%7C+awk+'!/%5ETables_in_/+{print+"ALTER+TABLE+`"$0"`+ENGINE+=+InnoDB;"}'+\
+%7C+column+-t+\|code-block|syntax|javascript|ds7ee|要简单地执行更改，请使用以下命令：|9m6nk|echo+'SHOW+TABLES;'+\
+%7C+mysql+-u++--password=+-D++\
+%7C+awk+'!/%5ETables_in_/+{print+"ALTER+TABLE+`"$0"`+ENGINE+=+InnoDB;"}'+\
+%7C+column+-t+\
+%7C+mysql+-u++--password=+-D|22g6s|来源:这是在|4jqbm|本文|bv1g5|..。^0|0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|T|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|U|9|@]|A|@]|B|$]]|$2|E|4|F|6|G|8|V|9|@]|A|@]|B|$H|I]]|$2|J|4|K|6|7|8|W|9|@]|A|@]|B|$]]|$2|L|4|M|6|G|8|X|9|@]|A|@]|B|$H|I]]|$2|N|4|O|6|7|8|Y|9|@]|A|@]|B|$]]|$2|P|4|Q|6|7|8|Z|9|@]|A|@]|B|$]]|$2|R|4|S|6|7|8|10|9|@]|A|@]|B|$]]]]

In the scripts below, replace &lt;username&gt;, &lt;password&gt; and &lt;schema&gt; with your specific data.

To show the statements that you can copy-paste into a mysql client session type the following:

<pre><code>echo 'SHOW TABLES;' \
 | mysql -u &lt;username&gt; --password=&lt;password&gt; -D &lt;schema&gt; \
 | awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
 | column -t \
</code></pre>

To simply execute the change, use this:

<pre><code>echo 'SHOW TABLES;' \
 | mysql -u &lt;username&gt; --password=&lt;password&gt; -D &lt;schema&gt; \
 | awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
 | column -t \
 | mysql -u &lt;username&gt; --password=&lt;password&gt; -D &lt;schema&gt;
</code></pre>

CREDIT: This is a variation of what was outlined in <a href="http://kevin.vanzonneveld.net/techblog/article/convert_all_tables_to_innodb_in_one_go/" rel="noreferrer">this article</a>.

entityMap|blocks|key|am8bp|text|将其用作phpMyAdmin中的sql查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|c7qrk|SELECT+CONCAT('ALTER+TABLE+',table_schema,'.',table_name,'+engine=InnoDB;')+
FROM+information_schema.tables+
WHERE+engine+=+'MyISAM';|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

Use this as a sql query in your phpMyAdmin

<pre><code>SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=InnoDB;') 
FROM information_schema.tables 
WHERE engine = 'MyISAM';
</code></pre>

entityMap|blocks|key|a51d6|text|您可以在mysql命令行工具中执行以下语句：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|32ltq|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'"+%7C+mysql+>+convert.sql|code-block|syntax|javascript|7qls7|您可能需要使用以下命令指定用户名和密码:+mysql+sql+username+-p结果是一个可以通过管道传回mysql的-u脚本：|2c7jb|mysql+name-of-database+<+convert.sql|3dsg9|在上述语句和命令行中替换“数据库名称”。^0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|N|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|O|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|P|9|@]|A|@]|B|$]]|$2|J|4|K|6|E|8|Q|9|@]|A|@]|B|$F|G]]|$2|L|4|M|6|7|8|R|9|@]|A|@]|B|$]]]]

You can execute this statement in the mysql command line tool:

<pre><code>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 &gt; convert.sql
</code></pre>

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:

<pre><code>mysql name-of-database &lt; convert.sql
</code></pre>

Replace "name-of-database" in the above statement and command line.

entityMap|blocks|key|1bfip|text|要为所有非系统模式中的所有表生成ALTER语句，请运行以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|99prd|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;|code-block|syntax|javascript|7vd17|之后，通过客户端运行这些查询以执行更改。|219i3|Answer基于上述答案，但改进了模式处理。^0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|L|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|M|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|N|9|@]|A|@]|B|$]]|$2|J|4|K|6|7|8|O|9|@]|A|@]|B|$]]]]

To generate ALTER statements for all tables in all the non-system schemas, ordered by those schemas/tables run the following:

<pre><code>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;
</code></pre>

After that, run those queries via a client to perform the alteration.

<ul>
<li>Answer is based on above answers, but improves schema handling.</li>
</ul>

entityMap|blocks|key|4mo15|text|这很简单，只有两个步骤:复制和粘贴：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|9auar|第1步。|9gpmi|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;|code-block|syntax|javascript|3o3uo|(复制并粘贴sql选项卡中的所有结果)|5e6nn|步骤2：(将所有结果复制到sql选项卡中)并将其粘贴到下面的行中|9elll|启动事务；|abos3|提交；|7mojr|例如：启动事务；|4hhp1|ALTER+TABLE|akmea|ENGINE=InnoDB；|akhsk^0|0|0|0|0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|Y|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|Z|9|@]|A|@]|B|$]]|$2|E|4|F|6|G|8|10|9|@]|A|@]|B|$H|I]]|$2|J|4|K|6|7|8|11|9|@]|A|@]|B|$]]|$2|L|4|M|6|7|8|12|9|@]|A|@]|B|$]]|$2|N|4|O|6|7|8|13|9|@]|A|@]|B|$]]|$2|P|4|Q|6|7|8|14|9|@]|A|@]|B|$]]|$2|R|4|S|6|7|8|15|9|@]|A|@]|B|$]]|$2|T|4|U|6|7|8|16|9|@]|A|@]|B|$]]|$2|V|4|W|6|7|8|17|9|@]|A|@]|B|$]]|$2|X|4|Q|6|7|8|18|9|@]|A|@]|B|$]]]]

It's Very simple there are only TWO step just copy and paste :

step 1.

<pre><code> 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;
</code></pre>

(copy and paste all result in in sql tab)

step 2:
(copy all result in in sql tab)
and paste below in the line

START TRANSACTION;

COMMIT;

eg.
START TRANSACTION;

ALTER TABLE <code>admin_files</code> ENGINE=InnoDB;

COMMIT;

entityMap|blocks|key|e5e6r|text|它还没有被提及，所以我将它写给后人：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|6sk6k|如果您正在DB服务器之间迁移(或者出于其他原因，您需要转储和重新加载dta)，则只需修改以下命令的输出即可|edite|mysqldump+--no-data+DBNAME+%7C+sed+'s/ENGINE=MyISAM/ENGINE=InnoDB/'+>+my_schema.sql;
mysqldump+--no-create-info+DBNAME+>+my_data.sql;|code-block|syntax|javascript|bf9rm|然后再次加载：|fgbhl|mysql+DBNAME+<+my_schema.sql+&&+mysql+DBNAME+<+my_data.sql|fbn7k|(而且，在我有限的经验中，这可能是一个比‘实时’更改表快得多的过程。这可能取决于数据和索引的类型。)^0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|P|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|Q|9|@]|A|@]|B|$]]|$2|E|4|F|6|G|8|R|9|@]|A|@]|B|$H|I]]|$2|J|4|K|6|7|8|S|9|@]|A|@]|B|$]]|$2|L|4|M|6|G|8|T|9|@]|A|@]|B|$H|I]]|$2|N|4|O|6|7|8|U|9|@]|A|@]|B|$]]]]

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 <code>mysqldump</code>:

<pre><code>mysqldump --no-data DBNAME | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' &gt; my_schema.sql;
mysqldump --no-create-info DBNAME &gt; my_data.sql;
</code></pre>

Then load it again:

<pre><code>mysql DBNAME &lt; my_schema.sql &amp;&amp; mysql DBNAME &lt; my_data.sql
</code></pre>

(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.)

entityMap|blocks|key|dua4k|text|以下是Django用户的一种方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|f6ktg|from+django.core.management.base+import+BaseCommand
from+django.db+import+connections


class+Command(BaseCommand):

++++def+handle(self,+database="default",+*args,+**options):

++++++++cursor+=+connections[database].cursor()

++++++++cursor.execute("SHOW+TABLE+STATUS");

++++++++for+row+in+cursor.fetchall():
++++++++++++if+row[1]+!=+"InnoDB":
++++++++++++++++print+"Converting+%25s"+%25+row[0],
++++++++++++++++result+=+cursor.execute("ALTER+TABLE+%25s+ENGINE=INNODB"+%25+row[0])
++++++++++++++++print+result|code-block|syntax|javascript|t3rc|将其添加到文件夹管理/命令/下的应用程序中，然后您可以使用manage.py命令转换所有表：|fvb44|python+manage.py+convert_to_innodb^0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|L|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|M|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|N|9|@]|A|@]|B|$]]|$2|J|4|K|6|E|8|O|9|@]|A|@]|B|$F|G]]]]

Here is a way to do it for Django users:

<pre><code>from django.core.management.base import BaseCommand
from django.db import connections


class Command(BaseCommand):

 def handle(self, database="default", *args, **options):

 cursor = connections[database].cursor()

 cursor.execute("SHOW TABLE STATUS");

 for row in cursor.fetchall():
 if row[1] != "InnoDB":
 print "Converting %s" % row[0],
 result = cursor.execute("ALTER TABLE %s ENGINE=INNODB" % row[0])
 print result
</code></pre>

Add that to your app under the folders management/commands/ Then you can convert all your tables with a manage.py command:

<pre><code>python manage.py convert_to_innodb
</code></pre>

entityMap|blocks|key|djada|text|一个普通的MySQL版本。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|fch0k|您可以简单地启动mysql可执行文件，使用数据库并复制粘贴查询。|f5uh3|这会将当前数据库中的所有MyISAM表转换为INNODB表。|3apbg|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;|code-block|syntax|javascript^0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|L|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|M|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|N|9|@]|A|@]|B|$]]|$2|G|4|H|6|I|8|O|9|@]|A|@]|B|$J|K]]]]

A plain MySQL Version.

You can simply start mysql executable, use database and copy-paste the query.

This will convert all MyISAM tables in the current Database into INNODB tables.

<pre class="lang-mysql prettyprint-override"><code>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;
</code></pre>

entityMap|blocks|key|92lpr|text|在mysql中，您可以使用文本编辑器进行搜索/替换：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1q7bd|SELECT+table_schema,+table_name+FROM+INFORMATION_SCHEMA.TABLES+WHERE+engine+=+'myisam';|code-block|syntax|javascript|6qre5|注意:您可能应该忽略信息|cu8uv|_|3pqli|模式和mysql，因为“mysql和信息|d9knh|562fs|实现某些MySQL内部结构的模式数据库仍然使用MyISAM。特别是，您不能将授权表切换为使用InnoDB。“(|ekmc5|http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html|d2uq8|)|34li8|在任何情况下，请注意要忽略并运行的表：|o1bk|SELECT+table_name+FROM+INFORMATION_SCHEMA.TABLES+WHERE+engine+=+'myisam';|ckqum|现在只需将该列表复制/粘贴到文本编辑器中，并搜索/替换"%7C“为"ALTER+TABLE”等。|80r8l|然后你会有一个这样的列表，你可以简单地粘贴到你的mysql终端：|1bkpt|ALTER+TABLE+arth_commentmeta+++++++++++ENGINE=Innodb;
ALTER+TABLE+arth_comments++++++++++++++ENGINE=Innodb;
ALTER+TABLE+arth_links+++++++++++++++++ENGINE=Innodb;
ALTER+TABLE+arth_options+++++++++++++++ENGINE=Innodb;
ALTER+TABLE+arth_postmeta++++++++++++++ENGINE=Innodb;
ALTER+TABLE+arth_posts+++++++++++++++++ENGINE=Innodb;
ALTER+TABLE+arth_term_relationships++++ENGINE=Innodb;
ALTER+TABLE+arth_term_taxonomy+++++++++ENGINE=Innodb;
ALTER+TABLE+arth_terms+++++++++++++++++ENGINE=Innodb;
ALTER+TABLE+arth_usermeta++++++++++++++ENGINE=Innodb;|co3ks|如果您的文本编辑器不能轻松做到这一点，这里有另一个解决方案，可以从linux终端获取数据库的一个前缀的类似列表(可以粘贴到mysql中)：|btc3m|mysql+-u+[username]+-p[password]+-B+-N+-e+'show+tables+like+"arth_%25"'+[database+name]+%7C+xargs+-I+'{}'+echo+"ALTER+TABLE+{}+ENGINE=INNODB;"^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|18|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|19|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|1A|9|@]|A|@]|B|$]]|$2|J|4|K|6|7|8|1B|9|@]|A|@]|B|$]]|$2|L|4|M|6|7|8|1C|9|@]|A|@]|B|$]]|$2|N|4|K|6|7|8|1D|9|@]|A|@]|B|$]]|$2|O|4|P|6|7|8|1E|9|@]|A|@]|B|$]]|$2|Q|4|R|6|7|8|1F|9|@]|A|@]|B|$]]|$2|S|4|T|6|7|8|1G|9|@]|A|@]|B|$]]|$2|U|4|V|6|7|8|1H|9|@]|A|@]|B|$]]|$2|W|4|X|6|E|8|1I|9|@]|A|@]|B|$F|G]]|$2|Y|4|Z|6|7|8|1J|9|@]|A|@]|B|$]]|$2|10|4|11|6|7|8|1K|9|@]|A|@]|B|$]]|$2|12|4|13|6|E|8|1L|9|@]|A|@]|B|$F|G]]|$2|14|4|15|6|7|8|1M|9|@]|A|@]|B|$]]|$2|16|4|17|6|E|8|1N|9|@]|A|@]|B|$F|G]]]]

From inside mysql, you could use search/replace using a text editor:

<pre><code>SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';
</code></pre>

Note: You should probably ignore information_schema and mysql because "The mysql and information_schema databases, that implement some of the MySQL internals, still use MyISAM. In particular, you cannot switch the grant tables to use InnoDB." ( <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html</a> )

In any case, note the tables to ignore and run:

<pre><code>SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';
</code></pre>

Now just copy/paste that list into your text editor and search/replace "|" with "ALTER TABLE" etc.

You'll then have a list like this you can simply paste into your mysql terminal:

<pre><code>ALTER TABLE arth_commentmeta ENGINE=Innodb;
ALTER TABLE arth_comments ENGINE=Innodb;
ALTER TABLE arth_links ENGINE=Innodb;
ALTER TABLE arth_options ENGINE=Innodb;
ALTER TABLE arth_postmeta ENGINE=Innodb;
ALTER TABLE arth_posts ENGINE=Innodb;
ALTER TABLE arth_term_relationships ENGINE=Innodb;
ALTER TABLE arth_term_taxonomy ENGINE=Innodb;
ALTER TABLE arth_terms ENGINE=Innodb;
ALTER TABLE arth_usermeta ENGINE=Innodb;
</code></pre>

If your text editor can't do this easily, here's another solution for getting a similar list (that you can paste into mysql) for just one prefix of your database, from linux terminal:

<pre><code>mysql -u [username] -p[password] -B -N -e 'show tables like "arth_%"' [database name] | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;"
</code></pre>

entityMap|blocks|key|eph5f|text|刚刚测试了另一个(简单?)方式，并为我工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|7dvqa|只需导出您的数据库为.sql文件，编辑-它与gedit或记事本；|c7oc7|替换|129f3|使用|4vvvr|并保存编辑后的文件|55jsc|完成的数量或替换应该是您的表的数量|52jcg|将其导入到MySQL+(phpMyAdmin或命令行)|a0ii1|瞧！^0|0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|Q|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|R|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|S|9|@]|A|@]|B|$]]|$2|G|4|H|6|7|8|T|9|@]|A|@]|B|$]]|$2|I|4|J|6|7|8|U|9|@]|A|@]|B|$]]|$2|K|4|L|6|7|8|V|9|@]|A|@]|B|$]]|$2|M|4|N|6|7|8|W|9|@]|A|@]|B|$]]|$2|O|4|P|6|7|8|X|9|@]|A|@]|B|$]]]]

Just tested another (simple ?) way, and worked for me.

Just export your DB as .sql file, edit-it with gedit or notepad;

Replace <code>ENGINE=MyISAM</code> with <code>ENGINE=INNODB</code> and Save the file edited

Number or replacement done should be the number of your tables

Import it to MySQL (phpMyAdmin or command line)

And Voila !

entityMap|blocks|key|24rr4|text|使用此行更改单个表的数据库引擎。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|9vmb1|ALTER+TABLE+table_name+ENGINE+=+INNODB;|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

use this line to alter the database engine for single table.

<pre><code> ALTER TABLE table_name ENGINE = INNODB;
</code></pre>

entityMap|blocks|key|faegc|text|我是一个新手，必须找到我自己的解决方案，因为web上的mysql命令通常充满了拼写错误，这给刚刚开始工作的人带来了现实生活中的噩梦。这是我的解决方案..。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2ginb|我用excel一次准备了几十个命令(准备好复制和粘贴)，而不是每个表只有一个命令。|ecfht|怎么做？展开putty窗口并输入mysql，然后运行命令"SHOW+TABLE+STATUS;“并将输出复制/粘贴到microsoft+excel。转到Data选项卡，使用"text+to+columns“功能并使用空格键分隔列。然后按显示您的表类型的列对列进行排序，并删除表已经采用InnoDb格式的所有行(因为我们不需要对它们运行命令，它们已经完成了)。然后在tables列的左侧添加2列，在右侧添加2列。然后将命令的第一部分粘贴到第-1列(见下文)。第2列应该只包含一个空格。第3列是您的tables列。第4列应该只包含一个空格。第5列是命令的最后一部分。它应该看起来像这样：|6pq8s|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;|code-block|syntax|javascript|7s43n|然后一次复制并粘贴大约5行到mysql中。这将一次转换大约5个。我注意到，如果我一次做了更多的操作，那么命令就会失败。^0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|N|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|O|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|P|9|@]|A|@]|B|$]]|$2|G|4|H|6|I|8|Q|9|@]|A|@]|B|$J|K]]|$2|L|4|M|6|7|8|R|9|@]|A|@]|B|$]]]]

I'm a newbie and had to find my own solution because mysql commands on the web are usually riddled with misspellings create a real life nightmare for people just starting out. Here is my solution....

Instead of in 1 command per table, I prepared dozens of commands (ready to copy and paste) at once using 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:

<pre><code>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;
</code></pre>

Then copy and paste about 5 rows at a time into mysql. This will convert about 5 at once. I noticed if I did more than that at once then the commands would fail.

entityMap|blocks|key|3mo74|text|在我的例子中，我从一个默认为MyISAM的MySQL实例迁移到一个默认为InnoDB的MariaDB实例。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|377js|根据MariaDB迁移文档。|2ch20|在旧服务器上运行：|dmpes|mysqldump+-u+root+-p+--skip-create-options+--all-databases+>+migration.sql|code-block|syntax|javascript|jbsq|--skip-create-选项可确保数据库服务器在加载数据时使用默认存储引擎，而不是MyISAM。|6ji4c|mysql+-u+root+-p+<+migration.sql|a54bh|这抛出了一个关于创建mysql.db的错误，但现在一切正常:)^0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|R|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|S|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|T|9|@]|A|@]|B|$]]|$2|G|4|H|6|I|8|U|9|@]|A|@]|B|$J|K]]|$2|L|4|M|6|7|8|V|9|@]|A|@]|B|$]]|$2|N|4|O|6|I|8|W|9|@]|A|@]|B|$J|K]]|$2|P|4|Q|6|7|8|X|9|@]|A|@]|B|$]]]]

In my case, I was migrating from a MySQL instance with a default of MyISAM, to a MariaDB instance with a DEFAULT of InnoDB.

<a href="http://galeracluster.com/documentation-webpages/migration.html" rel="nofollow">Per MariaDB Migration Doc's.</a>

On old Server Run :

<pre><code>mysqldump -u root -p --skip-create-options --all-databases &gt; migration.sql
</code></pre>

The --skip-create-options ensures that the database server uses the default storage engine when loading the data, instead of MyISAM.

<pre><code>mysql -u root -p &lt; migration.sql
</code></pre>

This threw an error regarding creating mysql.db, but everything works great now :)

entityMap|blocks|key|29j39|text|你可以用你最喜欢的脚本语言写一个脚本。该脚本将执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|alfnj|问题|5jqc8|..。|df90h|对于返回的每一行，检查第二列是否显示|fvtu6|而不是|bbs32|46r86|如果不是|9fsos|，发出相应的|8h916|命令。^0|0|0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|R|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|S|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|T|9|@]|A|@]|B|$]]|$2|G|4|H|6|7|8|U|9|@]|A|@]|B|$]]|$2|I|4|J|6|7|8|V|9|@]|A|@]|B|$]]|$2|K|4|F|6|7|8|W|9|@]|A|@]|B|$]]|$2|L|4|M|6|7|8|X|9|@]|A|@]|B|$]]|$2|N|4|O|6|7|8|Y|9|@]|A|@]|B|$]]|$2|P|4|Q|6|7|8|Z|9|@]|A|@]|B|$]]]]

You could write a script to do it in your favourite scripting language. The script would do the following:

<ol><li>Issue <code>SHOW FULL TABLES</code>.</li><li>For each row returned, check that the second column says <code>'BASE TABLE'</code> and not <code>'VIEW'</code>.</li><li>If it is not <code>'VIEW'</code>, issue the appropriate <code>ALTER TABLE</code> command.</li></ol>

entityMap|blocks|key|co0i7|text|尝试此shell脚本|type|unstyled|depth|inlineStyleRanges|entityRanges|data|67nqd|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;"+%7C+while+read+TABLE+;+do+mysql+-h$DBHOST+-u$DBUSER+-p$DBPASS+$DBNAME+-Nse+"ALTER+TABLE+$TABLE+ENGINE=$DBENGINE;"+;+done|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

Try this shell script

<pre><code>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 &gt; 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
</code></pre>

entityMap|blocks|key|2hhds|text|此util脚本的一些修复|type|unstyled|depth|inlineStyleRanges|entityRanges|data|f59bi|SET+@DATABASE_NAME+=+'Integradb';

SELECT++CONCAT('ALTER+TABLE+',+table_schema,+'.',+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;|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

Some fixes to this util script

<pre><code>SET @DATABASE_NAME = 'Integradb';

SELECT CONCAT('ALTER TABLE ', table_schema, '.', 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;
</code></pre>

entityMap|blocks|key|ar540|text|{$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");
++++}
++}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript^0^^$0|$]|1|@$2|3|4|5|6|7|8|E|9|@]|A|@]|B|$C|D]]]]

<pre><code>&lt;?php

 // connect your database here first

 mysql_connect('host', 'user', 'pass');

 $databases = mysql_query('SHOW databases');

 while($db = mysql_fetch_array($databases)) {
 echo &quot;database =&gt; {$db[0]}\n&quot;;
 mysql_select_db($db[0]);

 $tables = mysql_query('SHOW tables');

 while($tbl = mysql_fetch_array($tables)) {
 echo &quot;table =&gt; {$tbl[0]}\n&quot;;
 mysql_query(&quot;ALTER TABLE {$tbl[0]} ENGINE=InnoDB&quot;);
 }
 }
</code></pre>

entityMap|blocks|key|4mmtd|text|这是一个简单的php脚本。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|fbbvc|";
++++++++++++}
++++}
++++if+($count+==+$ok){
++++++++++++echo+'ALL+OK';
++++}else{
++++++++++++echo+'ERRORSTotal+tables:+'.$count.',+updated+tables:'.$ok.'';
++++}|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

This is a simple php script.

<pre><code>&lt;?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."&lt;hr/&gt;";
 }
 }
 if ($count == $ok){
 echo '&lt;div style="color: green"&gt;&lt;b&gt;ALL OK&lt;/b&gt;&lt;/div&gt;';
 }else{
 echo '&lt;div style="color: red"&gt;&lt;b&gt;ERRORS&lt;/b&gt;Total tables: '.$count.', updated tables:'.$ok.'&lt;/div&gt;';
 }
</code></pre>

entityMap|blocks|key|ns70|text|\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:+%25s\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+%25s\n",+$tbl[0]);
++++++++if(mysql_query("ALTER+TABLE+`{$tbl[0]}`+ENGINE+=+INNODB")+===+false)
++++++++++++printf("---+---+Error+altering+table:+{$tbl[0]}.+"+.+mysql_error()+.+"\n");
++++}
}

mysql_close();

?>|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript^0^^$0|$]|1|@$2|3|4|5|6|7|8|E|9|@]|A|@]|B|$C|D]]]]

<pre><code>&lt;?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 &lt; 4)
 exit("Usage: {$argv[0]} &lt;host&gt; &lt;username&gt; &lt;password&gt;\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();

?&gt;
</code></pre>

entityMap|blocks|key|4gdum|text|对于mysqli连接；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|8r59u|query($sql);

while($row+=+$rs->fetch_array())
{
++++$tbl+=+$row[0];
++++$sql+=+"ALTER+TABLE+`$tbl`+ENGINE=INNODB";
++++$connect->query($sql);
}+?>|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

for mysqli connect;

<pre><code>&lt;?php

$host = "host";
$user = "user";
$pass = "pss";
$database = "db_name";


$connect = new mysqli($host, $user, $pass, $database); 

// Actual code starts here Dont forget to change db_name !!
$sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'db_name' 
 AND ENGINE = 'MyISAM'";

$rs = $connect-&gt;query($sql);

while($row = $rs-&gt;fetch_array())
{
 $tbl = $row[0];
 $sql = "ALTER TABLE `$tbl` ENGINE=INNODB";
 $connect-&gt;query($sql);
} ?&gt;
</code></pre>

entityMap|blocks|key|1vj3t|text|还有另一种选择..。下面是如何在ansible中做到这一点。它假定数据库的名称在|type|unstyled|depth|inlineStyleRanges|entityRanges|data|9t86k|并且您已经配置了访问权限。|45os2|-+name:+Get+list+of+DB+tables+that+need+converting+to+InnoDB
++command:+>
++++mysql+--batch+--skip-column-names+--execute="SELECT+TABLE_NAME
++++FROM+information_schema.TABLES
++++WHERE+TABLE_SCHEMA+=+'{{+dbname+}}'+AND+ENGINE+=+'MyISAM';"
++register:+converttables
++check_mode:+no
++changed_when:+False

-+name:+Convert+any+unconverted+tables
++command:+>
++++mysql+--batch+--skip-column-names+--execute="ALTER+TABLE+`{{+dbname+}}`.`{{+item+}}`+ENGINE+=+InnoDB;"
++with_items:+"{{+converttables.stdout_lines+}}"|code-block|syntax|javascript^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|J|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|K|9|@]|A|@]|B|$]]|$2|E|4|F|6|G|8|L|9|@]|A|@]|B|$H|I]]]]

Yet another option... Here's how to do it in ansible. It assumes that the name of your database is in <code>dbname</code> and that you have already configured access.

<pre><code>- name: Get list of DB tables that need converting to InnoDB
 command: &gt;
 mysql --batch --skip-column-names --execute="SELECT TABLE_NAME
 FROM information_schema.TABLES
 WHERE TABLE_SCHEMA = '{{ dbname }}' AND ENGINE = 'MyISAM';"
 register: converttables
 check_mode: no
 changed_when: False

- name: Convert any unconverted tables
 command: &gt;
 mysql --batch --skip-column-names --execute="ALTER TABLE `{{ dbname }}`.`{{ item }}` ENGINE = InnoDB;"
 with_items: "{{ converttables.stdout_lines }}"
</code></pre>

entityMap|blocks|key|3eeug|text|对于转换MySql表存储引擎，有一种方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|d62ql|使用MySql命令，如下所示，转换为innodb+(|feeg6|)或(|2si05|)对于myisam+(您应该对每个单独的表执行此操作，t1表示表名)。|67uhq|编写一个在所有表上循环的脚本，然后运行alter命令|d8eco|使用已有的脚本来处理此问题：|80msm|https://github.com/rafihaidari/convert-mysql-tables-storage-engine^0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|O|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|P|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|Q|9|@]|A|@]|B|$]]|$2|G|4|H|6|7|8|R|9|@]|A|@]|B|$]]|$2|I|4|J|6|7|8|S|9|@]|A|@]|B|$]]|$2|K|4|L|6|7|8|T|9|@]|A|@]|B|$]]|$2|M|4|N|6|7|8|U|9|@]|A|@]|B|$]]]]

For converting MySql tables storage engine there is a number way:
<ol>
<li>Use MySql commands as follow, for converting to innodb (<code>ALTER TABLE t1 ENGINE = InnoDB</code>) or (<code>ALTER TABLE t1 ENGINE = MyISAM</code>) for myisam (You should do this for each individual tables, t1 is for table name.).</li>
<li>Write a script that loop on all tables and run the alter command</li>
<li>Use an already available script to handle that: <a href="https://github.com/rafihaidari/convert-mysql-tables-storage-engine" rel="nofollow noreferrer">https://github.com/rafihaidari/convert-mysql-tables-storage-engine</a></li>
</ol>

entityMap|blocks|key|eend7|text|如果您使用的是Windows，则可以使用以下循环在批处理文件中完成此操作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|qil6|set+database=YOURDATABASENAME
for+/F+"tokens=1+skip=1+usebackq"+%25%25a+in+(`mysql+%25%25database%25%25+-e+"show+table+status+where+Engine+!=+'InnoDB';"`)+do+(
++++mysql+%25database%25+-e+"ALTER+TABLE+%25%25a+ENGINE+=+'InnoDB';"
)|code-block|syntax|javascript|32hr6|只需更改|e382i|设置为目标或使用的数据库的名称|9mcdh|通过命令行传递数据库名称。|4e2es|当前不是的每个表|3dus4|将转换为|8a9qi|..。如果您想专门针对|eshme|正如问题所暗示的那样，以下代码仅具有更新后的MySQL条件|4p365|..。|cumcc|set+database=YOURDATABASENAME
for+/F+"tokens=1+skip=1+usebackq"+%25%25a+in+(`mysql+%25%25database%25%25+-e+"show+table+status+where+Engine+=+'MyISAM';"`)+do+(
++++mysql+%25database%25+-e+"ALTER+TABLE+%25%25a+ENGINE+=+'InnoDB';"
)^0|0|0|0|0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|Z|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|10|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|11|9|@]|A|@]|B|$]]|$2|J|4|K|6|7|8|12|9|@]|A|@]|B|$]]|$2|L|4|M|6|7|8|13|9|@]|A|@]|B|$]]|$2|N|4|O|6|7|8|14|9|@]|A|@]|B|$]]|$2|P|4|Q|6|7|8|15|9|@]|A|@]|B|$]]|$2|R|4|S|6|7|8|16|9|@]|A|@]|B|$]]|$2|T|4|U|6|7|8|17|9|@]|A|@]|B|$]]|$2|V|4|W|6|7|8|18|9|@]|A|@]|B|$]]|$2|X|4|Y|6|E|8|19|9|@]|A|@]|B|$F|G]]]]

If you are using Windows you can accomplish this inside a batch file with the following loop.
<pre><code>set database=YOURDATABASENAME
for /F &quot;tokens=1 skip=1 usebackq&quot; %%a in (`mysql %%database%% -e &quot;show table status where Engine != 'InnoDB';&quot;`) do (
 mysql %database% -e &quot;ALTER TABLE %%a ENGINE = 'InnoDB';&quot;
)
</code></pre>
Simply change the <code>YOURDATABASENAME</code> to the name of the database you are targeting or use <code>%~1</code> to pass the database name via the command line.
Every table which is not currently <code>InooDB</code> will be converted to <code>InnoDB</code>. If you want to specifically target <code>MyISAM</code> as the question suggested, the following code has an updated MySQL conditional for only <code>MyISAM</code>.
<pre><code>set database=YOURDATABASENAME
for /F &quot;tokens=1 skip=1 usebackq&quot; %%a in (`mysql %%database%% -e &quot;show table status where Engine = 'MyISAM';&quot;`) do (
 mysql %database% -e &quot;ALTER TABLE %%a ENGINE = 'InnoDB';&quot;
)
</code></pre>

entityMap|blocks|key|2lo6|text|cd+/var/lib/mysql/DBNAME|type|unstyled|depth|inlineStyleRanges|entityRanges|data|bpf2h|ls+%7C+grep+".frm“%7C+cut+-d".”-f1+%7C+xargs+-I{}+-n1+mysql+-D+DBNAME+-e+"alter+table+{}+ENGINE=INNODB;“-uroot+-pXXXXX^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|E|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|F|9|@]|A|@]|B|$]]]]

cd /var/lib/mysql/DBNAME

ls | grep ".frm" | cut -d"." -f1 | xargs -I{} -n1 mysql -D DBNAME -e "alter table {} ENGINE=INNODB;" -uroot -pXXXXX

I know I can issue an alter table individually to change the table storage from MyISAM to InnoDB.

I am wondering if there is a way to quickly change all of them to InnoDB?

How to convert all tables from MyISAM into InnoDB?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

 我知道我可以单独发出一个alter table来将表存储从MyISAM更改为InnoDB。 我想知道是否有一种方法可以快速地将它们全部更改为InnoDB？

问如何将所有MyISAM表格转换成InnoDB表格？
EN

回答 28

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将所有MyISAM表格转换成InnoDB表格？EN

回答 28

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将所有MyISAM表格转换成InnoDB表格？
EN