Convert Database to UTF-8

Posted by Nessa | Posted in ,,, | Posted on December 6, 2007

15

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:

<?php
// 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";
}


mysql_close($dbconn);
?>

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

Be Sociable, Share!

Comments (15)

Nessa’s Blog: Convert Database to UTF-8…

Nessa has posted a quick way to convert a database from ……

[...] has posted a quick way to convert a database from whatever character set it’s currently on over to UTF-8 with a [...]

Nice!
Tidy and very useful.

[...] deal with a lot of database dumps. So this post is a must for me. We seriously see a ton of customers coming in with the type of databases that are [...]

ALTER TABLE $table DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci, CONVERT TO CHARACTER SET utf8
would not only change the default types, but also the actual column data and types.

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html#id1358790

If the data in some rows or whole columns have a wrong encoding, you can let MySQL encode the data properly.

# Single rows
UPDATE table SET column=CONVERT(CONVERT(column USING binary) USING utf8) WHERE id=123

http://www.mysqlperformanceblog.com/2007/12/18/fixing-column-encoding-mess-in-mysql

thanks but your script has errors

odd, considering this has worked for me a dozen or so times.

wow,great thanx dude :)

Good, but not valuable as it does not change encoding of existent data

much appreciated, thanx!

hi
thanks for the script.

it is not work properly with my 204 tables.

———-

Fatal error: Maximum execution time of 30 seconds exceeded in C:\AppServ\www\vb\utf8.php on line 23

——————-

please advice.

thanks

@180B change max_execution_time in php.ini

nice work.thanks

nice topic.thanks

Post a comment