13

When it comes to MySQL and PHPMyAdmin, I'm not a novice, but I'm closer to novice than expert. Hopefully what I ask for is doable, and that someone will provide me with a simple, cut-n-paste SQL query to make it happen.

I need to convert around 9 tables in each of 12 dbs from MyISAM to InnoDB, and I'm hoping I can do all the tables in one db with a single query rather than having to click-and-wait for each and every table in PHPMyAdmin. Basically, is there a way to run "ALTER TABLE foo ENGINE = InnoDB" on multiple tables at once via a query run in PHPMyAdmin?

ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
iampariah
  • 155
  • 1
  • 8

1 Answers1

34
select concat('alter table ',table_name, ' engine = innodb;')
from information_schema.tables
where table_schema in ('db1','db2',....,'dbN')

then run the query output.

Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98