MySQL中的字符集与字符序

原创

valineliu

发布于 2022-08-27 18:30:02

2.8K1

发布于 2022-08-27 18:30:02

文章被收录于专栏：OnMyWay

这篇文章详细介绍一下MySQL中的字符集和字符序相关的问题，里里外外地了解一下字符集和字符序的方方面面，同时重点说明一下开发中需要注意的问题。

文章基于MySQL 8.0，也会涉及到5.7版本。主要参考MySQL手册：https://dev.mysql.com/doc/refman/8.0/en/

1. 太长不爱看版

字符集(Character Set)是字符的编码规则，字符序(Collation)是字符的排序规则；
每一个字符集都包含一定范围的字符；
每一个字符集都有一个或多个字符序，其中一个字符序为默认字符序；
每一个字符序都有一个相关联的字符集；
两个不同的字符集没有相同的字符序；
相同数据使用不同的字符序排序结果可能会不同；
MySQL支持服务(server)、数据库(database)、表(table)、字段(column)以及字符串字面量(string literal)等多个级别的字符集与字符序的设置；
库表创建以及程序中尽可能使用utf8mb4字符集（可支持emoji）；
MySQL中的utf8字符集是utf8mb3字符集的别名，避免使用；
MySQL 5.7的服务默认字符集是latin1，而8.0中是utf8mb4；
通过SHOW CHARACTER SET查看当前服务器所支持的字符集；
通过SHOW COLLATION查看所有的字符序；
MySQL中有多个关于字符集和字符序的系统变量，可以针对全局以及当前session进行设置；
通过SHOW VARIABLES LIKE "character_set%"查看当前服务以及数据库的字符集设置；
使用client客户端连接数据库时，如果编码有问题，可以通过SET NAMES charset_name来设置和数据库一致的字符集；
库表内容在不同字符集之前转换可能会发生数据丢失问题。

2. 什么是字符集与字符序

在大多数情况下，我们并不需要了解字符集与字符序，但是在涉及到不同字符集的转换时可能会出现问题，这时了解一下相关的知识还是有帮助的。

我们首先了解一下字符集的原理。

2.1 字符与字符集

字符是我们经常接触到的东西，比如a、中、ß，以及😀等都是字符。

我们知道计算机是通过bit来存储数据的，将人类可识别的字符转换成计算机可存储的形式，这个过程就是编码；字符编码的结果，就是内存编码。

一个字符需要用多少个bit来存储，那就需要知道一共有多少个字符。

所有的字符放在一起就是字符集。

显然，由于使用范围不同，就出现了不同的字符集。比如：

汉语中的所有字符构成一个字符集（也包括不是汉字的字符，比如标点符号等）；
英语中的所有字符构成一个字符集；
等等

对于字符集中的每个字符来说，都有两个属性：

一个是这个字符在所属字符集中的位置，可以叫做字符序号，或码点（code point）；
第二个就是这个字符在计算机中的数据表示，即内存编码。

比如ASCII码，只需要8个bit就可以存储所有需要使用的字符了。

但对于汉语来说显然是不够的，因此汉语字符需要更多的bit，来将每个字符进行编码。

这样对于每个国家来说，都可能需要一个将自己使用的字符集编码成计算机内存编码的规则。

那么同一个内存编码，对于不同的字符集来说就可能代表不同的字符：

比如GB18030字符集中的“地球”两个字符的内存编码分别是0xB5D8和0xC7F2，但这两个内存编码在字符集BIG5中代表的字符却是“華⑩”。

这将对我们的程序有很大的影响。

2.2 Unicode字符集与UTF-8

为了解决不同语言编码之间不兼容的问题，Unicode出现了。

Unicode字符集致力于为全世界每一个语言的每一个字符都有统一且唯一的编码：

All languages, all characters in Unicode

Unicode字符序号的范围是0x000000到0x10FFFF，可以容纳110多万个字符。

那么如何将Unicode中的字符映射到内存编码呢？主要有UTF-8、UTF-16和UTF-32等，其中最常用的就是UTF-8。

UTF-8使用1到4个不等的字节来表示所有的字符，其中前128个字符与ASCII一致。

关于Unicdoe的详细信息，可以参考https://home.unicode.org/

2.3 字符序

一个字符集中有多个字符，那么如何对其中的字符进行排序呢？这就是字符序。

简单来说，字符序就是字符排序的规则集合。比如一个字符集有下面几个字符（以及内存编码）：

字符	内存编码
A	00
B	01
a	10
b	11

当然我们可以直接按照A>B>a>b的规则来进行排序，这就是这个简单字符集的一个字符序。

如果想让小写字母放在前面，比如a>b>A>B，这又是一种字符序。

如果还想加上大小写无关或大小写相关，那么排序的规则集就会有相应的编码，这就产生了不同的字符序。

