【澳门金沙vip】MySQL乱码问题终极指南

mysql的字符集设置众多,从客户端到连接到结果集,从服务器到库到表到列,都可以设置字符集,灵活很强大,但就是很容易出问题,如果不了解其机制,很容易就出现乱码问题。

基本概念
• 字符(Character)是指人类语言中最小的表义符号。例如’A’、’B’等;

给定一系列字符,对每个字符赋予一个数值,用数值来代表对应的字符,这一数值就是字符的编码(Encoding)。例如,我们给字符’A’赋予数值0,给字符’B’赋予数值1,则0就是字符’A’的编码;

给定一系列字符并赋予对应的编码后,所有这些字符和编码对组成的集合就是字符集(Character
Set)。例如,给定字符列表为{‘A’,’B’}时,{‘A’=>0,
‘B’=>1}就是一个字符集;
• 字符序(Collation)是指在同一字符集内字符之间的比较规则;

确定字符序后,才能在一个字符集上定义什么是等价的字符,以及字符之间的大小关系;

每个字符序唯一对应一种字符集,但一个字符集可以对应多种字符序,其中有一个是默认字符序(Default
Collation);

MySQL中的字符序名称遵从命名惯例:以字符序对应的字符集名称开头;以_ci(表示大小写不敏感)、_cs(表示大小写敏感)或_bin(表示按编码值比较)结尾。例如:在字符序“utf8_general_ci”下,字符“a”和“A”是等价的;
MySQL字符集设置
• 系统变量:
– character_set_server:默认的内部操作字符集
– character_set_client:客户端来源数据使用的字符集
– character_set_connection:连接层字符集
– character_set_results:查询结果字符集
– character_set_database:当前选中数据库的默认字符集
– character_set_system:系统元数据(字段名等)字符集
– 还有以collation_开头的同上面对应的变量,用来描述字符序。
• 用introducer指定文本字符串的字符集:
– 格式为:[_charset] ‘string’ [COLLATE collation]
– 例如:
• SELECT _latin1 ‘string’;
• SELECT _utf8 ‘你好’ COLLATE utf8_general_ci;

由introducer修饰的文本字符串在请求过程中不经过多余的转码,直接转换为内部字符集处理。
MySQL中的字符集转换过程

为了让大家尽量在工作中少受或者不受乱码的困扰,这里我结合之前其它同学在论坛的发帖,并结合自己的理解和实践,详细分析总结了一下,以飨各位看官。

  1. MySQL
    Server收到请求时将请求数据从character_set_client转换为character_澳门金沙vip,set_connection;
    2.
    进行内部操作前将请求数据从character_set_connection转换为内部操作字符集,其确定方法如下:
    • 使用每个数据字段的CHARACTER SET设定值;
    • 若上述值不存在,则使用对应数据表的DEFAULT CHARACTER
    SET设定值(MySQL扩展,非SQL标准);
    • 若上述值不存在,则使用对应数据库的DEFAULT CHARACTER SET设定值;
    • 若上述值不存在,则使用character_set_server设定值。
  2. 将操作结果从内部操作字符集转换为character_set_results。

关于字符集和乱码的基础知识这里就不详细说明了,但有一个问题需要特别强调一下:乱码是怎么产生的?这个问题相信很多同学都是模棱两可,或者没有认真想过,反正理解就是”字符编码“不对导致乱码,但没有真正想过为什么”字符编码“会导致乱码。答案其实很简单:“转换导致乱码”!根据这个原则来判断,各种情况就很简单了:

澳门金沙vip 1
常见问题解析

向默认字符集为utf8的数据表插入utf8编码的数据前没有设置连接字符集,查询时设置连接字符集为utf8

1)数据传送过程中不会导致乱码2)数据存储不会导致乱码3)数据输入和输出可能导致乱码4)数据接收和发送可能导致乱码


插入时根据MySQL服务器的默认设置,character_set_client、character_set_connection和character_set_results均为latin1;

更详细的解释:转换导致乱码是指本来是A字符集的数据被当成了B字符集进行解析,而不是说正确的A字符集转换为B字符集。例如:如下mysql字符处理机制流程图中,mysql客户端发送的实际上是2个gbk字符,但character_set_connection设置了utf8,于是mysql服务器将收到的4字节gbk数据按照utf8解析,得到1个中文字符+1个字节,这时就产生乱码了;


插入操作的数据将经过latin1=>latin1=>utf8的字符集转换过程,这一过程中每个插入的汉字都会从原始的3个字节变成6个字节保存;

