[mysql基础文档]-21-中文乱码原理与解决方法

hyx 2019-06-06 11:46:06 123次 0

引言

本文以MySQL字符集转换储存原理为基础,提供了MySQL中文乱码以及网页中文乱码的解决方法。

文章目录

0×1.MySQL字符集转换过程

先来简单看看MySQL是如何将我们输入的中文储存在数据库中的,了解这一部分内容对理解乱码产生的原因很有帮助,也更容易理解本文后面的内容。

● MySQL系统变量中的字符集

01 --使用下面的命令,查看安装MySQL后默认的字符集转换参数
02 mysql>showvariableslike'%char%';
03 +--------------------------+----------------------------+
04 | Variable_name            | Value                      |
05 +--------------------------+----------------------------+
06 | character_set_client     | utf8                       |
07 | character_set_connection | utf8                       |
08 | character_set_database   | latin1                     |
09 | character_set_filesystem |binary                    |
10 | character_set_results    | utf8                       |
11 | character_set_server     | latin1                     |
12 | character_set_system     | utf8                       |
13 | character_sets_dir       | /usr/share/mysql/charsets/ |
14 +--------------------------+----------------------------+

参数解析:

character_set_server:默认的内部操作字符集;
character_set_client:客户端来源数据使用的字符集;
character_set_connection:连接层字符集;
character_set_results:查询结果字符集;
character_set_database:当前选中数据库的默认字符集;
character_set_system:系统元数据(字段名等)字符集 ;

● MySQL字符集转换过程遵循以下四步

1)当使用insert into或者其他方法向数据库插入中文数据时,原始数据首先被当做character_set_client值所设定的字符集;

2)将插入的数据转换为character_set_connection值所设定的编码(相当于转换层);

3)character_set_connection转换结束后,将结果储存到"内部操作字符集",其确定方法如下:

■ 如果有,使用每个数据字段的CHARACTER SET设定值;
■ 若上述值不存在,则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展,非SQL标准);
■ 若上述值不存在,则使用对应数据库的DEFAULT CHARACTER SET设定值;
■ 若上述值不存在,则使用默认的character_set_server值对应的编码集;

4)如果遇到查询(select),MySQL将以上三步得到的"内部操作字符集"编码结果转换为character_set_results值所对应的编码集,返回给调用者;

这里有必要说明上面第3步中的前三条,下面分别演示"表中某字段单独的字符集修改方法","数据库默认字符集的修改方法"以及"数据表默认字符集的创建方法"

01 --1)表中某字段单独的字符集修改方法
02 --语法:
03 --ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
04 --或
05 ----ALTER TABLE tbl_name MODIFY c_name CHARACTER SET character_name [COLLATE ...];
06  
07 --例如,将表t28的uname字段修改成utf8编码,字符校对集为"utf8_general_ci",校对集是可选参数
08 mysql>altertablet28modifyunamevarchar(200)charactersetutf8collateutf8_general_ci;
09  
10 --2)数据表默认字符集的创建与修改在本系列文章"[mysql基础文档]-5-数据表创建修改与删除"中有详细说明,这里不再赘述。
11  
12 --3)数据库默认字符集的修改方法
13 --创建数据库时设定数据库编码集,ubuntu中不携带charset参数,默认使用latin1编码集
14 mysql>createdatabaseqingsword_comcharset=utf8;
15  
16 --修改已存在的数据库默认字符集
17 --语法:
18 --ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];
19  
20 --例如,将qingsword_com这个数据库的默认字符集修改成utf8,字符校对集为"utf8_general_ci"
21 mysql>alterdatabaseqingsword_comdefaultcharactersetutf8collateutf8_general_ci;
22  
23 --查看数据库编码(看到最后那个utf8了没,那个就是这个数据库的默认编码集)
24 mysql>showcreatedatabaseqingsword_com G
25 ************* 1. row *************
26        Database: qingsword_com
27 CreateDatabase:CREATEDATABASE`qingsword_com` /*!40100DEFAULTCHARACTERSETutf8 */
28  
29 --查看表编码
30 mysql>showcreatetablet28 G
31  
32 --查看t28表所有字段编码
33 mysql>showfullcolumnsfromt28G
34  
35 --最重要的一点,要明白第3步中的那些"DEFAULT CHARACTER SET设定值",对应的就是这一部分实例中这三个地方的值。

