mysql服务器默认操作字符集,MySQL 字符集设置

数据库建库时默认是设置的latin1编码，查看系统的字符集和排序方式

show variables like

'character%';

SHOW VARIABLES LIKE

'collation_%';

SET NAMES gb2312;

show variables like 'character%';

SET NAMES x

相当于

SET character_set_client =

x;

SET character_set_results =

x;

SET character_set_connection = x;基本概念

•

字符(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设置的影响，对于比较之类的操作可能产生完全不同的结果，需要小心！

MySQL

字符编码是版本4.1引入的，支持多国语言，而且一些特性已经超过了其它大多数数据库管理系统。正因为这一特性才导致 MySQL

的乱码问题。

(以下摘自 MySQL 5.1

手册。更多内容可参见：http://dev.mysql.com/doc/refman/5.1/zh/charset.html)

字符集是一套符号和编码。校对规则是在字符集内用于比较字符的一套规则。让我们使用一个假想字符集的例子来区别清楚。

假设我们有一个字母表使用了四个字母：‘A’、‘B’、‘a’、‘b’。

我们为每个字母赋予一个数值：‘A’=0，‘B’= 1，‘a’= 2，‘b’= 3。

字母‘A’是一个符号，数字0是‘A’的编码，这四个字母和它们的编码组合在一起是一个字符集。

假设我们希望比较两个字符串的值(在if……else语句中我们经常做值的比较)：‘A’和‘B’。

比较的最简单的方法是查找编码：‘A’为0，‘B’为1。因为0 小于1，我们可以说‘A’小于‘B’。

我们做的仅仅是在我们的字符集上应用了一个校对规则。

校对规则是一套规则(在这种情况下仅仅是一套规则)：“对编码进行比较。”

我们称这种全部可能的规则中的最简单的校对规则为一个binary(二元)校对规则。

但是，如果我们希望小写字母和大写字母是等价的，

应该怎样？那么，我们将至少有两个规则：

(1)把小写字母‘a’和‘b’视为与‘A’和‘B’等价；

(2)然后比较编码。

我们称这是一个大小写不敏感的校对规则。比二元校对规则复杂一些。

在实际生活中，大多数字符集有许多字符：不仅仅是‘A’和‘B’，而是整个字母表，

有时候有许多种字母表，或者一个东方的(比如中文、日文、韩文、藏文、泰文等等)使用上千个字符的书写系统，

还有许多特殊符号和标点符号。

并且在实际生活中，大多数校对规则有许多个规则：

不仅仅是大小写不敏感，还包括重音符不敏感(“重音符” 是附属于一个字母的符号，象德语的‘?’符号)

和多字节映射(例如，作为规则‘?’=‘OE’就是两个德语校对规则的一种)。

(以上摘自MySQL 5.1

手册。更多内容可参见：http://dev.mysql.com/doc/refman/5.1/zh/charset.html)

MySQL 4.1.x开始支持以下这些事情

? 使用多种字符集(Character

Set)来存储字符

?

使用多种校对规则(Collation)来比较字符串

?

在同一台服务器、同一个数据库或甚至在同一个表中使用不同字符集或校对规则来混合字符串

? 允许定义任何级别的字符集和校对规则

MySQL 4.1及以上版本的字符集支持(Character Set

Support)有两个方面：

字符集(Character Set)和校对规则(Collation)。

字符集和校对规则有4个级别的默认设置：服务器(server)，数据库(database)，数据表(table)和连接(connection)。

MySQL

中是根据下面几个变量确定服务器端和客户端用的什么字符集：

character_set_client

客户端字符集

character_set_connection

客户端与服务器端连接采用的字符集

character_set_results　　　　　SELECT查询返回数据的字符集

character_set_database　　　　数据库采用的字符集

MySQL的字符集处理是这样的：

1、发送请求。

1)客户端发送请求到服务器端。

2)服务器端会把请求的数据从客户端字符集(character_set_client)转成服务器连接字符集(character_set_connection)。

3)然後服务器会检测存储区域(table，column)的字符集，

然后把数据从连接字符集(character_set_connection)转为存储区域(table，column)的字符集，然後再存储或者查询。

2、返回请求。

1)服务器将存储区域(table，column)的字符集转换成服务器连接字符集(character_set_connection)。

2)将服务器连接字符集(character_set_connection)转换成结果字符集(character_set_results)，再发送到客户端。

例如，我建立一个字符集为 gbk 的数据库(服务器端)。

(MySQL 4.1

开始，在建立数据库时要指定它的字符集和校对规则，不指定就用默认的字符集和校对规则。)

连接数据库的程序(客户端)使用 gb2312 字符集(如 windows

命令行下使用 MySQL ，或者 PHP 连接MySQL )，

