MySQL字符集详解

待我称王封你为后i 2024-02-18 21:16 11阅读 0赞

一、字符集和校验规则

字符集是一套符合和编码，校验规则（collation）是在字符集内用于比较字符的一套规则，即字符集的排序规则。MySQL可以使用对种字符集和检验规则来组织字符。

MySQL服务器可以支持多种字符集，在同一台服务器，同一个数据库，甚至同一个表的不同字段都可以指定使用不同的字符集，相比oracle等其他数据库管理系统，在同一个数据库只能使用相同的字符集，MySQL明显存在更大的灵活性。

每种字符集都可能有多种校对规则，并且都有一个默认的校对规则，并且每个校对规则只是针对某个字符集，和其他的字符集么有关系。

在MySQL中，字符集的概念和编码方案被看做是同义词，一个字符集是一个转换表和一个编码方案的组合。

Unicode（Universal Code）是一种在计算机上使用的字符编码。Unicode 是为了解决传统的字符编码方案的局限而产生的，它为每种语言中的每个字符设定了统一并且唯一的二进制编码，以满足跨语言、跨平台进行文本转换、处理的要求。Unicode存在不同的编码方案，包括Utf-8，Utf-16和Utf-32。Utf表示Unicode Transformation Format。

#### 二、查看mysql字符集方法 ####

##### 1、查看mysql服务器支持的字符集 #####

mysql> show character set;

mysql> select \* from information\_schema.character\_sets;

mysql> select character\_set\_name, default\_collate\_name, description, maxlen from

information\_schema.character\_sets;

##### 2、查看字符集的校对规则 #####

mysql> show collation;

mysql> show collation like 'utf8';

mysql> select \* from information\_schema.collations where collation\_name like 'utf8%';

##### 3、查看当前数据库的字符集 #####

mysql> show variables like 'character%';

\+--------------------------+----------------------------------+

| Variable\_name | Value |

\+--------------------------+----------------------------------+

| character\_set\_client | utf8 |

| character\_set\_connection | utf8 |

| character\_set\_database | latin1 |

| character\_set\_filesystem | utf8 |

| character\_set\_results | utf8 |

| character\_set\_server | utf8 |

| character\_set\_system | utf8 |

| character\_sets\_dir | /usr/local/mysql/share/charsets/ |

\+--------------------------+----------------------------------+

8 rows in set (0.00 sec)

名词解释：

character\_set\_client：客户端请求数据的字符集  
character\_set\_connection：客户机/服务器连接的字符集  
character\_set\_database：默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，那就使用 character\_set\_server指定的字符集，这个变量建议由系统自己管理，不要人为定义。  
character\_set\_filesystem：把os上文件名转化成此字符集，即把 character\_set\_client转换character\_set\_filesystem， 默认binary是不做任何转换的

character\_set\_results：结果集，返回给客户端的字符集  
character\_set\_server：数据库服务器的默认字符集  
character\_set\_system：系统字符集，这个值总是utf8，不需要设置。这个字符集用于数据库对象（如表和列）的名字，也用于存储在目录表中的函数的名字。

##### 4、查看当前数据库的校对规则 #####

mysql> show variables like 'collation%';

\+----------------------+-------------------+

| Variable\_name | Value |

\+----------------------+-------------------+

| collation\_connection | utf8\_general\_ci |

| collation\_database | latin1\_swedish\_ci |

| collation\_server | utf8\_general\_ci |

\+----------------------+-------------------+

3 rows in set (0.01 sec)

名词解释：

collation\_connection 当前连接的字符集。  
collation\_database 当前日期的默认校对。每次用USE语句来“跳转”到另一个数据库的时候，这个变量的值就会改变。如果没有当前数据库，这个变量的值就是collation\_server变量的值。  
collation\_server 服务器的默认校对。

排序方式的命名规则为：字符集名字\_语言\_后缀，其中各个典型后缀的含义如下：  
1）\_ci：不区分大小写的排序方式  
2）\_cs：区分大小写的排序方式  
3）\_bin：二进制排序方式，大小比较将根据字符编码，不涉及人类语言，因此\_bin的排序方式不包含人类语言

#### 三、MySQL字符集的设置 ####

##### 1、概述 #####

MySQL字符集设置分为两类：

1）创建对象的默认值。

