Linux.中国 - 开源社区

 找回密码
 骑士注册

QQ登录

微博登录


理解和解决 MySQL 乱码问题

2015-3-12 14:41    评论: 18 收藏: 2 分享: 41    

如何修复已经编码损坏的数据

在介绍正确方法前,我们先科普一下那些网上流传的所谓的“正确方法”可能会造成的严重后果。

错误方法一

无论从语法还是字面意思来看:ALTER TABLE ... CHARSET=xxx 无疑是最像包治乱码的良药了!而事实上,他对于你已经损坏的数据一点帮助也没有,甚至连已经该表已经创建列的默认字符集都无法改变。我们看下面这个例子

master [localhost] {msandbox} (test) > show create table charset_test;
+--------------+--------------------------------+
| Table        | Create Table                   |
+--------------+--------------------------------+
| charset_test | CREATE TABLE `charset_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `char_col` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |
+--------------+--------------------------------+
1 row in set (0.00 sec)

master [localhost] {msandbox} (test) > alter table charset_test charset=gbk;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

master [localhost] {msandbox} (test) > show create table charset_test;
+--------------+--------------------------------+
| Table        | Create Table                   |
+--------------+--------------------------------+
| charset_test | CREATE TABLE `charset_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `char_col` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=gbk |
+--------------+--------------------------------+
1 row in set (0.00 sec)

可见该语法紧紧修改了表的默认字符集,即只对以后创建的列的默认字符集产生影响,而对已经存在的列和数据没有变化。

错误方法二

ALTER TABLE … CONVERT TO CHARACTER SET … 的相较于方法一来说杀伤力更大,因为从 官方文档的解释他的作用就是用于对一个表的数据进行编码转换。下面是文档的一小段摘录:

To change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:

ALTER TABLE tbl_name
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

而实际上,这句语法只适用于当前并没有乱码,并且不是通过错进错出的方法保存的表。。而对于已经因为错进错出而产生编码错误的表,则会带来更糟的结果。

我们用一个实际例子来解释下,这句SQL实际做了什么和他会造成的结果。假设我们有一张编码是latin1的表,且之前通过错进错出存入了UTF-8的数据,但是因为通过terminal仍然能够正常显示。即上文错进错出章节中举例的情况。一段时间使用后我们发现了这个错误,并打算把表的字符集编码改成UTF-8并且不影响原有数据的正常显示。这种情况下使用alter table convert to character set会有这样的后果:

master [localhost] {msandbox} (test) > create table charset_test_latin1 (id int primary key auto_increment, char_col varchar(50)) charset = latin1;
Query OK, 0 rows affected (0.01 sec)

master [localhost] {msandbox} (test) > set names latin1;
Query OK, 0 rows affected (0.00 sec)

master [localhost] {msandbox} (test) > insert into charset_test_latin1 (char_col) values ('这是中文');
Query OK, 1 row affected (0.01 sec)

master [localhost] {msandbox} (test) > select id,hex(char_col),char_col,char_length(char_col) from charset_test_latin1;
+----+--------------------------+--------------+-----------------------+
| id | hex(char_col)            | char_col     | char_length(char_col) |
+----+--------------------------+--------------+-----------------------+
|  1 | E8BF99E698AFE4B8ADE69687 | 这是中文     |                    12 |
+----+--------------------------+--------------+-----------------------+
1 row in set (0.01 sec)

master [localhost] {msandbox} (test) > alter table charset_test_latin1 convert to character set utf8;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

master [localhost] {msandbox} (test) > set names utf8;
Query OK, 0 rows affected (0.00 sec)

master [localhost] {msandbox} (test) > select id,hex(char_col),char_col,char_length(char_col) from charset_test_latin1;
+----+--------------------------------------------------------+-----------------------------+-----------------------+
| id | hex(char_col)                                          | char_col                    | char_length(char_col) |
+----+--------------------------------------------------------+-----------------------------+-----------------------+
|  1 | C3A8C2BFE284A2C3A6CB9CC2AFC3A4C2B8C2ADC3A6E28093E280A1 | 这是中文                |                    12 |
+----+--------------------------------------------------------+-----------------------------+-----------------------+
1 row in set (0.00 sec)

从这个例子我们可以看出,对于已经错进错出的数据表,这个命令不但没有起到“拨乱反正”的效果,还会彻底将数据糟蹋,连数据的二进制编码都改变了。

正确的方法一 Dump & Reload

这个方法比较笨,但也比较好操作和理解。简单的说分为以下三步:

  1. 通过错进错出的方法,导出到文件
  2. 用正确的字符集修改新表
  3. 将之前导出的文件导回到新表中

