[转]深入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_set_connection；

2. 进行内部操作前将请求数据从character_set_connection转换为内部操作字符集，其确定方法如下：

• 使用每个数据字段的CHARACTER SET设定值；

• 若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展，非SQL标准)；

• 若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值；

• 若上述值不存在，则使用character_set_server设定值。

3. 将操作结果从内部操作字符集转换为character_set_results。

常见问题解析

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

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

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

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

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

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

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

检测字符集问题的一些手段

• SHOW CHARACTER SET;

• SHOW COLLATION;

• SHOW VARIABLES LIKE ‘character%’;

• SHOW VARIABLES LIKE ‘collation%’;

• SQL函数HEX、LENGTH、CHAR_LENGTH

• SQL函数CHARSET、COLLATION

使用MySQL字符集时的建议

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

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

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

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

其他注意事项

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

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

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

 

 

 

附加：

 

 

character-set-server/default-character-set：服务器字符集，默认情况下所采用的。
character-set-database：数据库字符集。
character-set-table：数据库表字符集。
优先级依次增加。所以一般情况下只需要设置character-set-server，而在创建数据库和表时不特别指定字符集，这样统一采用character-set-server字符集。
character-set-client：客户端的字符集。客户端默认字符集。当客户端向服务器发送请求时，请求以该字符集进行编码。
character-set-results：结果字符集。服务器向客户端返回结果或者信息时，结果以该字符集进行编码。
在客户端，如果没有定义character-set-results，则采用character-set-client字符集作为默认的字符集。所以只需要设置character-set-client字符集。

要处理中文，则可以将character-set-server和character-set-client均设置为GB2312，如果要同时处理多国语言，则设置为UTF8。

关于MySQL的中文问题

解决乱码的方法是，在执行SQL语句之前，将MySQL以下三个系统参数设置为与服务器字符集character-set-server相同的字符集。
character_set_client：客户端的字符集。
character_set_results：结果字符集。
character_set_connection：连接字符集。
设置这三个系统参数通过向MySQL发送语句：set names gb2312

关于GBK、GB2312、UTF8

UTF- 8：Unicode Transformation Format-8bit，允许含BOM，但通常不含BOM。是用以解决国际上字符的一种多字节编码，它对英文使用8位（即一个字节），中文使用24为（三 个字节）来编码。UTF-8包含全世界所有国家需要用到的字符，是国际编码，通用性强。UTF-8编码的文字可以在各国支持UTF8字符集的浏览器上显 示。如，如果是UTF8编码，则在外国人的英文IE上也能显示中文，他们无需下载IE的中文语言支持包。

GBK是国家标准GB2312基础上扩容后兼容GB2312的标准。GBK的文字编码是用双字节来表示的，即不论中、英文字符均使用双字节来表示， 为了区分中文，将其最高位都设定成1。GBK包含全部中文字符，是国家编码，通用性比UTF8差，不过UTF8占用的数据库比GBD大。

GBK、GB2312等与UTF8之间都必须通过Unicode编码才能相互转换：
GBK、GB2312－－Unicode－－UTF8
UTF8－－Unicode－－GBK、GB2312

对于一个网站、论坛来说，如果英文字符较多，则建议使用UTF－8节省空间。不过现在很多论坛的插件一般只支持GBK。

GB2312是GBK的子集，GBK是GB18030的子集
GBK是包括中日韩字符的大字符集合
如果是中文的网站 推荐GB2312 GBK有时还是有点问题
为了避免所有乱码问题，应该采用UTF-8，将来要支持国际化也非常方便
UTF-8可以看作是大字符集，它包含了大部分文字的编码。
使用UTF-8的一个好处是其他地区的用户（如香港台湾）无需安装简体中文支持就能正常观看你的文字而不会出现乱码。

gb2312是简体中文的码
gbk支持简体中文及繁体中文
big5支持繁体中文
utf-8支持几乎所有字符

首先分析乱码的情况
1.写入数据库时作为乱码写入
2.查询结果以乱码返回
究竟在发生乱码时是哪一种情况呢？
我们先在mysql 命令行下输入
show variables like '%char%';
查看mysql 字符集设置情况:

mysql> show variables like '%char%';
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | gbk                                    |
| character_set_connection | gbk                                    |
| character_set_database   | gbk                                    |
| character_set_filesystem | binary                                 |
| character_set_results    | gbk                                    |
| character_set_server     | gbk                                    |
| character_set_system     | utf8                                   |
| character_sets_dir       | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+

