可以直接将foreign的Innodb转换成mysiam吗?
我想将带有innodb表的db转换为myisam,全部。我怎样才能做到这些?表之间存在一些外键。
我怎样才能以最好的方式做到这一点?
I want to convert the db with innodb tables into myisam, all of them. How can I do these? there are some foreign keys exist among tables.
how can I make this in the best way?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
当外键仍然存在时,您无法直接从 InnoDB 转换为 MyISAM。你必须先消除限制。为此,请针对每个表执行以下步骤:
SHOW CREATE TABLE tablename
ALTER TABLE tablename DROP FOREIGN KEY x
,其中x
是出现在CONSTRAINT
和FOREIGN KEY
之间的标识符。SHOW CREATE TABLE tablename
。外键约束可能会留下索引(因为 InnoDB 需要每个外键都有一个索引,并且它不一定会因为删除了约束而删除它们)。对于您决定不再需要的每个索引,请发出 ALTER TABLE tablename DROP INDEX indexname 。对涉及约束的所有表执行此操作后,您可以使用 ALTER TABLE tablename ENGINE=MYISAM 单独将表转换为 MyISAM。
You can't convert directly from InnoDB to MyISAM while the foreign keys are still there. You have to remove the constraints first. To do this, for each table follow these steps:
SHOW CREATE TABLE tablename
ALTER TABLE tablename DROP FOREIGN KEY x
wherex
is the identifier that appears betweenCONSTRAINT
andFOREIGN KEY
.SHOW CREATE TABLE tablename
again. The foreign key constraints may have left behind indexes (since InnoDB requires an index on each foreign key, and it won't necessarily remove them just because you have removed the constraint). For each index you decide you no longer want, issueALTER TABLE tablename DROP INDEX indexname
.Once you have done this for all tables that are involved with constraints, you can convert tables to MyISAM individually using
ALTER TABLE tablename ENGINE=MYISAM
.