批量修改Mysql表引擎为InnoDB

注意：本文最后更新于 2767 天前，有关的内容可能已经发生变化，请参考使用。

一般批量修改MYSQL中某表的数据库引擎可以利用官方工具mysql_convert_table_format来实现，这里指的是不使用其他工具仅用shell的方法来实现。（以下例子效果是将数据库ecjtubbs中所有引擎不为InnoDB的表修改为使用InnoDB引擎）

先用shell获取需要更换表引擎：

    mysql --user=root --password=passwd -e "show table status from ecjtubbs where Engine <> 'InnoDB'G"|grep Name|awk '{print "alter table "$2" engine=innodb;";}' >mysqlchange

这样在mysqlchange文件里面就会有如下的内容（包含了所有ecjtubbs表中所有引擎不为InnoDB的表名）：

    alter table cdb_activities engine=innodb;
    alter table cdb_activityapplies engine=innodb;
    alter table cdb_addons engine=innodb;
    alter table cdb_adminactions engine=innodb;
    alter table cdb_admincustom engine=innodb;
    alter table cdb_admingroups engine=innodb;
    alter table cdb_adminnotes engine=innodb;
    alter table cdb_adminsessions engine=innodb;
    alter table cdb_advertisements engine=innodb;
    alter table cdb_announcements engine=innodb;
    alter table cdb_attachmentfields engine=innodb;
    alter table cdb_attachments engine=innodb;
    alter table cdb_attachpaymentlog engine=innodb;
    alter table cdb_attachtypes engine=innodb;
    alter table cdb_banklist engine=innodb;
    alter table cdb_banklog engine=innodb;
    alter table cdb_bankoperation engine=innodb;
    alter table cdb_banned engine=innodb;
    alter table cdb_bbcodes engine=innodb;
    alter table cdb_caches engine=innodb;
    alter table cdb_creditslog engine=innodb;
    alter table cdb_crons engine=innodb;
    alter table cdb_debateposts engine=innodb;
    alter table cdb_debates engine=innodb;
    alter table cdb_failedlogins engine=innodb;
    alter table cdb_faqs engine=innodb;
    alter table cdb_favoriteforums engine=innodb;
    alter table cdb_favorites engine=innodb;
    alter table cdb_favoritethreads engine=innodb;
    alter table cdb_feeds engine=innodb;
    alter table cdb_forumfields engine=innodb;
    alter table cdb_forumlinks engine=innodb;
    alter table cdb_forumrecommend engine=innodb;
    alter table cdb_forums engine=innodb;
    alter table cdb_gameinfo engine=innodb;
    alter table cdb_gjdquiz_counts engine=innodb;
    alter table cdb_gjdquiz_questions engine=innodb;
    alter table cdb_gjdquiz_series engine=innodb;
    alter table cdb_happy28_flow engine=innodb;
    alter table cdb_happy28_members engine=innodb;
    alter table cdb_happy28_prize engine=innodb;
    alter table cdb_happy28_term engine=innodb;
    alter table cdb_imagetypes engine=innodb;
    alter table cdb_invites engine=innodb;
    alter table cdb_itempool engine=innodb;
    alter table cdb_league engine=innodb;
    alter table cdb_magiclog engine=innodb;
    alter table cdb_magicmarket engine=innodb;
    alter table cdb_magics engine=innodb;
    alter table cdb_marry engine=innodb;
    alter table cdb_medallog engine=innodb;
    alter table cdb_medals engine=innodb;
    alter table cdb_memberfields engine=innodb;
    alter table cdb_membermagics engine=innodb;
    alter table cdb_memberrecommend engine=innodb;
    alter table cdb_members engine=innodb;
    alter table cdb_memberspaces engine=innodb;
    alter table cdb_moderators engine=innodb;
    alter table cdb_modworks engine=innodb;
    alter table cdb_myguess engine=innodb;
    alter table cdb_myposts engine=innodb;
    alter table cdb_mytasks engine=innodb;
    alter table cdb_mythreads engine=innodb;
    alter table cdb_navs engine=innodb;
    alter table cdb_onlinelist engine=innodb;
    alter table cdb_onlinetime engine=innodb;
    alter table cdb_orders engine=innodb;
    alter table cdb_paymentlog engine=innodb;
    alter table cdb_pluginhooks engine=innodb;
    alter table cdb_plugins engine=innodb;
    alter table cdb_pluginvars engine=innodb;
    alter table cdb_polloptions engine=innodb;
    alter table cdb_polls engine=innodb;
    alter table cdb_postban engine=innodb;
    alter table cdb_postban_escapelog engine=innodb;
    alter table cdb_postposition engine=innodb;
    alter table cdb_posts engine=innodb;
    alter table cdb_preplies engine=innodb;
    alter table cdb_profilefields engine=innodb;
    alter table cdb_projects engine=innodb;
    alter table cdb_promotions engine=innodb;
    alter table cdb_prompt engine=innodb;
    alter table cdb_promptmsgs engine=innodb;
    alter table cdb_prompttype engine=innodb;
    alter table cdb_ranks engine=innodb;
    alter table cdb_ratelog engine=innodb;
    alter table cdb_regips engine=innodb;
    alter table cdb_relatedthreads engine=innodb;
    alter table cdb_reportlog engine=innodb;
    alter table cdb_request engine=innodb;
    alter table cdb_rewardlog engine=innodb;
    alter table cdb_rsscaches engine=innodb;
    alter table cdb_searchindex engine=innodb;
    alter table cdb_sessions engine=innodb;
    alter table cdb_settings engine=innodb;
    alter table cdb_smilies engine=innodb;
    alter table cdb_spacecaches engine=innodb;
    alter table cdb_stats engine=innodb;
    alter table cdb_statvars engine=innodb;
    alter table cdb_styles engine=innodb;
    alter table cdb_stylevars engine=innodb;
    alter table cdb_tags engine=innodb;
    alter table cdb_tasks engine=innodb;
    alter table cdb_taskvars engine=innodb;
    alter table cdb_team engine=innodb;
    alter table cdb_templates engine=innodb;
    alter table cdb_threads engine=innodb;
    alter table cdb_threadsmod engine=innodb;
    alter table cdb_threadtags engine=innodb;
    alter table cdb_threadtypes engine=innodb;
    alter table cdb_tradecomments engine=innodb;
    alter table cdb_tradelog engine=innodb;
    alter table cdb_tradeoptionvars engine=innodb;
    alter table cdb_trades engine=innodb;
    alter table cdb_typemodels engine=innodb;
    alter table cdb_typeoptions engine=innodb;
    alter table cdb_typeoptionvars engine=innodb;
    alter table cdb_typevars engine=innodb;
    alter table cdb_usergroups engine=innodb;
    alter table cdb_validating engine=innodb;
    alter table cdb_warnings engine=innodb;
    alter table cdb_words engine=innodb;
    alter table ecjtu_ads engine=innodb;
    alter table ecjtu_announcements engine=innodb;
    alter table ecjtu_attachments engine=innodb;
    alter table ecjtu_attachmenttypes engine=innodb;
    alter table ecjtu_blocks engine=innodb;
    alter table ecjtu_cache engine=innodb;
    alter table ecjtu_cache_0 engine=innodb;
    alter table ecjtu_cache_1 engine=innodb;
    alter table ecjtu_cache_2 engine=innodb;
    alter table ecjtu_cache_3 engine=innodb;
    alter table ecjtu_cache_4 engine=innodb;
    alter table ecjtu_cache_5 engine=innodb;
    alter table ecjtu_cache_6 engine=innodb;
    alter table ecjtu_cache_7 engine=innodb;
    alter table ecjtu_cache_8 engine=innodb;
    alter table ecjtu_cache_9 engine=innodb;
    alter table ecjtu_cache_a engine=innodb;
    alter table ecjtu_cache_b engine=innodb;
    alter table ecjtu_cache_c engine=innodb;
    alter table ecjtu_cache_d engine=innodb;
    alter table ecjtu_cache_e engine=innodb;
    alter table ecjtu_cache_f engine=innodb;
    alter table ecjtu_categories engine=innodb;
    alter table ecjtu_channels engine=innodb;
    alter table ecjtu_corpus engine=innodb;
    alter table ecjtu_crons engine=innodb;
    alter table ecjtu_customfields engine=innodb;
    alter table ecjtu_effects engine=innodb;
    alter table ecjtu_favorites engine=innodb;
    alter table ecjtu_friendlinks engine=innodb;
    alter table ecjtu_friends engine=innodb;
    alter table ecjtu_goodsprice engine=innodb;
    alter table ecjtu_groupfields engine=innodb;
    alter table ecjtu_groupinvite engine=innodb;
    alter table ecjtu_groupitems engine=innodb;
    alter table ecjtu_groups engine=innodb;
    alter table ecjtu_groupuid engine=innodb;
    alter table ecjtu_guestbooks engine=innodb;
    alter table ecjtu_hrcategories engine=innodb;
    alter table ecjtu_hrcomments engine=innodb;
    alter table ecjtu_hrfolders engine=innodb;
    alter table ecjtu_hritems engine=innodb;
    alter table ecjtu_hrmessage engine=innodb;
    alter table ecjtu_hrrates engine=innodb;
    alter table ecjtu_itemtypes engine=innodb;
    alter table ecjtu_lovecategories engine=innodb;
    alter table ecjtu_lovecomments engine=innodb;
    …………

接下来复制上面的内容，用Putty登录服务器，用“mysql --user=root --password=passwd”登录Mysql在 mysql> 界面后直接点击鼠标右键粘贴即可完成数据表的引擎更换（如果数据库非常大可能要等一段时间）。

其实完全可以通过以下shell完成同样功能的操作：

mysql --user=root --password=passwd -e "show table status from ecjtubbs where Engine <> 'InnoDB'G"|grep Name|awk '{print "mysql --user=root --password=passwd -p ecjtubbs "alter table "$2" engine=innodb;"";}'|bash

只是有的环境下mysql --user=root --password=passwd -p ecjtubbs 并不一定会按照预期的想法登录mysql并选择ecjtubbs数据库，只要你能确保mysql --user=root --password=passwd -p ecjtubbs 能够登录到Mysql并选择ecjtubbs数据库就可以使用一行命令实现修改表引擎的效果。(passwd是root用户的密码)

「倘若有所帮助，不妨酌情赞赏!」

Holmesian Blog

批量修改Mysql表引擎为InnoDB

相关文章