ter Smitten's

On 2011-06-16 I wrote about a small PHP snippet to convert a MySQL database and all its tables to UTF-8/InnoDB. Yesterday, two years later, I was trying to accomplish the exact same thing using common_schema which I discovered recently.

According to it’s author(s) “common_schema is a framework for MySQL server administration“. In High Performance MySQL (3rd Edition), Baron Schwartz states that “common_schema is to MySQL as jQuery is to javaScript“. I don’t know about that, but it sure is really useful and fun to play with 🙂

set @script := "
  var $database := 'cacti';
  foreach ($table, $schema, $engine: table in :$database)
  {
    ALTER TABLE :$schema.:$table ENGINE=InnoDB;
    ALTER TABLE :$schema.:$table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
  }
";
call common_schema.run(@script);