MySQL字符集

**计算机只能识别二进制代码，为了让计算机也能处理文字信息，需要对每个文字符号进行编码，以便于计算机识别和处理，这就是字符集编码的由来。

下面介绍字符集发展和在 MySQL 中使用字符集：

字符集概述

简单的说，字符集就是一套文字符号及其编码、比较规则的合集。20世纪60年代 美国 ANSI（标准化组织）发布了ASCII 码，后来进一步变成国际标准 ISO-646。这个字符集采用七位编码，定义了包括了大小写字母、阿拉伯数字和符号、33个控制符号等。后来定制的各种字符集都是基于 ASCII 码定制的。

后来各大公司、各国政府、标准化组织先后发明了几百种字符集，比如 ISO-8859、GB2312、GBK、BIG5 等。这些字符集五花八门，导致各个国家的编码都不通用，后来出现了统一字符编码（Unicode）。

Unicode又经过一系列发展，出现了 UTF-8 的解决方案

汉字常见字符集

在计算机发展的不同阶段，我国参照当时的国际标准和实际需要，定制了一些汉字字符集编码标准：

• GB2312-80： 于 1980 年发布，收录了 6763 个常用汉字和 682 个非汉字图形符号。
• GB13000： 于 1993 年发布，除收录 GB2312-80 外，还收录了第 1、3、5、7 辅助集的全部汉字，共 27484 个，以及一些偏旁部首等。但 GB13000 推出后 并没有遭到业界支持，也就是一个形式上的标准。
• GBK： 于 1995 年发布，在 GB2312 内码体系的基础上进行了扩充，收录了 GB13000.1-1993 的全部 20902 个 CJK 一统汉字，包括 GB2312 全部的 6763 个汉字。此外，还增补编码了 52 个汉字，13 个汉字结构符（表意文字描述符），和一些常用的部首与汉字部件。在 GBK 的内码系统中，并且 GB2312 的汉字所在码位不变，保证了 GBK 对 GB2312 的完全兼容。同时，GBK 内码与 GB13000.1 代码一一对应，为 GBK 兼容 13000.1 提供了解决版本。有趣的是，GBK 只是一个行业指导规范，并没有强制力吗，但是由于得到了 Microsoft Windows 95 的支持而大为流行。
• GB18030： 于 2000 年发布，收录了 ISO/IEC 10646.1:2000 全部 27484 个 CJK 统一汉字，GB18030 是 GBK 的超集，也完全与 GB13000 向上兼容，定制 GB18030 也是为了解决 GBK 强制力不够的问题。

上面介绍集中汉字字符集，下面将常用字符集的特点归纳如表：

选择合适的字符集

对于数据库来说，字符集的选择更加重要。因为数据库中存储的大部分内容都是各种文字，字符集对数据库的存储、处理性能，以及日后系统的移植、推广都会受到影响。

MySQL5.7 目前支持几十种在字符集，包括USC-2，UTF-16，UTF-16LE、UTF-32、UTF-8 等 Unicode 字符集。面对众多字符集，我们该如何选择？

可以根据应用的需求，结合字符集的特点来做权衡。

• 满足应用支持语言的需求，如果应用要处理各种各样的文字，或将发布到使用不同语言的国家或地区，就应该选用 Unicode 字符集。在 MySQL 中，最常用的字符集是UTF-8，其中 utf8mb3 和 utf8mb4 是 UTF-8 规则编码的两种字符集，后者是前者的超集。我们常说的 utf8 其实是 utf8mb3 的别名，3 表示这种字符集由 1 ~ 3 个字节组成。utf8mb4 表示这种字符集由 1~ 4个字节组成，如果需要支持 emoji 表情，通常需要选择 utf8mb4 的字符集来支持。随着互联网的发展，有越来越多的应用需要 utf8mb4 的字符集来支持。在最新的 MySQL8.0 中，默认字符集已经由 latin1 变为了 utf8mb4。
• 如果应用中涉及到已有数据导入，就要充分考虑到数据库字符集对已有数据的兼容性。假如已有数据是 GBK 文字，如果选择 GB2312-80 为数据库字符集，就很有可能出现部分文字无法正常导入的情况。
• 如果数据库只需要支持一般中文，数据量很大，性能要求也很高。那就应该选择双字节定长的中文字符集，比如 GBK。相对于 UTF-8 而言， GBK 的每个汉字只占用两个字节，而 UTF-8 的汉字编码需要三个字节，这样可以减少磁盘 I/O、数据库 cache 以及网络传输的时间，从而提供更好的性能。相反，如果应用主要处理英文字符，只有少部分的中文数据，那么 UTF-8 会更好，因为 GBK、UCS-2、UTF-16 的西文字编码都是两个字节，会造成很多不必要的开销。
• 如果数据库需要做大量的字符运算，例如比较、排序等，那么选择定长字符集更好，因为定长字符集的处理速度要比变长字符集更快。
• 如果所有的客户端都支持相同的字符集，则应该选择该字符集作为数据库字符集。这样可以避免因字符集转换而带来的性能开销和数据损失。

