MySQL – Convert several tables to utf8 encoding

Published on: June 3, 2016June 4, 2016Author: DavideComment: 0

For those who, like need to convert all tables of a MySQL database (MariaDB should work too), please find below a very simple and efficient PHP script.

As I like to keep things simple and clear, and don’t want to install PHPmyAdmin or anything else on my database server.

Create a file named php convert_tables_utf8.php on your server then copy and the script content below

<?php
  $dbname = "mydb";
  $dbuser = "myuser";
  $dbpassword = "mypassword";

  $con = mysql_connect('localhost',$dbuser,$dbpassword);
  if(!$con) { 
    echo "Cannot connect to the database ";
    die();
  }
  mysql_select_db($dbname);
  $result=mysql_query('show tables');

  while($tables = mysql_fetch_array($result)) {
    foreach ($tables as $key => $table) {
      echo "Converting table $table\n";
      mysql_query("ALTER TABLE $table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
    }
  echo "The collation of your database $dbname has been successfully changed!";
}
?>

<?php

$dbname = "mydb";

$dbuser = "myuser";

$dbpassword = "mypassword";

$con = mysql_connect('localhost',$dbuser,$dbpassword);

if(!$con) {

echo "Cannot connect to the database ";

die();

}

mysql_select_db($dbname);

$result=mysql_query('show tables');

while($tables = mysql_fetch_array($result)) {

foreach ($tables as $key => $table) {

echo "Converting table $table\n";

mysql_query("ALTER TABLE $table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");

}

echo "The collation of your database $dbname has been successfully changed!";

}

Adapt the variables to your environment and convert all database tables by running the script as below

$ php convert_tables_utf8.php

1	$ php convert_tables_utf8.php

If the script complain about missing PHP modules, make sure you’ve installed PHP MySQL extensions.
Checking the warnings and/or error in your terminal should give you more information.

Enjoy 🙂

Leave a Reply Cancel reply