P.s:字符校对集,_ci(表示大小写不敏感),_cs(表示大小写敏感),_bin(表示按编码值比较)结尾。例如:在字符序'utf8_general_ci'下,字符'a'和'A'是等价的,MySQL中都有哪些字符校对集在下面一小段中会介绍到;

● 字符集转换参数值的修改方法

MySQL可用的编码集以及校对集查看方法如下:

1 --查看全部编码集
2 mysql>showcharacterset;
3  
4 --仅查看包含utf8的编码集
5 mysql>showcharactersetlike'%utf8%';

下面是"show variables like '%char%'"命令所显示的列表中,对应值的修改方法:

1 --比如,将character_set_client的值,修改成latin1,其他名称修改方法以此类推
2 mysql>setcharacter_set_client=latin1;

明白了这些必要的基础知识后,来看看下面的MySQL中文乱码实例分析吧。

0×2.MySQL中文乱码实例分析

实例环境为ubuntu系统,使用终端连接mysql,本地终端环境默认字符编码utf8:

01 --mysql默认的字符集参数如下
02 mysql>showvariableslike'%char%';
03 +--------------------------+----------------------------+
04 | Variable_name            | Value                      |
05 +--------------------------+----------------------------+
06 | character_set_client     | utf8                       |
07 | character_set_connection | utf8                       |
08 | character_set_database   | utf8                       |
09 | character_set_filesystem |binary                    |
10 | character_set_results    | utf8                       |
11 | character_set_server     | latin1                     |
12 | character_set_system     | utf8                       |
13 | character_sets_dir       | /usr/share/mysql/charsets/ |
14 +--------------------------+----------------------------+
15  
16 --首先创建一个表t50,设置表的内部编码为utf8,输入一个中文"码"字,显示毫无问题
17 mysql>createtablet50(tx text)charset=utf8;
18 mysql>insertintot50values('码');
19 mysql>select*fromt50;
20 +------+
21 | tx   |
22 +------+
23 | 码   |
24 +------+
25  
26 --下面,将客户端来源数据使用的字符集设置成gbk,再次插入一个中文字符
27 mysql>setcharacter_set_client=gbk;
28 mysql>insertintot50values('码');
29  
30 --根据本文第一部分字符集的转换步骤分析
31 --第1步,ubuntu终端本地字符集默认是utf8,但此时我们告诉数据库,本地终端所使用字符集是gbk,相当于欺骗了数据库
32 --第2步,数据库接收到这个utf8编码的字符,将其当做GBK编码,'码'字的utf8编码是三字节的,但gbk编码中文是2字节编码,所以数据库将这3字节数据前端补0,补充成4个字节,并当做两个gbk字符传递,在转换层根据character_set_connection设置的值,转换成了utf8(乱码在这一步产生)
33 --第3步,数据表的内部编码为utf8,无需再转换,直接储存
34 --第4步,使用select取出的时候,查询结果字符集character_set_results值也是utf8,所以数据库原封不动的将这个utf8编码数据返回给终端,终端也是utf8编码,显示如下
35 mysql>select*fromt50;
36 +------+
37 | tx   |
38 +------+
39 | 码   |
40 | 鐮   |
41 +------+
42  
43 --继续将返回字符集以及连接字符集都改成gbk,下面这条命令相当于一次性将"character_set_client","character_set_connection","character_set_results"都改成gbk编码
44 mysql>setnames gbk;
45  
46 mysql>showvariableslike'%char%';
47 +--------------------------+----------------------------+
48 | Variable_name            | Value                      |
49 +--------------------------+----------------------------+
50 | character_set_client     | gbk                        |
51 | character_set_connection | gbk                        |
52 | character_set_database   | utf8                       |
53 | character_set_filesystem |binary                    |
54 | character_set_results    | gbk                        |
55 | character_set_server     | latin1                     |
56 | character_set_system     | utf8                       |
57 | character_sets_dir       | /usr/share/mysql/charsets/ |
58 +--------------------------+----------------------------+
59  
60 --插入测试
61 mysql>insertintot50values('码');
62  
63 --返回结果更加奇怪了,不是说set names可以解决一切吗?其实,看过本文第一部分就会明白,别忘记数据表t50的编码集是utf8;
64  
65 --部分数据库在这一步会插入失败,返回一个错误,提示插入的数据过长,为什么会过长呢?因为我们连接mysql的终端使用的是utf8编码,插入一个字符的汉字相当于插入3个字节的数据,而gbk编码只接受一个字符占2个字节的汉字
66  
67 --根据字符集转换步骤分析:
68 --第1步,ubuntu终端本地字符集是utf8,但此时我们告诉数据库,本地终端所使用字符集是gbk,相当于欺骗了数据库;
69 --第2步,被当做gbk的utf8编码被扩充成4个字节编码,传至转换层(character_set_connection),转换层设置了gbk编码,所以原封不动的将数据向内传递(已经出现乱码了)
70 --第3步,因为数据表设定了默认utf8编码,所以4字节的gbk编码数据被转成了utf8编码储存
71 --第4步,select时,内部的utf8编码字符根据character_set_results的值又被转换成4字节的gbk编码,但本地终端是使用utf8编码呢,终端直接将这个4字节的gbk编码当做utf8编码来处理,此时utf8模板与这个4字节gbk编码完全对不上,所以使用'?'代替,细心的朋友可能发现了,我们输入的是一个字符,这里却出现了两个问号,就是因为字符的字节数被扩充成了4字节。
72  
73 mysql>select*fromt50;
74 +------+
75 | tx   |
76 +------+
77 | ��  |
78 | ��  |
79 | ��  |
80 +------+
81  
82 --看到这里,可能有人会觉得,那将t50表的编码改成gbk不就好了吗?实际上就算将t50表的编码改成gbk,本地终端使用的还是utf8编码,除非将本地终端也改成gbk,但被破坏的数据是无法恢复的。