那么在执行 insert 命令时，insert 的字符串将做一个 gb2312 到 gbk 的转换。

而 select 时，数据库中保存的数据会先经过 gbk 到 gb2312

的转换之后再给你(结果集)。

好，那么为什么升级3.23(或4.0)到4.1时会乱码？举个例子说明。

例如3.23的数据库中保存的是gbk编码的数据。

升级之前我将这些数据导出保存到文件里，这个文件的编码当然也是gbk的

(因为3.23不支持多语言，不会对数据进行转换，也就是前面说的“原封不动地保存，原封不动地读出”)。

然后我在4.1中建立一个数据库，字符集为A；客户端字符集为B。将刚才的gbk数据导入。

1)A=gbk，B=gbk

导入数据时数据不会被转换；读出时需要set names gbk(set

name命令下面将讲解)。

2)A=latin1，B=gbk

导入数据会进行gbk->latin1的转换，可能会丢失数据，产生乱码。

3)A=gbk，B=latin1

导入数据会进行latin1->gbk转换，可能会产生乱码。

4)A=latin1，B=latin1

导入数据时不会进行转换；读出时不需要set names gbk

。

大家可以看到，上面1)、4)才是正确的做法，即让A和B使用同样的字符集才不会乱码。

三、解决方案

了解了 MySQL 4.1.x

以上版本字符集处理的过程，我们就知道了怎么从原理上解决这个问题。

思路：让服务器端和客户端的字符集保持一致。

服务器端的编码是由字符集(Character

Set)和校对规则(Collation)决定的。

上面提到，MySQL

中是根据下面几个变量确定服务器端和客户端用的什么字符集：

character_set_client

客户端字符集

character_set_connection

客户端与服务器端连接采用的字符集

character_set_results　　　　　SELECT查询返回数据的字符集

character_set_database　　　　数据库采用的字符集

也就是说，只要保证这几个变量采用一致的字符集，就不会出现乱码问题了。

查看系统的字符集用下面的命令：

mysql> SHOW VARIABLES

LIKE 'character_set_%';

+--------------------------+-----------------------------------------+

| Variable_name | Value

|

+--------------------------+-----------------------------------------+

| character_set_client | utf8

|

| character_set_connection | utf8

|

| character_set_database | utf8

|

| character_set_filesystem | binary

|

| character_set_results | utf8

|

| character_set_server | utf8

|

| character_set_system | utf8

|

| character_sets_dir | E:\usr\MySQL

Server 5.0\share\charsets\ |

+--------------------------+-----------------------------------------+

8 rows in set (0.00

sec)

可以看到，我的这几个变量都是一致的。但如果不一致呢？网上许多教程告诉你“你set

names下就解决了”。

那么set names是什么呢？

set names实际上就是同时设置了 character_set_client

，character_set_connection和character_set_results

这三个系统变量。

例如在mysql命令行上输入 set names 'gbk'

命令等同于：

SET character_set_client =

gbk;

SET character_set_connection =

gbk;

SET character_set_results =

gbk;

很多情况下，这样设置了之后就能把乱码问题解决了。但是还是不能完全避免出现乱码的可能，为什么呢？

因为character_set_client

，character_set_connection 这两个变量仅用于保证

与 character_set_database 编码的一致，

而character_set_results 则用于保证 SELECT 返回的结果与程序的编码一致。

例如，你的数据库(character_set_database)用的是 utf8

的字符集，

那么你就要保证 character_set_client，character_set_connection

也是utf8的字符集。

而你的程序也许采用的并不是utf8 ，

比如你的程序用的是gbk ，那么你若把 character_set_results 也设置为 utf8

的话就会出现乱码问题。

此时你应该把 character_set_results

设置为gbk。这样就能保证数据库返回的结果与你的程序的编码一致。

到此应该就可以解决绝大多数我们遇到的乱码问题了，另外还必须强调的是，有时候乱码的出现有可能是以上几种原因混合造成的。

总而言之，我们应当尽量的保证数据库中的数据是正确的，就是客户端到服务器端或者服务器端到客户端转换的过程中不要产生乱码，那么问题处理起来就相对简单了。

总结

为便于大家记忆，总结为以下四点：

1、要保证发送的数据与数据库的字符集一致，即

character_set_client，character_set_connection与character_set_database

一致。

2、要保证数据库中存储的数据与数据库编码一致，即数据的编码与character_set_database一致。

3、要保证 SELECT 的返回与程序的编码一致，即

character_set_results 与程序(PHP、Java等)编码一致。

4、要保证程序编码与浏览器编码一致，即程序编码与

一致。