MySQL原理 - 字符集与排序规则

Love The Way You Lie 2023-02-17 03:14 53阅读 0赞

任何计算机存储数据，都需要**字符集**，因为计算机存储的数据其实都是二进制编码，将一个个字符，映射到对应的二进制编码的这个映射就是字符编码（字符集）。这些字符如何排序呢？决定字符排序的规则就是**排序规则**。

# 查看内置字符集与比较规则 #

通过`show charset;`命令，可以查看所有的字符集。  
以下仅展示了我们常用的字符集：

+----------+---------------------------------+---------------------+--------+
    | Charset  | Description                     | Default collation   | Maxlen |
    +----------+---------------------------------+---------------------+--------+
    | latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
    | ascii    | US ASCII                        | ascii_general_ci    |      1 |
    | gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
    | cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
    | gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
    | utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
    | utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
    | utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
    | utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
    +----------+---------------------------------+---------------------+--------+

*  `ascii`：共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符，所以可以使用1个字节来进行编码
 *  `latin1`：共收录256个字符，是在ASCII字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。
 *  `gb2312`: 收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其他文字符号682个，**兼容ASCII字符集**。这是一个变长字符集，如果该字符在`ascii`字符集中，则采用1字节编码，否则采用两字节。
 *  `gbk`: GBK是在`gb2312`基础上扩容后的标准。收录了所有的中文字符。同样的，这是一个变长字符集，如果该字符在`ascii`字符集中，则采用1字节编码，否则采用两字节。
 *  `utf8`和`utf8mb4`: 收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容ASCII字符集，采用变长编码方式，编码一个字符需要使用1～4个字节。`MySQL`为了节省空间，其中的`utf8`是标准 UTF8 阉割后的，只有1~3字节编码的字符集，基本包含了所有常用的字符。如果还要使用 enoji 表情，那么需要使用`utf8mb4`，这个是完整的 UTF8 字符集。
 *  `utf16`: 不同于`utf8`，`utf16`用两个字节或者四个字节编码字符，可以理解为`utf8`的不节省空间的一种形式
 *  `utf32`: 固定用四个字节编码字符，可以理解为`utf8`的不节省空间的一种形式

通过查看`information_schema.character_sets`表，也可以看到所有的字符集：

通过`show collation;`命令，可以查看所有的字符集，我们这里来查看`utf8mb4`的排序规则：

mysql> show collation like 'utf8mb4%';
    +------------------------+---------+-----+---------+----------+---------+
    | Collation              | Charset | Id  | Default | Compiled | Sortlen |
    +------------------------+---------+-----+---------+----------+---------+
    | utf8mb4_general_ci     | utf8mb4 |  45 | Yes     | Yes      |       1 |
    | utf8mb4_bin            | utf8mb4 |  46 |         | Yes      |       1 |
    | utf8mb4_unicode_ci     | utf8mb4 | 224 |         | Yes      |       8 |
    | utf8mb4_icelandic_ci   | utf8mb4 | 225 |         | Yes      |       8 |
    | utf8mb4_latvian_ci     | utf8mb4 | 226 |         | Yes      |       8 |
    | utf8mb4_romanian_ci    | utf8mb4 | 227 |         | Yes      |       8 |
    | utf8mb4_slovenian_ci   | utf8mb4 | 228 |         | Yes      |       8 |
    | utf8mb4_polish_ci      | utf8mb4 | 229 |         | Yes      |       8 |
    | utf8mb4_estonian_ci    | utf8mb4 | 230 |         | Yes      |       8 |
    | utf8mb4_spanish_ci     | utf8mb4 | 231 |         | Yes      |       8 |
    | utf8mb4_swedish_ci     | utf8mb4 | 232 |         | Yes      |       8 |
    | utf8mb4_turkish_ci     | utf8mb4 | 233 |         | Yes      |       8 |
    | utf8mb4_czech_ci       | utf8mb4 | 234 |         | Yes      |       8 |
    | utf8mb4_danish_ci      | utf8mb4 | 235 |         | Yes      |       8 |
    | utf8mb4_lithuanian_ci  | utf8mb4 | 236 |         | Yes      |       8 |
    | utf8mb4_slovak_ci      | utf8mb4 | 237 |         | Yes      |       8 |
    | utf8mb4_spanish2_ci    | utf8mb4 | 238 |         | Yes      |       8 |
    | utf8mb4_roman_ci       | utf8mb4 | 239 |         | Yes      |       8 |
    | utf8mb4_persian_ci     | utf8mb4 | 240 |         | Yes      |       8 |
    | utf8mb4_esperanto_ci   | utf8mb4 | 241 |         | Yes      |       8 |
    | utf8mb4_hungarian_ci   | utf8mb4 | 242 |         | Yes      |       8 |
    | utf8mb4_sinhala_ci     | utf8mb4 | 243 |         | Yes      |       8 |
    | utf8mb4_german2_ci     | utf8mb4 | 244 |         | Yes      |       8 |
    | utf8mb4_croatian_ci    | utf8mb4 | 245 |         | Yes      |       8 |
    | utf8mb4_unicode_520_ci | utf8mb4 | 246 |         | Yes      |       8 |
    | utf8mb4_vietnamese_ci  | utf8mb4 | 247 |         | Yes      |       8 |
    +------------------------+---------+-----+---------+----------+---------+
    26 rows in set (0.13 sec)