MySQL 支持的字符集简介

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

查看所有可用字符集的命令是 show character set ：

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |

或者查看 information_schema.character_sets ，可以显示所有字符集和该字符集默认排序的规则。

mysql> DESC information_schema.character_sets;
+----------------------+-------------+------+-----+---------+-------+
| Field                | Type        | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| CHARACTER_SET_NAME   | varchar(32) | NO   |     |         |       |
| DEFAULT_COLLATE_NAME | varchar(32) | NO   |     |         |       |
| DESCRIPTION          | varchar(60) | NO   |     |         |       |
| MAXLEN               | bigint(3)   | NO   |     | 0       |       |
+----------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

MySQL 的字符集包括字符集（CHARACTER）和排序规则（COLLATION）两个概念。其中字符集是用来定义 MySQL 存储字符串的方式，排序规则用来定义比较字符串的方式。字符集和排序规则是一对多的关系，MySQL 支持 30 多种字符集和 70 多种排序规则。

每个字符集最少对应一个排序规则。可以使用 SHOW COLLATION LIKE '***' 命令或者通过系统表 information_schema.COLLATIONS 来查看关键字符的排序规则。

mysql> SHOW COLLATION LIKE 'utf8%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation                | Charset | Id  | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8    |  83 |         | Yes      |       1 |
| utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 |
| utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 |
| utf8_latvian_ci          | utf8    | 194 |         | Yes      |       8 |
| utf8_romanian_ci         | utf8    | 195 |         | Yes      |       8 |
| utf8_slovenian_ci        | utf8    | 196 |         | Yes      |       8 |
...

mysql> DESC information_schema.COLLATIONS;
+--------------------+-------------+------+-----+---------+-------+
| Field              | Type        | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| COLLATION_NAME     | varchar(32) | NO   |     |         |       |
| CHARACTER_SET_NAME | varchar(32) | NO   |     |         |       |
| ID                 | bigint(11)  | NO   |     | 0       |       |
| IS_DEFAULT         | varchar(3)  | NO   |     |         |       |
| IS_COMPILED        | varchar(3)  | NO   |     |         |       |
| SORTLEN            | bigint(3)   | NO   |     | 0       |       |
+--------------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

排序规则命名约定：它们以其相关的字符集名开始，通常包括一个语言名，并以 _ci（大小写不敏感）、_cs （大小写敏感）或 _bin （二元，即比较是基于字符编码的值而与 language 无关）结尾。

例如：

• utf8_general_ci 是默认的排序规则，对大小写不敏感。
• utf8_bin 按照编码的值进行比较，对大小写敏感。

在下面的例子中，如果指定 “A” 或 “a”按照 utf8_general_ci 去比较，则认为两个字符是相同的，如果按照 utf8_bin 去比较，则认为两个字符是不同的。我们事先需要确认应用的需求，是按照什么样的排序方式，是否需要区分大小写，以确定排序规则的选择。

mysql> SELECT IF('A' COLLATE utf8_general_ci = 'a' COLLATE utf8_general_ci,1,0) AS 'IF';
+----+
| IF |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> SELECT IF('A' COLLATE utf8_bin = 'a' COLLATE utf8_bin,1,0) AS 'IF';
+----+
| IF |
+----+
|  0 |
+----+
1 row in set (0.00 sec)

MySQL 字符集的设置

MySQL 的字符集和排序规则有 4 个级别的默认设置：服务器级、数据库级、表级和字段级。它们分别在不同的地方设置，作用也不相同。

服务器字符集和排序规则

服务器字符集和排序规则，可以在 MySQL 服务启动的时候确定。

• 在 my.cnf 中设置：

  [mysqld]
  character-set-server=utf8
  

• 在启动选项中指定：

  mysqld --character-set-server=utf8
  

• 或者在编译的时候指定：

  shell> cmake . -DDEFAULT_CHARSET=utf8
  

如果没有使用特定的方法指定服务器字符集，那么 MySQL5.7 中默认使用 latin1 作为服务器字符集。上面三种方法都只是制定了字符集，并没有指定排序规则，这样意味着使用该字符集默认排序规则。如果要使用该字符集非默认的排序规则，则需要在指定字符集的同时指定排序规则。

注意： 在最新的 MySQL8.0 中，默认字符集已变为 utf8mb4

可以使用 show variables like 'character_set_server'; 命令查询当前服务器的字符集和排序规则：

mysql> show variables like 'character_set_server';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+
1 row in set, 1 warning (0.00 sec)

mysql> show variables like 'collation_server';
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_unicode_ci |
+------------------+--------------------+
1 row in set, 1 warning (0.00 sec)

数据库字符集和排序规则