2）控制server和client端交互通信的配置。

###### 1、创建对象的默认值 ######

字符集合校对规则有4个级别的默认设置：

1）服务器级别；

2）数据库级别；

3）表级别、列级别；

4）连接级别。

更低级别的设置会集成高级别的设置。

这里有一个通用的规则：先为服务器或者数据库选择一个合理的字符集，然后根据不同的实际情况，让某个列选择自己的字符集。

###### 2、控制server和client端交互通信的配置 ######

大部分MySQL客户端都不具备同时支持多种字符集的能力，每次都只能使用一种字符集。

客户和服务器之间的字符集转换工作是由如下几个MySQL系统变量控制的。

1）character\_set\_server：mysql server默认字符集。

2）character\_set\_database：数据库默认字符集。

3）character\_set\_client：MySQL server假定客户端发送的查询使用的字符集。

4）character\_set\_connection：MySQL Server接收客户端发布的查询请求后，将其转换为character\_set\_connection变量指定的字符集。

5）character\_set\_results：mysql server把结果集和错误信息转换为character\_set\_results指定的字符集，并发送给客户端。

6）**character\_set\_system**：系统元数据(字段名等)字符集

还有以**collation\_**开头的同上面对应的变量，用来描述字符序。

注意事项：

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

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

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

##### 3、默认情况下字符集选择规则 #####

(1)编译MySQL 时，指定了一个默认的字符集，这个字符集是 latin1;  
(2)安装MySQL 时，可以在配置文件 (my.cnf) 中指定一个默认的的字符集，如果没指定，这个值继承自编译时指定的;  
(3)启动mysqld 时，可以在命令行参数中指定一个默认的的字符集，如果没指定，这个值继承自配置文件中的配置,此时character\_set\_server被设定为这个默认的字符集;  
(4)当创建一个新的数据库时，除非明确指定，这个数据库的字符集被缺省设定为character\_set\_server;  
(5)当选定了一个数据库时，character\_set\_database被设定为这个数据库默认的字符集;  
(6)在这个数据库里创建一张表时，表默认的字符集被设定为character\_set\_database，也就是这个数据库默认的字符集;  
(7)当在表内设置一栏时，除非明确指定，否则此栏缺省的字符集就是表默认的字符集;

##### 2、分述 #####

###### 2.1、为列分配字符集 ######

属于同一个表的不同列可以有不同的字符集，如果没有为一个列显示的定义字符集就使用默认字符集。创建一个表的时候，若显示的为列指定字符集，则字符集作为数据类型选项包含在其中，要放在数据类型后面及空指定和主键前面。

例如：

create table column\_charset(

c1 char(10) character set utf8 not null,

c2 char(10) char set utf8,

c3 varchar(10) charset utf8,

c4 varchar(10)) engine=innodb;

***注意：character set可以简写为char set和charset***

使用show create table table\_name;命令查看column\_charset建表语句：

mysql> show create table column\_charset\\G;

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: column\_charset

