• 2019-05-04 06:32:46

Changing your MySQL storage engine - MyISAM and InnoDB

I've been looking into many ways of converting my database tables from MyISAM to InnoDB and the other way around and I can say that this is the best/easiest one that I've found.

Here are the queries that I would usually use. The queries won’t convert the storage engine! They will basically create a list of the actual queries you’ll need to perform to actually execute the change, that way you can review the queries before actually changing the engine.

To actually convert the engine, just cut-paste the resulting row and execute them (each row is a single working query).

To convert all MyISAM Tables to InnoDB for the specified database (change database_name_here to your actual database name) - the most likely scenario!

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

To convert all InnoDB Tables to MyISAM for the specified database (change database_name_here to your actual database name)

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

To convert all InnoDB Tables to MyISAM for 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 for all databases

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

Once again, please note that these queries will build a list of queries you will have to execute individually to actually perform the conversion.

Here's an example. The output that you would get would look something like this:

+-------------------------------------------------------------------------+
| CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=MyISAM;') |
+-------------------------------------------------------------------------+
| ALTER TABLE dbname.user ENGINE=MyISAM;                                  |
| ALTER TABLE dbname.meta ENGINE=MyISAM;                                  |
| ALTER TABLE dbname.posts ENGINE=MyISAM;                                 |
| ALTER TABLE dbname.chats ENGINE=MyISAM;                                 |
| ALTER TABLE dbname.links ENGINE=MyISAM;                                 |
...

To do the actual conversion just run the ALTER TABLE .. queries.

This is pretty much it.

---