在查询结果中可以看到mysql 数据库系统中客户端、数据库连接、数据库、文件系统、查询
结果、服务器、系统的字符集设置
在这里，文件系统字符集是固定的，系统、服务器的字符集在安装时确定，与乱码问题无关
乱码的问题与客户端、数据库连接、数据库、查询结果的字符集设置有关
*注：客户端是看访问mysql 数据库的方式，通过命令行访问，命令行窗口就是客户端，通
过JDBC 等连接访问，程序就是客户端
我们在向mysql 写入中文数据时，在客户端、数据库连接、写入数据库时分别要进行编码转
换
在执行查询时，在返回结果、数据库连接、客户端分别进行编码转换
现在我们应该清楚，乱码发生在数据库、客户端、查询结果以及数据库连接这其中一个或多
个环节
接下来我们来解决这个问题
在登录数据库时，我们用mysql --default-character-set=字符集-u root -p 进行连接，这时我们
再用show variables like '%char%';命令查看字符集设置情况，可以发现客户端、数据库连接、
查询结果的字符集已经设置成登录时选择的字符集了
如果是已经登录了，可以使用set names 字符集;命令来实现上述效果，等同于下面的命令：
set character_set_client = 字符集
set character_set_connection = 字符集
set character_set_results = 字符集

如果碰到上述命令无效时，也可采用一种最简单最彻底的方法：

一、Windows

1、中止MySQL服务
2、在MySQL的安装目录下找到my.ini，如果没有就把my-medium.ini复制为一个my.ini即可
3、打开my.ini以后，在[client]和[mysqld]下面均加上default-character-set=utf8，保存并关闭
4、启动MySQL服务

要彻底解决编码问题，必须使

| character_set_client     | gbk                                    |
| character_set_connection | gbk                                    |
| character_set_database   | gbk                                    |
| character_set_results    | gbk                                    |
| character_set_server     | gbk                                    |
| character_set_system     | utf8     

这些编码相一致，都统一。

如果是通过JDBC 连接数据库，可以这样写URL：
URL=jdbc:mysql://localhost:3306/abs?useUnicode=true&characterEncoding=字符集
JSP 页面等终端也要设置相应的字符集
数据库的字符集可以修改mysql 的启动配置来指定字符集，也可以在create database 时加上
default character set 字符集来强制设置database 的字符集
通过这样的设置，整个数据写入读出流程中都统一了字符集，就不会出现乱码了
为什么从命令行直接写入中文不设置也不会出现乱码？
可以明确的是从命令行下，客户端、数据库连接、查询结果的字符集设置没有变化
输入的中文经过一系列转码又转回初始的字符集，我们查看到的当然不是乱码
但这并不代表中文在数据库里被正确作为中文字符存储
举例来说，现在有一个utf8 编码数据库，客户端连接使用GBK 编码，connection 使用默认
的ISO8859-1（也就是mysql 中的latin1），我们在客户端发送“中文”这个字符串，客户端
将发送一串GBK 格式的二进制码给connection 层，connection 层以ISO8859-1 格式将这段
二进制码发送给数据库，数据库将这段编码以utf8 格式存储下来，我们将这个字段以utf8
格式读取出来，肯定是得到乱码，也就是说中文数据在写入数据库时是以乱码形式存储的，
在同一个客户端进行查询操作时，做了一套和写入时相反的操作，错误的utf8 格式二进制
码又被转换成正确的GBK 码并正确显示出来。

摘自：http://tda7088.blog.163.com/blog/static/294411542009112033154929/

 

 

Mysql编码处理

2009-06-09 18:39

当一个Mysql连接请求从客户端传来的时候，服务器认为它的编码是character_set_client，
然后会根据character_set_connection把请求进行转码，从character_set_client转成character_set_connection，
然后更新到数据库的时候，再转化成字段所对应的编码

如果使用了set names指令，那么可以修改character_set_connection的值，
也同时会修改character_set_client和character_set_results的值

当从数据库查询数据返回结果的时候，将字段从默认的编码转成character_set_results