还是用上面那个例子举例,我们用UTF-8将数据“错进”到latin1编码的表中。现在需要将表编码修改为UTF-8可以使用以下命令

shell> mysqldump -u root -p -d --skip-set-charset --default-character-set=utf8 test charset_test_latin1 > data.sql
#确保导出的文件用文本编辑器在UTF-8编码下查看没有乱码
shell> mysql -uroot -p -e 'create table charset_test_latin1 (id int primary key auto_increment, char_col varchar(50)) charset = utf8' test
shell> mysql -uroot -p  --default-character-set=utf8 test < data.sql

正确的方法二 Convert to Binary & Convert Back

这种方法比较取巧,用的是将二进制数据作为中间数据的做法来实现的。由于,MySQL再将有编码意义的数据流,转换为无编码意义的二进制数据的时候并不做实际的数据转换。而从二进制数据准换为带编码的数据时,又会用目标编码做一次编码转换校验。通过这两个特性就相当于在MySQL内部模拟了一次“错出”,将乱码“拨乱反正”了。

还是用上面那个例子举例,我们用UTF-8将数据“错进”到latin1编码的表中。现在需要将表编码修改为UTF-8可以使用以下命令

mysql> ALTER TABLE charset_test_latin1 MODIFY COLUMN char_col VARBINARY(50);
mysql> ALTER TABLE charset_test_latin1 MODIFY COLUMN char_col varchar(50) character set utf8;

 

123
查看其它分页:

发表评论


最新评论

我也要发表评论

湖南刘凯威 2015-3-15 14:03  新浪微博网友评论
[作揖]
回复
果果酱0o 2015-3-14 23:03  新浪微博网友评论
@我的印象笔记
回复
lyhabc 2015-3-13 15:01
收藏了
9 回复
周明智 2015-3-13 11:03  新浪微博网友评论
乱码应该是初学者必踩的坑吧
回复
nihaoxiongfei 2015-3-13 07:33  新浪微博网友评论
mark
6 回复
glson 2015-3-13 03:03  新浪微博网友评论
//@Linux中国:[赞]//@_小噗: 全文精华,也跟我N年的经验一样:“只要做到‘三位一体’,即客户端,MySQL character-set-client,table charset三个字符集完全一致就可以保证一定不会有乱码出现了。”
回复
cannshui 2015-3-12 23:03  新浪微博网友评论
MA
回复
宋万伟_song 2015-3-12 22:33  新浪微博网友评论
收藏之
回复
尉海立 2015-3-12 21:33  新浪微博网友评论
乱码真的很恼火,读了MySql的参考文档才搞定
回复
戊辰人王路 2015-3-12 19:33  新浪微博网友评论
马克
回复
ZetaZ 2015-3-12 18:33  新浪微博网友评论
……
回复
HuangBowei 2015-3-12 18:03  新浪微博网友评论
Repost
回复
超凡临时工 2015-3-12 18:03  新浪微博网友评论
//@Linux中国:[赞]//@_小噗: 全文精华,也跟我N年的经验一样:“只要做到‘三位一体’,即客户端,MySQL character-set-client,table charset三个字符集完全一致就可以保证一定不会有乱码出现了。”
回复
约瑟夫-双子座 2015-3-12 16:33  新浪微博网友评论
//@Linux中国:[赞]//@_小噗: 全文精华,也跟我N年的经验一样:“只要做到‘三位一体’,即客户端,MySQL character-set-client,table charset三个字符集完全一致就可以保证一定不会有乱码出现了。”
回复
我心飛揚Since2010 2015-3-12 15:47  新浪微博网友评论
Repost
回复
做个有bigger的男子 2015-3-12 15:33  新浪微博网友评论
@一只会说话的猪仔
回复
Linux中国 2015-3-12 15:33  新浪微博网友评论
[赞]//@_小噗: 全文精华,也跟我N年的经验一样:“只要做到‘三位一体’,即客户端,MySQL character-set-client,table charset三个字符集完全一致就可以保证一定不会有乱码出现了。”
回复
_小噗 2015-3-12 15:03  新浪微博网友评论
全文精华,也跟我N年的经验一样:“只要做到‘三位一体’,即客户端,MySQL character-set-client,table charset三个字符集完全一致就可以保证一定不会有乱码出现了。”
回复

热点评论

lyhabc 2015-3-13 15:01
收藏了
9
nihaoxiongfei 2015-3-13 07:33
mark
6
返回顶部

分享到微信朋友圈

打开微信,点击底部的“发现”,
使用“扫一扫”将网页分享至朋友圈。