字符序主要对字符的排序有影响。

3. MySQL中的字符集与字符序

了解了字符集和字符序之后，来看看MySQL中的字符集与字符序。

3.1 MySQL中的字符集

通过下面的语句来查看MySQL中支持的字符集：

SHOW CHARACTER SET;

结果：

+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+

字段含义：

Charset: 字符集的名称；
Description：字符集的简单描述；
Default collation：该字符集的默认字符序；
Maxlen：该字符集中字符最大存储长度。

通过加入条件查询一部分字符集：

SHOW CHARATER SET LIKE 'utf%';

结果展示所有Unicode字符集：

+---------+------------------+--------------------+--------+
| Charset | Description      | Default collation  | Maxlen |
+---------+------------------+--------------------+--------+
| utf16   | UTF-16 Unicode   | utf16_general_ci   |      4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci |      4 |
| utf32   | UTF-32 Unicode   | utf32_general_ci   |      4 |
| utf8    | UTF-8 Unicode    | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode    | utf8mb4_0900_ai_ci |      4 |
+---------+------------------+--------------------+--------+

后面会有关于Unicode的每种字符集的详细信息。

3.2 MySQL中的字符序

每个字符集都有一个或多个字符序，可以通过下面的语句查看所有的字符序：

SHOW COLLATION;

结果（只展示一部分）：

+---------------------+----------+-----+---------+----------+---------+---------------+
| Collation           | Charset  | Id  | Default | Compiled | Sortlen | Pad_attribute |
+---------------------+----------+-----+---------+----------+---------+---------------+
| armscii8_bin        | armscii8 |  64 |         | Yes      |       1 | PAD SPACE     |
| armscii8_general_ci | armscii8 |  32 | Yes     | Yes      |       1 | PAD SPACE     |
| ascii_bin           | ascii    |  65 |         | Yes      |       1 | PAD SPACE     |
| ascii_general_ci    | ascii    |  11 | Yes     | Yes      |       1 | PAD SPACE     |
| big5_bin            | big5     |  84 |         | Yes      |       1 | PAD SPACE     |
| big5_chinese_ci     | big5     |   1 | Yes     | Yes      |       1 | PAD SPACE     |
| binary              | binary   |  63 | Yes     | Yes      |       1 | NO PAD        |
| cp1250_bin          | cp1250   |  66 |         | Yes      |       1 | PAD SPACE     |
| cp1250_croatian_ci  | cp1250   |  44 |         | Yes      |       1 | PAD SPACE     |
| cp1250_czech_cs     | cp1250   |  34 |         | Yes      |       2 | PAD SPACE     |
| cp1250_general_ci   | cp1250   |  26 | Yes     | Yes      |       1 | PAD SPACE     |
| cp1250_polish_ci    | cp1250   |  99 |         | Yes      |       1 | PAD SPACE     |
|      ......         | ......   |  ...|  ...    | ...      |   ...   |  ......       |
+---------------------+----------+-----+---------+----------+---------+---------------+

字段含义如下：

Collation：字符序名称；
Charset：该字符序关联的字符集；
Id：字符序ID；
Default：该字符序是否是所关联的字符集的默认字符序。比armscii8_general_ci就是armscii8的默认字符序，而armscii8_bin就不是；
Compiled：字符集是否已编译到服务器中；
Sortlen：这与对以字符集表示的字符串进行排序所需的内存量有关；
Pad_attribute：这表明了字符序在比较字符串时对末尾padding的处理。NO PAD表明在比较字符串时，末尾的padding也会考虑进去，否则不考虑。

也可以指定条件查询：

SHOW COLLATION WHERE Charset = 'utf8mb4';

这里查询的就是utf8mb4字符集的所有字符序。

每个字符序都是以该字符序所关联的字符集为前缀的，同时还有一些有规律的后缀。

这些后缀有：

bin：二进制；
ci：大小写不敏感；
cs：大小写敏感；
ai：口音（Accent）不敏感；
as：口音敏感；
ks：假名（Kanatype）敏感。

同时有的字符序是面向某种语言的，也会在字符序名字中有所体现，比如big5_chinese_ci。

3.3 字符集与字符序的关系

字符集与字符序的关系可以用下面的图来表示：

即：

每个字符集都有一个或多个字符序；
每个字符集都有一个默认的字符序；
每个字符序都关联一个且只有一个字符集；
两个不同的字符集没有相同的字符序。

3.4 MySQL中的相关变量

MySQL中有一些变量用于字符集与字符序的设置。

3.4.1 字符集变量

通过下面的语句来查看与字符集相关的变量：

SHOW VARIABLES LIKE 'character_set\_%'; -- 当前会话
SHOW GLOBAL VARIABLES LIKE 'character_set\_%'; -- 全局

结果：

