将存储引擎从MyISAM更改为InnoDB-一次所有表-使用phpmyadmin

本文介绍了将存储引擎从MyISAM更改为InnoDB-一次所有表-使用phpmyadmin的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在mysql数据库中有100个表，并且都具有存储引擎:MyISAM.我想一次将其存储引擎全部更改为innoDB，并且我不想使用:

I have like 100 tables in mysql database and all are having storage engine: MyISAM. I want to change their storage engine all at once to innoDB and I DO NOT want to use:

1.PHP(如何从MyISAM转换所有表进入InnoDB?

Laravel( Laravel& InnoDB )

一次一张表.( how更改phpmyadmin中数据库的存储引擎?)

SQL查询.(如何将表存储引擎从MyISAM更改为InnoDB )

通过xampp或类似方式. (如何将默认存储引擎设置为InnoDB在XAMPP )

by xampp or similar way. (How to set the default storage engine to InnoDB in XAMPP )

所以我想更改:一次所有表存储引擎，并使用phpmyadmin 界面.

So I want to change : ALL TABLE STORAGE ENGINE AT ONCE and using phpmyadmin interface.

有帮助吗?

推荐答案

两步就足够了吗?

(1)对information_schema运行SELECT，如其中一些链接所示.但是，它可以输出100个完整的ALTER语句.

(1) Run the SELECT against information_schema, as seen in some of those links. But have it output 100 fully-formed ALTER statements.

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

(2)将100行复制并粘贴到phpadmin中.你去喝杯咖啡吧或花一些时间阅读有关 转换中的陷阱 看看您是否会遇到一些问题.

(2) Copy and paste the 100 lines into phpadmin. Go have a coffee. Or spend the time reading about the gotchas in converting to see if you will have some issues.

真正并行地执行它们不可能更快—您将受I/O约束.小桌子会很快完成，反正大桌子会延长时间.

Doing them truly in parallel is unlikely to be any faster -- you will be I/O bound. And the small tables will finish fast, leaving the big tables to stretch out the time anyway.

输出样本:

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

产生类似的东西:

ALTER TABLE 07 ENGINE=InnoDB;\n
ALTER TABLE 597377b ENGINE=InnoDB;\n
ALTER TABLE adjprice ENGINE=InnoDB;\n
ALTER TABLE big ENGINE=InnoDB;\n
ALTER TABLE bigv ENGINE=InnoDB;\n
ALTER TABLE blobid ENGINE=InnoDB;\n
ALTER TABLE charlatin1 ENGINE=InnoDB;\n
ALTER TABLE collorder ENGINE=InnoDB;\n
ALTER TABLE collorder2 ENGINE=InnoDB;\n
ALTER TABLE countries ENGINE=InnoDB;\n
ALTER TABLE d1 ENGINE=InnoDB;\n

然后将生成的许多行粘贴回工具中.无需手动输入名称.

Then paste however many rows are generated back into the tool. No need to manually type the names.

如果您第一次转换100个表，则当您再次运行查询时将不包括它们.仅包括新的25个.

If you first convert 100 tables, they will not be included when you run the query again. Only the new 25 will be included.

否，您不能自动"将任何新表更改为其他引擎.但是...

No, you cannot "automatically" change any new tables to a different engine. However...

在my.cnf中这样说，您可以使所有未明确为其指定ENGINE 的新表成为InnoDB:

By saying this in my.cnf, you can have all new table for which you do not explicitly specify an ENGINE be InnoDB:

[mysqld]
default_storage_engine = InnoDB

这篇关于将存储引擎从MyISAM更改为InnoDB-一次所有表-使用phpmyadmin的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！