实例分析：
DB采用utf8存储数据，默认连接也使用utf8，客户端请求采用gb2312，调用了set names “gb2312”
当客户端gb2312的请求过来的时候，由于先前调用了set names，所以character_set_client、
character_set_connection、character_set_results都被改成了gb2312，
所以一路过来直到更新到db字段之前都是gb2312编码，然后因为db字段是utf8的，
所以会再进行一次转码，从gb2312转成utf8，最终以utf8格式存储在数据库中
当客户端要查询显示一个gb2312的内容时，也先set names “gb2312”，
这样会把character_set_results改成gb2312，当服务器查询出结果返回的时候就会从utf8转成gb2312，
适合页面显示的需求了

如果没有使用set names，那么从客户端过来的gb2312将一直被当成是character_set_client编码（比如说是utf8），
然后一路因为character_set_connection和character_set_client，所以不会进行转码，然后直到更新db字段都不会去转码，
所以就把请求中的内容直接更新为db字段的值了，就出现了乱码

所以要解决乱码问题的关键是要正确识别客户端过来的请求的编码，即character_set_client，同时因为要考虑到页面的显示，所以也 要考虑到返回的结果的编码（character_set_results）必须和页面显示的编码一致，而 character_set_connection貌似没有很大用处？

一点补充：
如果配置文件中[mysql]default-character-set=gb2312，那么效果和每次set names “gb2312”是一样的，
每个连接都会将character_set_client、character_set_connection、character_set_results都被设置为gb2312

 

 

------------------------------------------------------------------------------------------------------

我们传统意义上说的编码其实是指字符集，它包括两个方面，一个是存储的字符，另外一个是映射关系，也就是真正的编码。各种字符集的存储的字符都是差不多的，就那么几个字符，而编码却是各不相同，是真正发挥威力的地方。
原来的系统数据存储采用gbk字符集，因为版本原因，升级后系统必须采用latin1字符集来存储，所以新的数据库中存储的是gbk的字符，而使用的是latin1的编码。所以这种数据只能在需要显示gbk的页面上正确显示，在显示其他字符集的地方就会是乱码。
页面显示gbk的流程是：程序（或者mysql配置文件中）中设置了结果集采用latin1，mysql发现latin1就是当前字段的实际字符集，所以 不进行任何转化，直接返回，但是这个时候实际上返回的是gbk的字符序列，当页面得到这个字符序列进行显示的时候，因为要显示gbk的数据，所以会采用 gbk编码对得到的字符序列进行编码，得到实际的字符，而用gbk编码对gbk字符序列进行编码自然不会出错。
而如果要显示utf8编码，则页面显示的时候会采用utf8编码对gbk字符序列进行编码，自然得到的是乱码了。
由于有两个数据库之前采用的是gbk的字符集，要在utf8编码下显示就存在问题，所以必须对存储的数据进行转换。而mysql查询的过程中不是自己会根据设定的编码进行转换么？但是不幸的是，它自带的这个功能在我们这样的情况下没有任何用处。
Mysql编码转换中涉及到的三个变量我们上面提到过，是character_set_client、 character_set_connection、character_set_results，character_set_client是插入数据 的时候采用的字符集，character_set_connection是查询字符串采用的字符集，character_set_results是查询结 果需要返回的字符集。这三个变量的使用应该需要有字符串和编码统一的前提，否则可能会出乱子，莫名其妙的乱码问题就会呈现在你面前。而我们的数据存储采用 的字符和编码是不统一的，所以不能直接使用这三个变量来编码，所以只能在程序中将数据进行编码转换。因为我们最终要显示的是utf8数据，所以我们存储的 必须是utf8的字符序列（原因和上面介绍的显示gbk数据的时候一样），而我们从老的数据库中查询得到的是gbk的字符串，编码是latin1的，要插 入到新的数据库中需要经历这么几个转换，第一步，要根据character_set_connection进行转换，因为我们默认 character_set_connection是latin1编码，和我们存储的编码一致，所以不需要进行任何转换，然后第二步是插入到数据库中，因 为character_set_client也是latin1，所以也不需要进行任何转换，所以我们需要插入的字符串序列应该是和根据 character_set_connection进行转换之前的字符串序列一致的，而我们最终需要的是utf8的字符串序列，所以在根据 character_set_connection进行转换之前也必须是utf8的字符串序列，也就是说我们要在得到老的数据后，将gbk字符串序列转换 成utf8字符串序列。Php自带的mb_convert_encoding函数应该能够完成这个艰巨的任务。