Jul 132011
Alter all tables in MySQL
Answer:
To alter all the tables in a given database in MySQL, e.g. change the storage engine, you can first generate the SQL statements using following method.
mysql> select CONCAT("Alter Table `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` ENGINE = InnoDB") AS cmd from TABLES where TABLE_SCHEMA = "test";
It will print out the followings:
+----------------------------------------------------------------+
| cmd |
+----------------------------------------------------------------+
| Alter Table `test`.`foo` ENGINE = InnoDB |
| Alter Table `test`.`bar` ENGINE = InnoDB |
...
Then you can execute the above commands manually.
