Search code examples
mysqldatabasephpmyadmininnodbmyisam

How can I change the storage engine from MyISAM to innoDB only for tables sharing a similar prefix?

Using phpmyadmin I have several tables with the same prefix. I know I can change the storage engine one table by one table, but since some tables are quite heavy to process loading takes forever and sometimes crashes.

Is there a way to select several tables without using the full table name, and then change the storage engine from MyISAM to innoDB for the selected tables ?

Solution

  • phpmyadmin has a window for executing commands. (Better yet, use mysql commandline tool.)

    Step 1: Generate the ALTERs:

    SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ENGINE=InnoDB;')
   FROM information_schema.TABLES
   WHERE table_schema='test' AND ENGINE='MyISAM' AND TABLE_NAME LIKE 'rj%';
+-------------------------------------------------------+
| CONCAT('ALTER TABLE ', TABLE_NAME, ' ENGINE=InnoDB;') |
+-------------------------------------------------------+
| ALTER TABLE rj_article ENGINE=InnoDB;                 |
| ALTER TABLE rj_blobs ENGINE=InnoDB;                   |
| ALTER TABLE rj_bodies ENGINE=InnoDB;                  |
| ALTER TABLE rj_combos ENGINE=InnoDB;                  |
| ALTER TABLE rj_contents ENGINE=InnoDB;                |
| ALTER TABLE rj_current ENGINE=InnoDB;                 |

    ...

    Step 2:

    Execute those by copying them. Note: Since phpmyadmin has some kind of time limit, either raise that limit, or switch to mysql commandline tool.