Changing character sets on a live MySQL database
I currently have a bunch of tables using the latin1 charset in a MySQL 5.1.x DB. Problem is,开发者_StackOverflow社区 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?
I suggest to take a look at Converting Character Sets post on mysqlperformanceblog.
Here is a peace of PHP code that changes encoding on existing table:
<?php
$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)
精彩评论