CODEWITHSUNDEEP
mysqldatabasephpmyadmininnodbmyisam
Cobactan
Cobactan

Reputation: 113

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 ?

Upvotes: 0

Views: 1023

Answers (1)

Rick James
Rick James

Reputation: 142540

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.

Upvotes: 1

Related Questions