Convert Database to UTF-8

We seriously see a ton of customers coming in with the type of databases that are a nightmare to move over. When you’re dealing with special characters in a database, you have to make sure that the charset and collation are dumped *with* the database, so that when you move it to another server the tables and data create properly. The biggest annoyance so far is converting tables back to UTF-8, as when this is done through the MySQL shell or phpmyadmin is had to be done table-by-table. So, I wrote this simple PHP script to do it all at once:

// Database info

$dbhost = 'localhost';
$dbuser = 'db_user';
$dbpass = 'password';
$dbname = 'db_name';


header('Content-type: text/plain');

$dbconn = mysql_connect($dbhost, $dbuser, $dbpass) or die( mysql_error() );
$db = mysql_select_db($dbname) or die( mysql_error() );

$sql = 'SHOW TABLES';
$result = mysql_query($sql) or die( mysql_error() );

while ( $row = mysql_fetch_row($result) )
$table = mysql_real_escape_string($row[0]);
$sql = "ALTER TABLE $table DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
mysql_query($sql) or die( mysql_error() );
print "$table changed to UTF-8.\n";


If course, you can adjust the ALTER TABLE statement to any character set and collation that you need.

Be Sociable, Share!

Leave a Reply

Your email address will not be published. Required fields are marked *