MySQL – Convert all Tables of one or more Database(s) from MyISAM to InnoDB

To convert all DBNAME’s InnoDB Tables to MyISAM

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=MyISAM;') 
FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'

To convert all DBNAME’s MyISAM Tables to InnoDB

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=InnoDB;') 
FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE'

To convert all InnoDB Tables to MyISAM (all databases)

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=MyISAM;') 
FROM information_schema.TABLES WHERE ENGINE = 'InnoDB';

To convert all MyISAM Tables to InnoDB (all databases)

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=InnoDB;') 
FROM information_schema.TABLES WHERE ENGINE = 'MyISAM';

发表评论

电子邮件地址不会被公开。 必填项已用*标注