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

Have you ever needed to convert one, some or all the tables of a MySQL Database from MyISAM toInnoDB or the other way around?

Here’s a couple queries you can use to achieve such results. As you will notice, they won’t execute the commands – they will basically create a list of the actual queries you’ll need to perform to actually execute the change, so you can review it just before it goes live. To execute the queries, just cut-paste the resulting row and execute them (each row is a single working query).

To convert all DBNAME’s InnoDB Tables to MyISAM

								1
							
								2
							
								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

								1
							
								2
							
								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)

								1
							
								2
							
								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)

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

Once again, these commands will build a list of queries you will have to execute to actually perform the conversion: to actually perform the change you’ll have to execute them.

That’s all for now: happy conversion!

文章版权归作者所有，未经允许请勿转载。

THE END