Have you ever needed to convert one, some or all the tables of a MySQL Database from MyISAM to InnoDB 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'; |
If you need to perform this task on a single Database, replace DBNAME with the name of your Database; if you need to do that on multiple Databases, add one or more additional OR conditions to the WHERE and let MySQL do the rest.
Once again, these commands will build a list of queries you will have to execute to actually perform the conversion, meaning that you'll have to manually execute them afterwards.
That's all for now: happy conversion!
EDIT: This logic can also be used to convert all the tables of one (or more) database(s) to a specific Collation (the series of rules used by MySQL to compare character within a given character set). for further info, read this post.