+--------------------------------+---------+
| Variable_name                  | Value   |
+--------------------------------+---------+
| character_set_client           | utf8mb4 |
| character_set_client_handshake | ON      |
| character_set_connection       | utf8mb4 |
| character_set_database         | utf8mb4 |
| character_set_filesystem       | binary  |
| character_set_results          | utf8mb4 |
| character_set_server           | utf8mb4 |
| character_set_system           | utf8    |
+--------------------------------+---------+

变量含义：

变量	含义	作用域	默认值
`character_set_client`	客户端发出SQL语句的字符集	全局，会话	`utf8mb4`
`character_set_client_handshake`	不忽略客户端发出的字符集信息	全局，会话	ON（不忽略）
`character_set_connection`	没有指定字符集的字符串字面值所使用的字符集	全局，会话	`utf8mb4`
`character_set_database`	数据库默认使用的字符集	全局，会话	`utf8mb4`
`character_set_filesystem`	文件系统默认的字符集	全局，会话	`binary`
`character_set_results`	服务器返回给客户端的结果使用的字符集	全局，会话	`utf8mb4`
`character_set_server`	服务器默认的字符集	全局，会话	`utf8mb4`
`character_set_system`	服务器存储元数据使用的字符集	全局	`utf8bm3`

3.4.2 character_set_system

这个变量是MySQL数据库元数据使用的字符集。

所有描述数据库的数据都是元数据，比如表名、列名等等。

对元数据的存储有如下几个要求：

所有的元数据必须使用相同的字符集；

这个字符集必须包含所有语言的字符。

MySQL使用UTF-8，具体的就是utf8mb3字符集，在MySQL中utf8就是utf8mb3，不过后续的版本可能会有所改变。

3.4.3 字符序变量

通过下面的语句来查看与字符序相关的变量：

SHOW VARIABLES LIKE '%collation%'; -- 当前会话
SHOW GLOBAL VARIABLES LIKE '%collation%'; -- 全局

结果：

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

变量含义：

变量	含义	作用域
`collation_connection`	没有指定字符集的字符串字面值所使用的字符序	全局，会话
`collation_database`	数据库默认使用的字符序	全局，会话
`collation_server`	服务器默认使用的字符序	全局，会话

3.4.3 重要变量

本文主要关注于下面几个变量：

character_set_server, collation_server
character_set_database, collation_database
character_set_client
character_set_connection, collation_connection
character_set_results

其中前前两组涉及库表设计时字符集与字符序的配置，后三组涉及到客户端与服务器连接时的字符集与字符序的配置。

4. 设置字符集与字符序

MySQL中支持多种字符集与字符序，对此，MySQL能够为我们做到：

使用不同字符集存储字符串；
使用不同的字符序对字符串进行排序；
在同一个服务器中，或同一个数据库中，甚至同一张表中使用不同的字符集或字符序；
对于多个级别的字符集与字符序进行设置。

4.1 服务器的设置

MySQL中，服务器有一个默认的字符集与字符序，其中：

5.7以后版本默认字符集是utf8mb4；
5.7的默认字符序是utf8mb4_general_ci；
8.0的默认字符序是utf8mb4_0900_ai_ci。

服务器的字符集与字符序可以通过多种方式设置：

服务器启动时：

mysqld
mysqld --character-set-server=utf8mb4
mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_0900_ai_ci

这三种方式效果一样。

服务运行时：设置character_set_server和collation_server变量；
还可以编译MySQL服务时进行设置。

服务器字符集与字符序的影响：当创建数据库时没有指定字符集与字符序，就是用服务器的字符集与字符序。

除此之外没有别的影响。

4.2 库表设计中的设置

在创建库表时，需要指定数据库、表以及字段所使用的字符集与字符序。

如果没有指定，MySQL有一系列规则来使用字符集与字符序的默认值。

4.2.1 数据库的设置

在创建数据库的时候可以指定该数据库所使用的字符集与字符序：

CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

MySQL使用下面的规则来设置数据库的字符集与字符序：

如果创建数据库的时候指定了字符集与字符序，就是用这个字符集与字符序：CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;这里数据库的字符集就是latin1，字符序就是latin1_swedish_ci。
如果创建数据库时指定了字符集而没有指定字符序，那字符集就是这个值，而字符序就是字符集的默认字符序：

CREATE DATABASE db_name CHARACTER SET latin1;

字符集就是latin1，字符序就是latin1的默认字符序latin1_swedish_ci。

如果创建数据库时指定了字符序而没有指定字符集，那么字符集就是该字符序所对应的字符集：

CREATE DATABASE db_name CHARACTER COLLATE latin1_swedish_ci;

字符序就是latin1_swedish_ci，字符集就是这个字符序关联的字符集latin1。

如果字符集与字符序都没有指定，那么就是用服务器默认的字符集(character_set_server)与字符序(collation_server)。

对于当前数据库所使用的字符集与字符序，可以通过查看下面两个变量的值：