如果character_set_connection
设置为gbk,mysql服务器收到数据后按照gbk解析,得到两个正确的中文,然后再转换为这两个中文对应的utf8编码,这就不会产生乱码。)


查询时的结果将经过utf8=>utf8的字符集转换过程,将保存的6个字节原封不动返回,产生乱码……

详细的处理机制如下图:

澳门金沙vip 2

我们模拟一下一条数据从插入到读取的处理流程,看看在整个流程中,字符集是如何辗转腾挪的。1.
客户端设定了自己的编码,接收用户的输入;2.
客户端将用户的输入“转换”成连接的编码 ===== 第一次转换3.
客户端将转换后的数据发送给服务器; ===== 传输不会导致编码转换4.
服务器收到客户端的数据,再判断数据列的字符集,进行字符转换 =====
第二次转换5. 服务器将数据存储 ===== 存储不会导致编码转换


向默认字符集为latin1的数据表插入utf8编码的数据前设置了连接字符集为utf8

略去前面的sql语句处理流程,从数据读取开始1. 服务器从存储读取数据 =====
存储不会导致编码转换,因此从存储读取也不需要2.
服务器判断当前连接返回结果的字符集, 将读取的数据转换为结果集要求的数据
===== 逆向的第一次转换,对应正向的第二次编码转换3.
服务器将数据发送给客户端 ===== 传输不会导致编码转换4.
客户端收到服务器的数据,根据客户端的字符集进行编码转换 =====
逆向第二次转换,对应正向第一次编码转换5. 客户端显示数据 =====
你能看到乱码的时候


插入时根据连接字符集设置,character_set_client、character_set_connection和character_set_results均为utf8;

有了这个流程,我们就很容易定位乱码可能产生的地方,以及产生乱码的字符集配置究竟是哪个了。理想的情况是整个流程中,所有涉及字符转换的地方都不需要转换,这样就不会产生乱码了。


插入数据将经过utf8=>utf8=>latin1的字符集转换,若原始数据中含有/u0000~/u00ff范围以外的Unicode字符,会因为无法在latin1字符集中表示而被转换为“?”(0x3F)符号,以后查询时不管连接字符集设置如何都无法恢复其内容了。

有了上面的理论分析后,我们再结合一个乱码的抓包实例,加深理解,其中有一些问题,请大家思考一下,看看是否真的理解了。

澳门金沙vip 3

环境:+————————–+—————————————————–+|
Variable_name | Value
|+————————–+—————————————————–+|
character_set_client | latin1 || character_set_connection | latin1
|| character_set_database | utf8 || character_set_filesystem |
binary || character_set_results | latin1 || character_set_server |
utf8 |

检测字符集问题的一些手段 • SHOW CHARACTER SET;

测试语句是插入一个中文字符“你”,其utf8编码为”0xE4 0xBD 0xA0″,

• SHOW COLLATION;

  1. latin1发送包

• SHOW VARIABLES LIKE ‘character%’;

思考一下1:为什么客户端和连接都设置了latin1,但最终发送的是正确的utf8编码呢?

• SHOW VARIABLES LIKE ‘collation%’;

  1. latin1接收包

• SQL函数HEX、LENGTH、CHAR_LENGTH

思考一下2:为什么接收到的还是正确的utf8编码?

• SQL函数CHARSET、COLLATION

  1. latin1不显示乱码

使用MySQL字符集时的建议

建立数据库/表和进行数据库操作时尽量显式指出使用的字符集,而不是依赖于MySQL的默认设置,否则MySQL升级时可能带来很大困扰;

思考一下3:为什么latin1显示了正确的utf8字符?


数据库和连接字符集都使用latin1时虽然大部分情况下都可以解决乱码问题,但缺点是无法以字符为单位来进行SQL操作,一般情况下将数据库和连接字符集都置为utf8是较好的选择;

  1. utf8接收包

• 使用mysql C
API时,初始化数据库句柄后马上用mysql_options设定MYSQL_SET_CHARSET_NAME属性为utf8,这样就不用显式地用SET
NAMES语句指定连接字符集,且用mysql_ping重连断开的长连接时也会把连接字符集重置为utf8;

思考一下4:为什么连接的字符集和数据库的字符集设置成一样了,接收的数据反而不是utf8了?

