CODEWITHSUNDEEP
mysqlencodingutf-8
bikey77
bikey77

Reputation: 6672

MySQL - How do I convert entire database into utf8

I'm trying to figure out how to convert an entire database to utf8 and solve the following issue which results into having part of the data display incorrectly. At this point I'm simply confused and I need your insight. Here's some information you will probably need:

SHOW VARIABLES LIKE '%character%' returns:

Variable_name   Value
character_set_client    utf8
character_set_connection    utf8
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/

I'd like to convert ALL data to utf8 but I'm not sure how to perform this oreration or what might happen to the data (ie, malformed data, incorrect encoding).

Once I've finished the conversion, do I still need 

header("Content-type: text/html; charset=utf-8");
and mysql_query("SET NAMES 'utf8'", $connection);

in my code?

Upvotes: 2

Views: 2003

Answers (2)

DKSan
DKSan

Reputation: 4197

i would suggest you go with this little script to convert your data. And make sure you have a fresh backup of you database.

<?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);
?>

Upvotes: 1

Tjekkles
Tjekkles

Reputation: 5612

Perhaps this will help:

http://www.oreillynet.com/onlamp/blog/2006/01/turning_mysql_data_in_latin1_t.html

Upvotes: 1

Related Questions