当表很大时，最好从控制台进行操作

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 

对于转换MySql表存储引擎有许多方法:

使用MySql命令如下，转换为innodb (ALTER TABLE t1 ENGINE = innodb)或(ALTER TABLE t1 ENGINE = MyISAM)为MyISAM(你应该为每个单独的表这样做，t1是表名。) 编写一个在所有表上循环的脚本，并运行alter命令 使用一个已经可用的脚本来处理这个问题:https://github.com/rafihaidari/convert-mysql-tables-storage-engine

当表很大时，最好从控制台进行操作

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 

如果您使用的是Windows，您可以在批处理文件中使用以下循环完成此任务。

set database=YOURDATABASENAME
for /F "tokens=1 skip=1 usebackq" %%a in (`mysql %%database%% -e "show table status where Engine != 'InnoDB';"`) do (
    mysql %database% -e "ALTER TABLE %%a ENGINE = 'InnoDB';"
)

只需将YOURDATABASENAME更改为目标数据库的名称，或者使用%~1通过命令行传递数据库名称。

所有当前不是InnoDB的表都将被转换为InnoDB。如果您像问题所建议的那样专门针对MyISAM，下面的代码有一个仅针对MyISAM的更新的MySQL条件。

set database=YOURDATABASENAME
for /F "tokens=1 skip=1 usebackq" %%a in (`mysql %%database%% -e "show table status where Engine = 'MyISAM';"`) do (
    mysql %database% -e "ALTER TABLE %%a 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个。我注意到，如果我一次做了更多的操作，那么命令就会失败。

还有另一种选择……下面是如何在ansible中做到这一点。它假设数据库的名称在dbname中，并且您已经配置了访问权限。

- 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 }}"