同样的，通过查询`information_schema.collations`也可以：

mysql> select * from information_schema.collations where character_set_name = "utf8mb4";
    +------------------------+--------------------+-----+------------+-------------+---------+
    | COLLATION_NAME         | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
    +------------------------+--------------------+-----+------------+-------------+---------+
    | utf8mb4_general_ci     | utf8mb4            |  45 | Yes        | Yes         |       1 |
    | utf8mb4_bin            | utf8mb4            |  46 |            | Yes         |       1 |
    | utf8mb4_unicode_ci     | utf8mb4            | 224 |            | Yes         |       8 |
    | utf8mb4_icelandic_ci   | utf8mb4            | 225 |            | Yes         |       8 |
    | utf8mb4_latvian_ci     | utf8mb4            | 226 |            | Yes         |       8 |
    | utf8mb4_romanian_ci    | utf8mb4            | 227 |            | Yes         |       8 |
    | utf8mb4_slovenian_ci   | utf8mb4            | 228 |            | Yes         |       8 |
    | utf8mb4_polish_ci      | utf8mb4            | 229 |            | Yes         |       8 |
    | utf8mb4_estonian_ci    | utf8mb4            | 230 |            | Yes         |       8 |
    | utf8mb4_spanish_ci     | utf8mb4            | 231 |            | Yes         |       8 |
    | utf8mb4_swedish_ci     | utf8mb4            | 232 |            | Yes         |       8 |
    | utf8mb4_turkish_ci     | utf8mb4            | 233 |            | Yes         |       8 |
    | utf8mb4_czech_ci       | utf8mb4            | 234 |            | Yes         |       8 |
    | utf8mb4_danish_ci      | utf8mb4            | 235 |            | Yes         |       8 |
    | utf8mb4_lithuanian_ci  | utf8mb4            | 236 |            | Yes         |       8 |
    | utf8mb4_slovak_ci      | utf8mb4            | 237 |            | Yes         |       8 |
    | utf8mb4_spanish2_ci    | utf8mb4            | 238 |            | Yes         |       8 |
    | utf8mb4_roman_ci       | utf8mb4            | 239 |            | Yes         |       8 |
    | utf8mb4_persian_ci     | utf8mb4            | 240 |            | Yes         |       8 |
    | utf8mb4_esperanto_ci   | utf8mb4            | 241 |            | Yes         |       8 |
    | utf8mb4_hungarian_ci   | utf8mb4            | 242 |            | Yes         |       8 |
    | utf8mb4_sinhala_ci     | utf8mb4            | 243 |            | Yes         |       8 |
    | utf8mb4_german2_ci     | utf8mb4            | 244 |            | Yes         |       8 |
    | utf8mb4_croatian_ci    | utf8mb4            | 245 |            | Yes         |       8 |
    | utf8mb4_unicode_520_ci | utf8mb4            | 246 |            | Yes         |       8 |
    | utf8mb4_vietnamese_ci  | utf8mb4            | 247 |            | Yes         |       8 |
    +------------------------+--------------------+-----+------------+-------------+---------+
    26 rows in set (0.11 sec)

*  每个字符集都有一个默认的排序规则：IS\_DEFAULT 为 Yes。
 *  比较规则名称以与其关联的字符集的名称开头，可以用通过这个开头查询所有的字符集，也可以查询`information_schema.collations`精确指定字符集
 *  字符集后面跟着的是语言编码，因为`utf8mb4`包含了所有字符，不同国家的文字语言排序肯定不一样。
 *  最后末尾的`ci`代表`case insensitive`，大小写不敏感，所有可能的后缀如下所示：
    
     *  ai: accent insensitive 不区分重音
     *  as: accent sensitive 区分重音
     *  ci: case insensitive 不区分大小写
     *  cs: case sensitive 区分大小写
     *  bin: binary 以二进制方式比较

# 应用字符集与比较规则 #

字符集与比较规则配置有四个级别：

*  MySQL实例级别
 *  库级别
 *  表级别
 *  字段级别  
    **指定的级别粒度越小，则以粒度越小的字符集还有比较规则优先**。例如指定MySQL实例级别字符集是`utf8mb4`，指定某个表字符集是`latin1`，那么这个表的所有字段如果不指定的话，编码就是`latin1`

由于字符集和比较规则是互相有联系的，如果我们只修改了字符集，比较规则也会跟着变化，如果只修改了比较规则，字符集也会跟着变化，具体规则如下：

*  只修改字符集，则比较规则将变为修改后的字符集默认的比较规则。
 *  只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。

## 实例级别 ##

通过两个系统变量来指定实例级别的字符集与排序规则。

配置文件：

[server]
    character_set_server=utf8mb4
    collation_server=utf8mb4_general_ci

启动之后，可以查看并修改这两个变量。