Create Table: CREATE TABLE \`column\_charset\` (

\`c1\` char(10) CHARACTER SET utf8 NOT NULL,

\`c2\` char(10) CHARACTER SET utf8 DEFAULT NULL,

\`c3\` varchar(10) CHARACTER SET utf8 DEFAULT NULL,

\`c4\` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.01 sec)

ERROR:

No query specified

插入数据，感受一下效果：

mysql> insert into column\_charset(c1,c2,c3,c4) value("图灵","图灵","图灵","chavin");

Query OK, 1 row affected (0.01 sec)

mysql> select \* from column\_charset;

\+--------+--------+--------+--------+

| c1 | c2 | c3 | c4 |

\+--------+--------+--------+--------+

| 图灵 | 图灵 | 图灵 | chavin |

\+--------+--------+--------+--------+

1 row in set (0.00 sec)

###### 2.2、为表分配字符集 ######

create table table\_charset(

c1 varchar(10),

c2 varchar(10))engine=innodb default charset=utf8;

***注意：为表指定字符集可以使用以下几种方式：***

***default charset=utf8;***

***charset=utf8;***

***default character set=utf8;***

***character set=utf8;***

***default char set=utf8;***

***char set=utf8;***

检查建表语句：

mysql> show create table table\_charset\\G;

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: table\_charset

Create Table: CREATE TABLE \`table\_charset\` (

\`c1\` varchar(10) DEFAULT NULL,

\`c2\` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

测试：

mysql> insert into table\_charset(c1,c2) values('图灵','图灵');

Query OK, 1 row affected (0.01 sec)

mysql> select \* from table\_charset;

\+--------+--------+

| c1 | c2 |

\+--------+--------+

| 图灵 | 图灵 |

\+--------+--------+

1 row in set (0.00 sec)

###### 2.3、为数据库指定字符集 ######

创建的每个数据库都有一个默认字符集，如果没有指定，就用latin1。

create database dbking charset=utf8;

***注意：创建数据库分配字符集可以采用以下几种子句：***

***charset=utf8;***

***default charset=utf8;***

***charset utf8;***

***default charset utf8;***

***char set=utf8;***

***default char set=utf8;***

***char set utf8;***

***default char set utf8;***

***character set=utf8;***

***default character set=utf8;***

***character set utf8;***

***default character set utf8;***

使用show create database db\_name;命令查看数据库创建语句：

mysql> show create database dbking;

\+----------+-----------------------------------------------------------------+

| Database | Create Database |

\+----------+-----------------------------------------------------------------+

| dbking | CREATE DATABASE \`dbking\` /\*!40100 DEFAULT CHARACTER SET utf8 \*/ |

\+----------+-----------------------------------------------------------------+

1 row in set (0.00 sec)

###### 2.4、为列分配校对规则 ######

每个列都应该有一个校对，如果没有显示指定，MySQL就使用属于该字符集的默认校对。如果指定了一个字符集和一个校对，字符集应该放在前面。

create table column\_collate(

c1 varchar(10) charset utf8 collate utf8\_romanian\_ci not null,

c2 varchar(10) charset utf8 collate utf8\_spanish\_ci)engine=innodb;

查看表的校验规则信息：

mysql> select table\_name,column\_name,collation\_name

from information\_schema.columns

where table\_name='column\_collate';

\+----------------+-------------+------------------+

| table\_name | column\_name | collation\_name |

\+----------------+-------------+------------------+

| column\_collate | c1 | utf8\_romanian\_ci |

| column\_collate | c2 | utf8\_spanish\_ci |

\+----------------+-------------+------------------+

2 rows in set (0.04 sec)

注意：字符集和校对在处理字符表达式的过程中扮演着重要角色。我们不能比较两个属于不同校对的不同字符值。例如：

mysql> insert into column\_collate(c1,c2) values('A','A');

Query OK, 1 row affected (0.22 sec)

mysql> select \* from column\_collate;

\+----+------+

| c1 | c2 |

\+----+------+

| A | A |

\+----+------+

1 row in set (0.00 sec)

mysql> select \* from column\_collate where c1=c2;

ERROR 1267 (HY000): Illegal mix of collations (utf8\_romanian\_ci,IMPLICIT) and (utf8\_spanish\_ci,IMPLICIT) for operation '='

###### 2.5、为表指定校对规则 ######

create table table\_collate(

c1 varchar(10),

c2 varchar(10))engine=innodb default charset utf8 collate utf8\_romanian\_ci;

检查表的校对规则：

mysql> select table\_name,column\_name,collation\_name from information\_schema.columns where table\_name='table\_collate';

\+---------------+-------------+------------------+

| table\_name | column\_name | collation\_name |

\+---------------+-------------+------------------+

| table\_collate | c1 | utf8\_romanian\_ci |

| table\_collate | c2 | utf8\_romanian\_ci |

\+---------------+-------------+------------------+

2 rows in set (0.00 sec)

###### 2.6、为数据库指定校对规则 ######

create database dbking102 default charset utf8 collate utf8\_romanian\_ci;

查看数据库定义语句：

mysql> show create database dbking102\\G;

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Database: dbking102

Create Database: CREATE DATABASE \`dbking102\` /\*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8\_romanian\_ci \*/

1 row in set (0.00 sec)

###### 2.7、字符直接量字符集 ######

如果没有显示指定，那么字符直接量的字符集就是数据库的默认字符集。如果要显示分配另一个字符集，需要把字符集的名字放在直接量前面，并且要在字符集前面加上下划线。

mysql> select _utf8'语言 Language 言語 язык';

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

| 语言 Language 言語 язык     |

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

| 语言 Language 言語 язык     |

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

###### 2.8、修改和设置MySQL服务器级别字符集 ######

MySQL服务器支持众多不同的字符集，这类字符集可在编译时和运行时指定。

###### 1） 编译时指定 ######

编译时可指定默认字符集和默认校对规则，要想同时更改默认字符集和校对规则，要同时使用--with-charset和--with-collation选项。校对规则必须是字符集的合法校对规则。

./configure -- with-charset=CHARSET --with-collation=COLLATION

通过configure选项--with-extra-charsets=LIST，可以定义在服务器中再定义增加字符集。LIST 指下面任何一项：  
a.空格间隔的一系列字符集名  
b.complex -，以包括不能动态装载的所有字符集  
c.all –，以将所有字符集包括进二进制

./configure -- with-charset=CHARSET --with-collation=COLLATION --with-extra-charsets=all

###### 2） 在参数文件my.cnf中指定 ######

\[mysqld\]

character\_set\_server=utf8

\--影响参数：character\_set\_server 和 character\_set\_database

\--注意：修改后要重启数据库才能生效。

\[client\]

default-character-set=utf8

\--影响参数：character\_set\_client，character\_set\_connection 和character\_set\_results。

\--注意：修改后无需重启数据库。  
3） 在启动参数前指定

./mysqld --character-set-server=utf8 &

\--影响参数：character\_set\_server 和 character\_set\_database  
4）在mysql客户端登陆时通过--default-character-set指定

mysql -uroot -pmysql --default-character-set=utf8

\--影响参数：set character\_set\_client，set character\_set\_connection，set character\_set\_results。

###### 5）临时指定 ######

a）分别指定

mysql> SET character\_set\_client = utf8;

mysql> SET character\_set\_connection = utf8;

mysql> SET character\_set\_database = utf8;

mysql> SET character\_set\_results = utf8;

mysql> SET character\_set\_server = utf8;

b）mysql客户端使用：set names utf8;

等同于

set character\_set\_client=utf8;

set character\_set\_connection=utf8;

set character\_set\_results=utf8;

c）set character set utf8;

等同于

set character\_set\_client=utf8;

set character\_set\_results=utf8;

set collation\_connection=@@collation\_database;

##### 3、总结 #####

下面介绍下几个MYSQL命令：  
1）show character set;或show char set;  
查看数据库支持的所有字符集  
2）status;或\\s;  
查看当前状态 里面包括当然的字符集设置  
3）show variables like 'char%';  
查看系统字符集设置，包括所有的字符集设置  
4）show table status from sqlstudy like '%countries%';  
查看sqlstudy数据库中表的字符集设置  
5）show full columns from countries;  
查看表列的字符集设置，关键是在同一个表中，每列可以设置成不同的字符集  
知道怎么查看字符集了，下面我来说下如何设置这些字符集  
1.修改服务器级  
a. 临时更改：  
mysql>SET GLOBAL character\_set\_server=utf8;  
b. 永久更改：  
修改my.cnf文件  
\[mysqld\]  
character-set-server=utf8  
2.修改数据库级  
a. 临时更改：  
mysql>SET GLOBAL character\_set\_database=utf8;  
b. 永久更改：  
改了服务器级就可以了  
3.修改表级  
mysql>ALTER TABLE table\_name DEFAULT CHARSET utf8;  
更改了后永久生效  
4.修改列级  
修改示例：  
mysql>alter table \`products\` change \`products\_model\` \`products\_model\` varchar( 20 )  
character set utf8 collate utf8\_general\_ci null default null;  
更改了后永久生效  
5.更改连接字符集  
a. 临时更改：  
mysql> set names utf8;  
b. 永久更改：  
修改my.cnf文件  
在\[client\]中增加：  
default-character-set=utf8  
执行SQL语句时信息的路径是这样的  
信息输入路径：client→connection→server;  
信息输出路径：server→connection→results.

#### 四、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。

下图源自于《高性能MySQL》中关于字符集转换的图解：

[![clip\_image002][clip_image002]][clip_image002_clip_image002]

#### 五、MySQL数据库乱码原因解析及案例 ####

1、产生乱码的根本原因  
1）客户机没有正确地设置client字符集，导致原先的SQL语句被转换成connection所指字符集，而这种转换，是会丢失信息的，如果client是utf8格式，那么如果转换成gb2312格式，这其中必定会丢失信息，反之则不会丢失。一定要保证connection的字符集大于client字符集才能保证转换不丢失信息。  
2）数据库字体没有设置正确，如果数据库字体设置不正确，那么connection字符集转换成database字符集照样丢失编码，原因跟上面一样。

##### 2、乱码或数据丢失 #####

character\_set\_client:我们要告诉服务器，我给你发送的数据是什么编码？  
character\_set\_connection:告诉字符集转换器，转换成什么编码？  
character\_set\_results:查询的结果用什么编码？  
如果以上三者都为字符集N,可简写为set names 'N';

###### 2.1 乱码问题 ######

模拟情景1：

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

插入时根据MySQL服务器的默认设置，character\_set\_client、character\_set\_connection和character\_set\_results均为latin1；  
插入操作的数据将经过latin1=>latin1=>utf8的字符集转换过程，这一过程中每个插入的汉字都会从原始的3个字节变成6个字节保存；  
查询时的结果将经过utf8=>utf8的字符集转换过程，将保存的6个字节原封不动返回，产生乱码……

**[![clip\_image004][clip_image004]][clip_image004_clip_image004]**

**例如：**

mysql> set names latin1;

mysql> create table temp(name varchar(10)) charset utf8;

mysql> insert into temp values('中国');

mysql> select \* from temp;

\+--------+

| name |

\+--------+

| 中国 |

\+--------+

mysql> set names utf8;

mysql> select \* from temp;

\+---------------+

| name |

\+---------------+

| ä¸å›½ |

\+---------------+

注意：存储字符集编码比插入时字符集大时，如果原封不动返回数据会出现乱码，不过可通过修改查询字符集，避免乱码，即不会丢失数据。

###### 2.2 数据丢失问题 ######

模拟情景1：

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

[![clip\_image006][clip_image006]][clip_image006_clip_image006]

例如：

mysql> set names utf8;

mysql> create table temp(name varchar(10)) charset latin1;

mysql> insert into temp values('中国');

mysql> select \* from temp;

\+------+

| name |

\+------+

| ?? |

\+------+

mysql> set names latin1;

mysql> select \* from temp;

\+------+

| name |

\+------+

| ?? |

\+------+

数据不完整了，且无法恢复。

##### 3、 乱码终极解决方案 #####

1）首先要明确你的客户端时候何种编码格式，这是最重要的（IE6一般用utf8，命令行一般是gbk，一般程序是gb2312)  
2）确保你的数据库使用utf8格式，很简单，所有编码通吃。  
3）一定要保证connection字符集大于等于client字符集，不然就会信息丢失，比如： latin1 < gb2312 < gbk < utf8，若设置set character\_set\_client = gb2312，那么至少connection的字符集要大于等于gb2312，否则就会丢失信息  
4）以上三步做正确的话，那么所有中文都被正确地转换成utf8格式存储进了数据库，为了适应不同的浏览器，不同的客户端，你可以修改character\_set\_results来以不同的编码显示中文字体，由于utf8是大方向，因此web应用是我还是倾向于使用utf8格式显示中文的。

设置数据库字符集

1.alter database test default character set = utf8;

2.create database mydb character set utf8;

修改单个表的字符集

1.alter table test1 default character set = utf8;

2.alter table test2 convert to character set utf8;

使用MySQL字符集的建议：建立数据库/表和进行数据库操作时尽量显式指出使用的字符集，而不是依赖于MySQL的默认设置；

[clip_image002]: https://images2015.cnblogs.com/blog/669905/201705/669905-20170529003505047-1070819958.jpg
[clip_image002_clip_image002]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/71fae4812cf24b079acb63e25a228b4a.png
[clip_image004]: https://images2015.cnblogs.com/blog/669905/201705/669905-20170529003507328-353636243.jpg
[clip_image004_clip_image004]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/f1638fa6b10249408d692c426d6ff9bc.png
[clip_image006]: https://images2015.cnblogs.com/blog/669905/201705/669905-20170529003509407-617465747.jpg
[clip_image006_clip_image006]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/d1425c4f3085414fab9c763fd93a3adf.png