数据库的字符集和排序规则既可以在创建数据库的时候指定，也可以创建之后通过 alter database 命令修改。需要注意的是，如果数据库已经存有数据，因为修改字符集并不能将已有的数据按照新的字符集进行存放，所以不能脱过修改数据库的字符集直接修改数据的内容。

设置数据库字符集的规则如下：

• 如果指定了字符集和排序规则，则使用指定的字符集和排序规则；
• 如果指定了字符集但没有指定排序规则，则使用指定字符集的默认排序规则；
• 如果指定了排序规则但未指定字符集，则字符集是使用与该排序规则关联的字符集；
• 如果没有指定字符集和排序规则，则使用服务器字符集和排序规则作为数据库的字符集和排序规则；

推荐在创建数据库的时候明确指定字符集和排序规则，避免默认值受到影响。要显示当前数据库的字符集和排序规则。要显示当前数据库的字符集和排序规则，可以使用 show variables like 'character_set_database';  和 show variables like 'collation_database';命令查看：

mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> show variables like 'collation_database';
+--------------------+-----------------+
| Variable_name      | Value           |
+--------------------+-----------------+
| collation_database | utf8_general_ci |
+--------------------+-----------------+
1 row in set, 1 warning (0.00 sec)

表字符集和排序规则

表的字符集和排序规则可以在创建表的时候指定，可以通过 alter table 命令进行修改，同样，如果表中已有记录，修改字符集对原有的记录并没有影响，不会按照新的字符集存放。表的字段仍然使用原来的字符集。

设置表的字符集和排序规则与数据库基本类似:

• 如果指定了字符集和排序规则，则使用指定的字符集和排序规则；
• 如果指定了字符集但没有指定排序规则，则使用指定字符集的默认排序规则；
• 如果指定了排序规则但未指定字符集，则字符集是使用与该排序规则关联的字符集；
• 如果没有指定字符集和排序规则，则使用数据库字符集和排序规则作为表的字符集和排序规则；

推荐在创建表的时候指定字符集和排序规则，以免受到默认值影响。要显示表的默认字符集和排序规则，可以使用 show create table 命令查看：

mysql> show create table salary G;
*************************** 1. row ***************************
       Table: salary
Create Table: CREATE TABLE `salary` (
  `userid` int(11) DEFAULT NULL,
  `salary` decimal(9,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

列字符集排序规则

MySQL 可以定义列级别的字符集和排序规则，主要针对相同表的不同字段需要使用不同字符集的情况，应用一般不会碰到这种情况，只是 MySQL 提供给我们的一种灵活设置的手段。

列字符集和排序规则的定义可以在创建表的时候定义，或者在修改表的时候调整。如果在创建表的时候没有特别指定字符集和排序规则，则默认使用表的字符集和排序规则。

连接字符集和排序规则

上面的四种设置方式，确定的是数据保存的字符集和排序规则，对于实际的应用来说，还存在客户端和服务器之间交互的字符集和排序规则。

对于客户端和服务器的交互操作，MySQL 提供了 3 个不同的参数：character_set_client、character_set_connection、character_set_results 分别代表 客户端、连接、返回结果的字符集。通常这三个字符集是相同的，才可以确保正常的写入数据和读出，特别是对于中文字符，不同的写入字符集和返回字符集会导致写入的记录不能正常读出。

通常部分分别设置这三个参数，可以通过以下命令：

SET NAMES ***;

来设置连接的字符集和排序规则，这个命令可以同时修改这三个参数的值。使用这个方法修改字符集和排序规则，仅当前连接有效，下次连接时依然需要执行这个命令。

还可以直接从 my.cnf 中设置：

[mysql]
default-character-set=utf8

设置后，所有连接默认使用utf8，另外字符串常量的字符集也是由 character_set_connection 参数来指定的。

可以通过 [charset_name]'string'[COLLATE collation_name] 强制设置字符串的字符集和排序规则：

select _utf8 '字符集';
select _latin1 '字符集';

通常情况下不需要用户强制指定字符串的字符集。

字符集修改步骤

如果在应用开始阶段没有正确的设置字符集，在运行一段时间之后才发现存在不能满足需求需要调整，又不想丢弃这段时间的数据，那就需要进行对字符集的修改。字符集的修改不能通过 alter database character set *** 或 alter table tablename character set *** 两个命令进行修改，这两个命令没有更新已有记录的字符集，而只是对新创建的表或记录生效。已有记录的字符集调整，需要先将数据导出，经过适当调整后重新导入才可完成。

注意： 选择目标字符集的时候，要注意最好选择的是原字符集的超集，或者确定比原字符集的字库更大，否则如果目标字符集的字库更小，那么目标字符集中不支持的字符导入后会变成乱码，丢失一部分数据。

例如：GBK 的字符集字库就大于 GB2312 字符集，那么 GBK 字符集的数据如果导入 GB2321 数据库中，就会丢失 GB2312 中不支持的那部分汉字的数据。