mysql数据表latin1编码导致中文乱码问题

前几天一位朋友使用wordpress搭建的个人网站升级。升级完后页面内容全部变成了乱码。找我帮忙解决。

经过研究发现应该是mysql数据库升级导致的。他的网站建站至少20多年了，当时mysql中文编码还是使用的latin1。

解决方案是将表和字段编码格式修改为utf8，

ALTER TABLE `table` DEFAULT CHARACTER SET utf8;

修改了表的编码格式，但有一些字段比如text仍然是使用Latin1编码的。使用上面的命令可以看字段的编码格式。

show create table wp_comments;

其中的comment_author,comment_content等类型为text的字段使用的是latin1编码。可以通过下面的语句将字段的编码格式也修改为utf8。

alter table wp_comments change comment_content comment_content text  character set utf8 not null;

如果一个个的修改闲麻烦，还可以直接执行下面的命令，修改一张表的全部字段编码格式。

--修改表的编码格式
ALTER TABLE wp_comments DEFAULT CHARACTER SET utf8;
--修改表中所有字段的编码格式
ALTER TABLE wp_comments CONVERT TO CHARACTER SEt utf8;

但以上只是修改了编码格式，但存储的内容还是以latin1识别的形式存放的，依然乱码。这时候可以通过对字段内容编码转换后进行更新。如果不放心，可以先找一条记录进行测试：

UPDATE wp_comments
   SET comment_author = CONVERT (unhex (hex (CONVERT (comment_author USING   latin1))) using utf8) WHERE comment_id = '47';

上面的方法完美解决了朋友的问题。不过在此之前，我使用了费劲却不理想的方法。通过将数据表内容导出再导入的方式。

-- 导出建表语句和数据内容
mysqldump -u用户名 -p 数据库名 wp_posts > wp_posts.sql

然而网站的服务器非常慢，使用winscp传输到个人笔记本上超级慢。我又将文件复制到我另一台vps上，

--将wp_post.sql复制到另一台远程服务器
scp ./wp_post.sql [email protected]地址:/home/目标文件夹

然后使用winScp把文件传输到自己的笔记本。因为我个人的笔记本上有安装mysql，操作起来比较快。

然后在本地mysql数据库中使用下面的数据导出为正确内容替换wp_posts.sql的数据内容部分。

--导出wp_posts的正确内容
SELECT post_date, ...省略其它字段, CONVERT (unhex (hex (CONVERT (post_title USING         latin1))) using utf8) as post_title, convert(unhex(hex(convert(post_content using latin1))) using utf8) as post_content FROM wp_posts

修改wp_posts.sql中的建表语句为utf8格式后，将文件上传到服务器，

scp -P 22 [email protected]地址:/home/目录/wp_posts.sql web服务器目录

重命名原来的表作为备份。

ALTER TABLE wp_posts RENAME wp_posts_backup;

使用source命令重新建表和导入数据。

source /web服务器目录/wp_posts.sql;

上面的方法我居然成功了。早知可以直接使用开头update的方法，我干嘛这么费劲巴拉。