20.MySQL之字符集与校对集

如果想看其他有关于MySQL数据库的文章，请跳转到到MySQL自学目录

本节介绍MySQL处理不同字符集和校对集的相关知识。

1. 字符集

1. 字符集是什么

   • 不同的字符集支持不同地区的字符。例如gbk支持中文简体字符，latin1支持西欧字符、希腊字符等，utf8几乎支持世界上所有国家的字符等。
   • 每种字符集占用不同的存储空间。其中latin1占用一个字节，gbk占用2个字节，utf8占用3个字节。占用的存储空间越多，意味着字符集能表示的字符越多，但也会造成存储空间的浪费。

2. MySQL字符集的转化
   如图所示：

graph LR
    A(客户机 jbk)-->B(字符集转换器 utf8)
    B(字符集转换器 utf8)-->A(客户机 jbk)
    B(字符集转换器 utf8)-->C(服务器 utf8)
    C(服务器 utf8)-->B(字符集转换器 utf8)

原理：当我们客户机设置的字符集为jbk时，客户机会将字符送到字符集转换器，若我们设置字符集转换器为uft8时，这时字符集就会将jbk转成utf8，然后请求服务器的字符集，若服务器的字符集为utf8，就不用转换，直接将已转好的字符送给服务器。这三者的字符集都是可以来设置的。
3. MySQL字符集参数说明

• charater_set_client:MySQL客户机的字符集。
• charater_set_connection:数据通信链路的字符集，也就是上图的字符集转换器。
• charater_set_database:数据库字符集。
• charater_set_filesystem:MySQL服务器系统的字符集。
• charater_set_results:结果集的字符集。
• charater_set_server:MySQL服务实例字符集。
• charater_set_system:元数据（字段名、表名、数据库名等）的字符集。

4.MySQL字符集的设置

• 方法一：修改my.ini配置文件。若将[mysql]选项组中的default_character_set参数设置为gbk，则charater_set_client、charater_set_connection以及charater_set_results参数的默认设置为gbk。若将[mysqld]选项组中的character_set_server参数设置为gbk,则charater_set_database以及charater_set_server参数的默认修改为gbk。保存文件，重启MySQL服务将生效。
• 方法二：MySQL提供下列MySQL命令，可以“临时地”修改MySQL“当前回话的”字符集。
  - set charater_set_client = gbk;
  - set charater_set_connection = gbk
  - …(不一一列举)
• 方法三：MySQL命令：set names gbk; 可以“临时一次性地”设置charater_set_client、charater_set_connection以及charater_set_results的字符集为gbk。
• 方法四：连接MySQL服务器时指定字符集。语法格式为：mysql –default-character-set = 字符集 -h 服务器IP地址 -u 账户名 -p 密码。这种方式等效于连接MySQL服务器后，执行MySQL命令“set names 字符集”。

5.两个问题
- 什么时候会出现乱码？
举个栗子

    mysql> # 数据库中的各种字符集都是默认utf8，从客服端向服务端写数据
    mysql> insert into aaaa values ("世界和平");
    Query OK, 1 row affected (0.49 sec)

    mysql> select * from aaaa;
    +--------------+
    | name         |
    +--------------+
    | 世界和平     |
    +--------------+
    1 row in set (0.14 sec)

    mysql> # 修改 character_set_results的字符集不为utf8
    mysql> set character_set_results = gbk;
    Query OK, 0 rows affected (0.04 sec)

    mysql> # 再次检索就是从服务器端取数据就会发生错误。
    mysql> select * from aaaa;
    +----------+
    | name     |
    +----------+
    | ÊÀ½çºÍƽ        |
    +----------+
    1 row in set (0.11 sec)

    mysql> # 设置回原来的字符集，就不会出错

当我们在客户端向写入服务器的数据的字符集发生改变，再次从服务器中取数据时就会发生错误。

• 什么时候会丢失数据？
  当我们的在写入数据或读取数据时，数据向范围小的字符集转化时，出现了溢出就会丢失数据。例如gbk向latin1转换时就可能出现丢失数据。

2. 校对集

当我们在使用order by 来时就要用到排序，这就需要一个排序规则。校对集就是字符集的排序规则。在MySQL中，一个字符集对应多个校对集。
sql命令：show collation;可以查看MySQL数据库中所有的校对集。

3. 字符集与校对集的使用

1. 字符集的相关操作
   
   • sql命令：show character set（查看所有的字符集）
   • sql命令：show collation（查看所有的校对集）
   • sql命令：show variables like ‘character%’（查看本数据库所用的字符集）
   • sql命令：show variables like ‘collation’（查看本数据库所有的校对集）

2. 字符集和校对集的使用

   1.为了给表指定字符集和校对，可在建表时说明指定的字符集和校对集。

   mysql> create table aa (name char(10))
       -> charset gbk collate gbk_general_ci;

   2.MySQL还允许对每个列设置指定的字符集与校对。

   mysql> create table aaa (
       -> name varchar(10) character set utf8 collate utf8_general_ci,
       -> age int
       -> )character set gbk collate gbk_chinese_ci;
   Query OK, 0 rows affected (0.39 sec)

   3.在order by子句检索出来的数据排序时也可指定特定校对集。

   mysql> select * from aaa order by name collate utf8_bin;
   +------+------+
   | name | age  |
   +------+------+
   | B    |   12 |
   | D    |   18 |
   | a    |   10 |
   | c    |   12 |
   +------+------+
   4 rows in set (0.11 sec)

tips:
1. charset = character set,两者是一个东西
2. charset utf8 collate utf8_bin;更改校对集时，在建表时
声明的校对集必须是合法的校对集（也就是字符集的校对集）
3. MySQL如何确定使用什么样的字符集和校对集？

• 如果指定了character set和collate 两者，则使用指定的值。
• 如果只指定character set，则使用此字符集及其默认的校对集（使用show character set可以查看）
• 如果character set与collate都未指定，则使用数据库默认的值。