如何将所有表从MyISAM转换为InnoDB?

您可以用您最喜欢的脚本语言编写一个脚本来完成它。该脚本将执行以下操作:

1. SHOW FULL TABLES问题。
2. 对于返回的每一行，检查第二列是否为__ABC1而不是'VIEW'。
3. 如果不是__ABC2，则发出适当的ALTER TABLE命令。

<?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);
}
?>

你可以在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 name-of-database < convert.sql

替换上述语句和命令行中的"name-of-database"。

<?php


// connect your database here first


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


$databases = mysql_query('SHOW databases');


while($db = mysql_fetch_array($databases)) {
echo "database => {$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");
}
}

在phpMyAdmin中使用此sql查询

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

在以下脚本中，替换<用户名>， <密码>和& lt; schema>你的具体数据。

要显示可以复制粘贴到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>

这是这篇文章中概述的一个变体。

运行此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查询运行。

在mysql中，你可以使用文本编辑器进行搜索/替换:

SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';

注意:你可能应该忽略information_schema和mysql，因为“mysql和information_schema数据库，实现了一些mysql内部，仍然使用MyISAM。特别是，你不能切换授权表来使用InnoDB。”(__abc0)

在任何情况下，请注意要忽略并运行的表:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';

现在只需复制/粘贴列表到你的文本编辑器，搜索/替换“|”与“ALTER TABLE”等。

然后你会有一个这样的列表，你可以简单地粘贴到你的mysql终端:

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;

如果你的文本编辑器不能很容易地做到这一点，这里有另一个解决方案，为一个类似的列表(你可以粘贴到mysql)，你的数据库只有一个前缀，从linux终端:

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

下面是一个为Django用户提供的方法:

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

将它添加到你的应用程序的文件夹管理/命令/下，然后你可以用manage.py命令转换所有的表:

python manage.py convert_to_innodb

使用这一行更改单个表的数据库引擎。

  ALTER TABLE table_name ENGINE = INNODB;

试试这个shell脚本

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;" | while read TABLE ; do mysql -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME -Nse "ALTER TABLE $TABLE ENGINE=$DBENGINE;" ; done

我是一个新手，必须找到自己的解决方案，因为网上的mysql命令通常充满了拼写错误，这对刚开始使用的人来说是一个现实生活的噩梦。这是我的解决方案....

我用excel一次准备了几十个命令(可以复制和粘贴)，而不是每个表一个命令。

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个。我注意到，如果我一次做了更多的操作，那么命令就会失败。

这是一个简单的php脚本。

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

一行:

 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

它还没有被提及，所以我将为后人写下来:

如果你在DB服务器之间迁移(或者有其他原因你要转储和重新加载你的dta)，你可以修改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

(此外，根据我有限的经验，这可能是一个比“实时”修改表快得多的过程。这可能取决于数据和索引的类型。)

对这个util脚本的一些修复

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;

一个简单的MySQL版本。

你可以简单地启动mysql可执行文件，使用数据库和复制粘贴查询。

这将把当前数据库中的所有MyISAM表转换为INNODB表。

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;

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

效果非常好。

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

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


?>

cd /var/lib/mysql/DBNAME

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

在我的例子中，我从一个默认MyISAM的MySQL实例迁移到一个默认InnoDB的MariaDB实例。

每个MariaDB迁移文件

mysqldump -u root -p --skip-create-options --all-databases > migration.sql

mysql -u root -p < migration.sql

这很简单。只有两步。

1. 复制，粘贴并运行

   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选项卡)

2. 复制所有结果到SQL选项卡和粘贴下面的行。

   START TRANSACTION;
   COMMIT;
   

例如:

START TRANSACTION;
ALTER TABLE `admin_files` ENGINE=InnoDB;
COMMIT;

要为所有非系统模式中的所有表生成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;

之后，通过客户端运行这些查询来执行修改。

• 答案基于上述答案，但改进了模式处理。

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

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

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

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

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

导入到MySQL (phpMyAdmin或命令行)

瞧!

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);
} ?>

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

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

如果您使用的是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通过命令行传递数据库名称。

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

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';"
)

遵循步骤:

1. 使用MySql命令如下，用于MyISAM的(ALTER TABLE t1 ENGINE = InnoDB)或(ALTER TABLE t1 ENGINE = MyISAM)转换为InnoDB(你应该对每个单独的表执行此操作，t1是表名。)

2. 写一个在所有表上循环的脚本并运行alter命令

3. 使用已经可用的脚本来处理:https://github.com/rafihaidari/convert-mysql-tables-storage-engine

4. 尝试此SQL获取所有信息 然后你可以把所有的表从isam改为InnoDB

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

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

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