Board index Linux Mysql

Moderator: chandranjoy

Convert MySQL databases to UTF-8 charset

Postby chandranjoy » Mon Dec 27, 2010 1:54 am

Convert MySQL databases to UTF-8 charset - How to?
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. The following 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.

Enjoy folks :)
chandranjoy
Site Admin
 
Posts: 283
Joined: Fri Oct 23, 2009 11:19 pm

Return to Mysql

Who is online

Users browsing this forum: No registered users and 1 guest


cron