MySQL - 如何将整个数据库转换为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:

我试图弄清楚如何将整个数据库转换为utf8并解决以下问题,导致部分数据显示不正确。在这一点上,我只是困惑,我需要你的洞察力。以下是您可能需要的一些信息:

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).

我想将所有数据转换为utf8,但我不确定如何执行此操作或数据可能发生的情况(即格式错误的数据,不正确的编码)。

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?

在我的代码?

2 个解决方案

#1

Perhaps this will help:

也许这会有所帮助:

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

#2

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

#1