mysql>  show variables like 'character_set_server';
    +----------------------+---------+
    | Variable_name        | Value   |
    +----------------------+---------+
    | character_set_server | utf8mb4 |
    +----------------------+---------+
    1 row in set (0.06 sec)
    
    mysql> show variables like 'collation_server';
    +------------------+--------------------+
    | Variable_name    | Value              |
    +------------------+--------------------+
    | collation_server | utf8mb4_general_ci |
    +------------------+--------------------+
    1 row in set (0.05 sec)
    
    mysql> set character_set_server = 'utf8mb4';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set collation_server = 'utf8mb4_general_ci';
    Query OK, 0 rows affected (0.00 sec)

## 库级别 ##

创建数据库的时候，可以指定字符集还有排序规则。

mysql> create database test_db character set utf8mb4 collate utf8mb4_general_ci;
    Query OK, 1 row affected (0.01 sec)

不指定的话，就用实例级别的字符集还有排序规则。

查看当前数据库的字符集还有排序规则则是通过`use`命令指定数据库之后，查看`character_set_database`变量以及`collation_database`来实现：

就算设置这两个变量，也是无效的：

mysql> set character_set_database = 'utf8';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show variables like 'character_set_database';
    +------------------------+---------+
    | Variable_name          | Value   |
    +------------------------+---------+
    | character_set_database | utf8mb4 |
    +------------------------+---------+
    1 row in set (0.09 sec)

修改数据库的字符集还有排序规则的方式：

mysql> alter database test_db character set = 'utf8';
    Query OK, 1 row affected (0.01 sec)
    
    mysql> show variables like 'character_set_database';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | character_set_database | utf8  |
    +------------------------+-------+
    1 row in set (0.08 sec)

这个更新只会对新建的表如果没指定字符集和排序规则的生效，并不会更新老表的字符集还有排序规则。

## 表级别 ##

可以在创建时指定字符集合排序规则，不指定的话，用数据库的字符集还有排序规则，也可以修改字符集和排序规则。

mysql> create table test (name varchar(32)) character set utf8mb4 collate utf8mb4_bin; 
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> show create table test;
    +-------+---------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                          |
    +-------+---------------------------------------------------------------------------------------------------------------------------------------+
    | test  | CREATE TABLE `test` (
      `name` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
    +-------+---------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.09 sec)
    
    mysql> alter table test character set = 'utf8';
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> show create table test;
    +-------+--------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                         |
    +-------+--------------------------------------------------------------------------------------------------------------------------------------+
    | test  | CREATE TABLE `test` (
      `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +-------+--------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.06 sec)

可以看出，仅仅是表的字符集还有排序规则变了，对于已有字段，并没有改变编码和排序规则。

## 列级别 ##

可以在创建表的时候，指定不同的列有不同的字符集和排序规则，也可以修改列的字符集和排序规则：

mysql> create table test (name varchar(32) character set utf8 collate utf8_bin) character set utf8mb4 collate utf8mb4_bin; 
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> show create table test;
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                          |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
    | test  | CREATE TABLE `test` (
      `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.09 sec)
    
    mysql> alter table test modify column name  varchar(32) COLLATE latin1_bin;
    Query OK, 0 rows affected (0.09 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> show create table test;
    +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                              |
    +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
    | test  | CREATE TABLE `test` (
      `name` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
    +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.09 sec)

# MySQL客户端字符编码问题 #

有时候，我们会遇到字符编码不一致导致的程序问题。例如我们的 Java 程序，使用 jdbc 链接。读取的数据，打印出来是乱码。或者是，MySQL 无法识别我们客户端发来的命令。这涉及到字符编码问题。**我们需要保持 Java 程序的字符编码与 JDBC 链接指定的字符编码一致，这样才不会有乱码的问题。**

\*\*指定 Java 程序编码：\*\*通过启动参数：`-Dfile.encoding=UTF-8` 设置默认的字符编码（`java.nio.charset.Charset.defaultCharset();`）是`utf-8`（对应 MySQL 的`utf8`还有`utf8mb4`）。

**指定 JDBC 链接编码：**

jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8

mysql客户端命令行指定字符集

mysql -h 127.0.0.1 -P 3306 -u root  --default-character-set=utf8mb4  -p

之后查看有关编码的环境变量，都是和设置的这个字符集一样。

其中：

*  `character_set_client`: 服务器解码请求时使用的字符集
 *  `character_set_connection`：服务器处理请求时将字符集转换成这个字符集处理。操作具体列时，在转换为具体列的编码。
 *  `character_set_results`:服务器向客户端返回数据时使用的字符集

MySQL 设计这三个编码的时候，出于以下考虑：

*  一个 MySQL，可能有多种不同语言和操作系统或者国家的客户端，所以通过设置`character_set_client`还有`character_set_results`进行兼容。
 *  由于操作具体列数据的时候需要编码转换，如果`character_set_connection`和字段一致的话，就不用转换了，所以设置`character_set_connection`可以让 MySQL 用一种编码理解命令统一处理，同时设置`character_set_connection`为最常用的可以减少转换。

一般情况下，保持这三个一致就好。我们就设置好连接使用的字符集就行了。