mysql 批量修改表引擎

通过以下查询可以看到数据库中表的引擎。

select TABLE_NAME from information_schema.tables as t 
where table_schema = 'AA' 
and table_type='base table'
and engine = 'MyISAM';

生成sql语句

要先修改group_concat默认长度限制

SET GLOBAL group_concat_max_len=102400;
SET SESSION group_concat_max_len=102400; 

生成sql语句

USE `sc_ol_201901301642`;
SELECT GROUP_CONCAT(CONCAT( 'ALTER TABLE ' ,TABLE_NAME ,' ENGINE=InnoDB; ') SEPARATOR '' )
FROM information_schema.TABLES AS t
WHERE TABLE_SCHEMA = 'AA' 
AND TABLE_TYPE = 'BASE TABLE'
and engine = 'MyISAM'
;