mysqli连接;

<?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->query($sql);

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

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

效果非常好。

这将为您提供可以批处理运行的带有alter查询的所有表的列表

运行此SQL语句(在MySQL客户端、phpMyAdmin或任何地方)检索数据库中的所有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查询运行。

只是测试了另一种(简单的?)方法，对我来说很有效。

只需导出您的DB为.sql文件，编辑它与gedit或记事本;

将ENGINE=MyISAM替换为ENGINE=INNODB并保存已编辑的文件

编号或替换完成应该是你的表的编号

导入到MySQL (phpMyAdmin或命令行)

瞧!

还有另一种选择……下面是如何在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 }}"

一行:

 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