USE db_name;
SELECT @@character_set_database, @@collation_database;

这两个变量的值有如下的影响：

如果创建表时没有指定该表使用的字符集与字符序，就是用这两个变量所对应的字符集与字符序；
LOAT DATA语句没有指定字符集时，服务器使用character_set_database来解析文件中的信息。

4.2.2 表的设置

创建表时也可以制定该表所使用的字符集与字符序：

CREATE TABLE t1 ( ... )
CHARACTER SET latin1 COLLATE latin1_danish_ci;

MySQL选择表的字符集与字符序的规则和数据库类似，不同在于如果创建表时没有指定字符集与字符序，就会使用变量character_set_database和 collation_database所指定的字符集与字符序。

4.2.3 字段的设置

表里的每个字段也可以拥有自己的字符集与字符序：

CREATE TABLE t1
(
    col1 VARCHAR(5)
      CHARACTER SET latin1
      COLLATE latin1_german1_ci
);

ALTER TABLE t1 MODIFY
    col1 VARCHAR(5)
      CHARACTER SET latin1
      COLLATE latin1_swedish_ci;

与数据库和表类似，MySQL也是按照层级来制定字符集与字符序的。

如果字段没有指定，那么就是用表所使用的字符集与字符序。

4.2.4 小结

上面的几个小节中关于库表设计的字符集与字符序设置，可以用下图来表示：

上一层级如果没有指定字符集与字符序，就是用下一层级的字符集与字符序。

4.3 客户端连接中的设置

当我们使用mysql这个客户端与MySQL服务器连接的时候，也会涉及到字符集与字符序的设置。

4.3.1 与连接有关的变量

每一个连接到服务器的客户端都有一个对应的字符集与字符序。

涉及到的变量有：

character_set_client
character_set_connection, collation_connection
character_set_results

这三个关于字符集的变量是这样使用的：

即：

客户端的语句从客户端出发时，使用的字符集是character_set_client；
语句到达服务器时，服务器将语句转换成character_set_connection字符集；
服务器执行完，将结果返回给客户端时，使用的是character_set_results字符集。

4.3.2 客户端连接字符集设置

当服务器使用的字符集与客户端连接使用的字符集不同时，可能会有问题：

MySQL [test]> select title from article where id = 2;
+-------+
| title |
+-------+
| ????? |
+-------+

这时需要将客户端连接的字符集设置成与服务器保持一致。

SET NAMES utf8mb4;

这样就可以了：

MySQL [test]> select title from article where id = 2;
+-----------------+
| title           |
+-----------------+
| 未命名项目       |
+-----------------+

上面的语句等同于：

SET character_set_client = utf8mb4;
SET character_set_results = utf8mb4;
SET character_set_connection = utf8mb4;

4.3.3 一些限制

在设置character_set_client变量时，下面的字符集不可用：

ucs2
utf16
utf16le
utf32

否则就会报错：

MySQL [test]> set names utf16;
ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of 'utf16'

4.3.4 程序中连接的设置

使用数据库的程序也是一个客户端，在连接数据库的时候也需要指定字符集。

Python：

conn = mysql.connect(host='127.0.0.1',
                     user='user',
                     passwd='passwd',
                     db='db',
                     charset='utf8')

Golang:

dsn := `root:root@tcp(127.0.0.1 :3306)/DB_NAME?charset=utf8mb4`
dbConn, _:= sql.Open(`mysql`, dsn)

5. MySQL对Unicode的支持

前面提到过一种包含所有语言所有字符的字符集Unicode，它的码点分为两个部分：

BMP(Basic Multilingual Plane): 基本多文种平面，范围是0x0000到0xFFFF；

补充平面（Supplementary）: 补充平面，范围是0x10000到0x10FFFF。

5.1 BMP

BMP有如下的特点：

范围是0x0000到0xFFFF，一共65535个字符；

可以编码成变长编码，使用1到3个字节；

也可以编码成定长编码，使用2个字节；

对于主要语言的大多数字符来说是足够了。

5.2 Supplementary

补充平面有如下的特点：

范围是0x10000到0x10FFFF；

编码所需的字节大于BMP，需要4个字节。

5.3 MySQL的支持

MySQL中有几个字符集支持了Unicode：

字符集	支持的字符	每个字符所需的存储大小	备注
`utf8mb3`, `utf8`	BMP	1、2或3个字节	MySQL 8.0中已弃用
`ucs2`	BMP	2个字节	MySQL 8.0中已弃用
`utf8mb4`	BMP和Supplementary	1、2、3或3个字节	推荐使用
`utf16`	BMP和Supplementary	2或4个字节
`utf16le`	BMP和Supplementary	2或4个字节	little-endian，和`utf16`一样
`utf32`	BMP和Supplementary	4个字节

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

#mysql

#character set