• 对于mysql PHP
API,一般页面级的PHP程序总运行时间较短,在连接到数据库以后显式用SET
NAMES语句设置一次连接字符集即可;但当使用长连接时,请注意保持连接通畅并在断开重连后用SET
NAMES语句显式重置连接字符集。

  1. utf8显示包

其他注意事项

my.cnf中的default_character_set设置只影响mysql命令连接服务器时的连接字符集,不会对使用libmysqlclient库的应用程序产生任何作用!

思考一下5:为什么连接的字符集和数据库的字符集设置成一样了,显示反而乱码了?


对字段进行的SQL函数操作通常都是以内部操作字符集进行的,不受连接字符集设置的影响。

怎么样,上面的思考题是否都有答案了,如果没有,相信下面这幅图能够帮助你:


SQL语句中的裸字符串会受到连接字符集或introducer设置的影响,对于比较之类的操作可能产生完全不同的结果,需要小心!/P>

这个抓包案例的字符变化图解:

澳门金沙vip 4

附:mysql字符编码操作技巧

在mysql客户端与mysql服务端之间,存在着一个字符集转换器。

mysql show variables like '%char%';+--------------------------+-----------------------------------------------------+| Variable_name | 说明 |+--------------------------+-----------------------------------------------------+| character_set_client | 客户端字符集 || character_set_connection | 当前连接字符集 || character_set_database | 数据库字符集 || character_set_filesystem | 文件系统字符集,不要修改,使用binary即可 || character_set_results | 返回结果集字符集 || character_set_server | 服务器默认字符集,当数据库、表、列没有设置时, || | 默认使用此字符集 || character_set_system | 固定为utf8 |+--------------------------+-----------------------------------------------------+ 

 

服务器的配置在服务器建立的时候就由DBA设置好了,不推荐后续再改通过SET
NAMES
utf8命令同时设置character_set_client/character_set_connection/character_set_results的字符集建议所有配置都设置成utf8

character_set_client        
 =>gbk:转换器就知道客户端发送过来的是gbk格式的编码

思考一下1:为什么客户端和连接都设置了latin1,但最终发送的是正确的utf8编码呢?客户端设置了latin1,而我的语句是从notepad++中写好的,是utf8格式的;中文utf8是3个字节,而latin1是按照单个字节解析的,虽然进行了转换,但不会导致二进制内容的变化,但实际上mysql客户端认为我输入了3个latin1字符;如果客户端设置的编码是2个字节的gbk,这时转换就会发生乱码,utf8的3个字节会被转换为1个gbk字符加上一个西欧字符

character_set_connection=>gbk:将客户端传送过来的数据转换成gbk格式

思考一下2:为什么接收到的还是正确的utf8编码?这是因为mysql服务器从将数据从“列”的编码转换为latin1了,而列存储的数据并不是真正的utf8的中文“你”对应的”0xe4
0xbd 0xa0″,而是后面抓包看到的“c3a4 c2bd
c2a0”,mysql服务器将utf8的c3a4转换为latin1的0xe4,c2bd转换为0xbd,
c2a0转换为0xa0

character_set_results        =>gbk:

思考一下3:为什么latin1显示了正确的utf8字符?因为mysql客户端收到了mysql服务器转换后的”0xe4
0xbd
0xa0″,并把这个数据当做latin1的3个字符处理,然后抛给终端,SecureCRT又把这三个latin1当做uft8处理,结果中文的“你”就显示出来了。

 

思考一下4:为什么连接的字符集和数据库的字符集设置成一样了,接收的数据反而不是utf8了?字符集都一样的情况下,整个流程中不需要进行编码转换,直接将存储的“c3a4
c2bd c2a0”返回给客户端

注:以上三个字符集可以使用set names gbk来统一进行设置

思考一下5:为什么连接的字符集和数据库的字符集设置成一样了,显示反而乱码了?参考思考4,客户端收到数据后也直接抛给终端显示,终端认为是两个utf8字符,并且找到了对应字符并显示,但我们看不懂,所以知道是乱码了,但这两个字符显示并没有错,如果真正找不到字符,可能会显示问号或者字符集规定的缺省符号。

例子:

以上就是关于MySQL乱码问题大集合,希望能够帮助大家解决MySQL乱码问题,谢谢大家的阅读。

create table test(

name varchar(64) NOT NULL

)charset utf8;#这里的utf8表示服务器端的字符编码

 

首先,往数据表test中插入一条数据

inert into test values(‘测试’);

则,数据“测试”在数据库中是以“utf8”格式保存的

过程: