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!