P.s:总结,根据上面的实验可以得到一个很重要的结论,"character_set_client"以及"character_set_results"的编码设置,要和我们连接数据库使用的终端编码一致,"character_set_connection"连接层使用的字符集要大于或等于"character_set_client"以及"内部操作字符集"(可以根据本文第一部分字符转换过程分析,如果连接层的字符集小于我们传递给数据库的字符集,比如我们传递一个GBK中文字符给数据库,但是连接层使用了ascii编码,ascii编码根本没有包含中文字符,2字节的gbk编码被转换成2字节的ascii编码,这些中文编码大小已经超出了ascii的范围,所以在连接层转换的时候数据全部丢失,最后会得到一堆问号未知数据),"内部操作字符集",比如数据表的charset所设置的编码集要能够包含我们所需要储存的字符。

0×3.网页乱码解决方法

1)设置数据库"character_set_client"以及"character_set_results"值的编码与连接数据库的页面程序所使用的编码一致;

2)使用任何终端操作数据库时,确保终端所使用的编码与"character_set_client"以及"character_set_results"值的编码一致;

3)页面代码创建表时,使用charset参数定义表字符集,设置的字符集要能包含所有需要储存到表中的字符;

4)"character_set_connection"连接层使用的字符集要大于或等于"character_set_client"以及"内部操作字符集";

5)将以上所有字符集统一成一种字符集,避免内部字符集转换能够提高数据库效率;

P.s:中文环境下Windows命令行终端所使用的编码为GBK,win下phpmyadmin的编码也被设置成了gbk,但是网页的编码却是utf8,win下数据库的"character_set_client"以及"character_set_results"的编码全部被设置成了utf8,根据本文前两部分的分析很容易理解,用win命令行或phpmyadmin对数据库插入数据的时候,在网页上调用这些数据显示乱码的原因。

*转载请注明来自:晴刃(QingSword.COM)

*原文连接:http://www.qingsword.com/qing/1489.html

发表评论

注:*为必填

回复 的评论
*
选择
*
*