更换MySQL数据库字符集的那些事儿,更改活动MySQL数据库上的字符集

I currently have a bunch of tables using the latin1 charset in a MySQL 5.1.x DB. Problem is, we recently had a bunch of users trying to input text using UTF-8 encoding, and that seemed to break things.

Is it safe to blindly update the table's character set? What are some best practices (besides obviously backing everything up) for a situation like this?

解决方案

Here is a peace of PHP code that changes encoding on existing table:

$table_name = 'some_table';

$from = 'latin1';

$to = 'utf8';

$q = 'SELECT * FROM '.$table_name;

//get data from mysql table

$data = mysql::array_qw($q) or die('Cound not select: '.mysql_error());

for ($i = 0; $i < count($data); $i++)

{

$q = 'UPDATE '.$table_name.' SET ';

foreach($data[$i] as $key=>$value)

{

$q.= $key.' = '."'".iconv($from, $to, mysql_real_escape_string($value))."', ";

}

$q[strlen($q)-2] = ' ';

$q.= $key = "WHERE id = ".$data[$i]['id'];

mysql_query($q) or die(mysql_error());

}

?>

It gets all the data, converts it, and then updates database table. (mysql::array_qw function converts mysql query result to array)