应用场景：两台MySQL数据库读写分离，将读库的引擎修改为MyISAM来加快查询速度。

操作：通过字符串拼接的方法拼出SQL语句，到命令行执行。

查询表引擎

1
2

SELECT CONCAT(table_name,'  ', engine)
      FROM information_schema.tables WHERE table_schema="表名" AND ENGINE="InnoDB";

生成修改sql

1

select CONCAT('alter table ',table_name,' engine=MyISAM;') FROM information_schema.tables WHERE table_schema="xfdlx" AND ENGINE="InnoDB";

结果

1
2
3
4
5
6
7
8
9
10
11
12
13

alter table bbp_businesshqlist engine=MyISAM;
alter table bbp_countlimit engine=MyISAM;
alter table bbp_countproportion engine=MyISAM;
alter table bbp_distributorbusinessinfo engine=MyISAM;
alter table bbp_distributortypeinfo engine=MyISAM;
alter table bbp_dkhregisterinfo_upload engine=MyISAM;
alter table bbp_legalpersonabout engine=MyISAM;
alter table bbp_legalpersonabout_interface engine=MyISAM;
alter table bbp_legalpersonabout_log engine=MyISAM;
alter table bbp_legalpersonupdatehistory engine=MyISAM;
alter table bbp_modifypromisetimelimit engine=MyISAM;
alter table bbp_ndlockcondition engine=MyISAM;
alter table bbp_ndlockinfo engine=MyISAM;

运行

在命令行将上述语句复制运行。

查看当前表引擎

show TABLE STATUS FROM xfdlx where NAME='lenovo_shopinfo'