xyZGHio

樱花同载酒，终似少年游

0%

MySQL之字符集、比较规则

发表于 更新于 阅读数： 评论数：
本文字数： 5.8k 阅读时长 ≈ 12 分钟

这里让我们了解下MySQL中字符集、比较规则相关的知识

abstract.png

Character Set 字符集

可通过下面命令查看MySQL中支持的字符集。严格来说UTF-8、UTF-16、UTF-32这些并不能称之为字符集，它们只是Unicode字符集下的不同编码方案。本文为了行文方便，故使用字符集来称呼

1
2
3
4
-- 查看支持的字符集，CHARACTER SET 与 CHARSET 二者可以随意选用其中一个
SHOW {CHARACTER SET|CHARSET}; 
-- 支持匹配查询，亦可通过%实现模糊查询
SHOW {CHARACTER SET|CHARSET} [LIKE 匹配的模式];

下图为MySQL中支持的全部字符集，当然不同版本下其查询结果可能有些许出入

figure 1.jpeg

上图查询结果中的MaxLen列，其含义表示的是该字符集中表示一个字符最多需要的字节数。通过上图红框，我们可以看出MySQL中的utf8字符集、utf8mb4字符集的MaxLen值分别为3、4。而我们知道实际在utf8字符集中，其需要1～4个字节来表示一个字符。那为啥MySQL下的utf8字符集却最多只需3个字节呢？原因在于MySQL中一个字符所用的最大字节长度有时可能会对存储、性能等方面造成影响，故早期MySQL设计者对于utf8字符集的支持只到3个字节长度的字符，即其对于utf8中需要4个字节表示的字符是不支持的。故MySQL中的utf8字符集又被称之为utf8mb3字符集(most bytes 3) 。通常3字节的utf8字符已经能够满足绝大多数场景的需要了。但对于一些特殊场景，如emoji表情在实际的utf8字符集中就需要4个字节来表示。为此MySQL从5.5.3版本开始，提供了utf8mb4字符集，其是utf8(utf8mb3)的超集，将utf8字符的支持拓展到4字节。故utf8mb4才是我们真正意义上的utf8字符集

Collation 比较规则

Collation比较规则，指的是对字符进行比较、排序的规则，故有时又被称作排序规则。可通过下面命令查看MySQL中支持的比较规则

1
2
3
4
-- 查看支持的比较规则
SHOW COLLATION; 
-- 支持匹配查询，亦可通过%实现模糊查询
SHOW COLLATION [LIKE 匹配的模式];

下图为MySQL中支持的比较规则，由于篇幅有限这里仅显示部分了。当然不同版本下其查询结果可能有些许出入

figure 2.jpeg

这里我们对utf8_general_ci比较规则进行讲解。在MySQL中，一个字符集下可以支持多个比较规则，而一个比较规则却只关联一个字符集。故比较规则的命名是以其所关联的字符集名称开头，即名称utf8_general_ci中的”utf8”表示该比较规则只能在utf8字符集下才可以使用。当然该关联性也可以从Charset列看出。名称中间的部分表示该比较规则以哪种语言的规则进行比较，例如utf8_croatian_ci规则的”croatian”表示克罗地亚语，则该比较规则是以克罗地亚语的规则进行比较。这里名称utf8_general_ci中的”general”表示其是一种通用的规则。而名称中的”ci”后缀则意为该比较规则不区分大小写。下面列出了常见后缀的含义。上图Default列如果为YES，则表示该比较规则是其所关联的字符集的默认比较规则，即utf8_general_ci是MySQL中utf8字符集默认的比较规则

figure 3.jpeg

现在让我们通过一个示例来看看，不同的比较规则对于SQL语句的结果有什么影响

1. 大小写敏感的比较规则

通过下面的语句来修改report表name字段的比较规则，使用大小敏感(case sensitive)的比较规则

1
alter table report modify name varchar(255) collate utf8mb4_0900_bin;

两个SQL语句的结果如下所示

figure 4.png

figure 5.png

2. 大小写不敏感的比较规则

通过下面语句让report表name字段使用大小不敏感(case insensitive)的比较规则

1
alter table report modify name varchar(255) collate utf8mb4_0900_ai_ci;

两个SQL语句的结果如下所示

figure 6.png

figure 7.png

至此我们可以看到，在不同的比较规则下字符(串)类型的字段排序、比较的结果可能会大相径庭

不同级别下的字符集、比较规则

MySQL中的字符集、比较规则有四个级别——即服务器级别、数据库级别、表级别、列(字段)级别。其作用范围正如其名，依次降低分别为MySQL服务、数据库、表、列(字段)。某个级别如果没有显式指定字符集和比较规则，则会用上一个级别的字符集和比较规则的设定。比如，创建表时若未指定字符集和比较规则，则该表会继承使用其所在数据库的字符集、比较规则配置

我们知道在一个字符集下其可能对应有多个比较规则，并且其中有一个比较规则是该字符集的默认比较规则，故当我们仅显式的配置字符集而未指定比较规则时，则会使用该字符集对应的默认比较规则；而对于一个比较规则而言，其只可能关联一个字符集，故当我们仅显式的配置比较规则而未指定字符集时，则会使用该比较规则所关联的字符集

服务器级别

关于服务器级别的字符集、比较规则，MySQL中提供了以下两个系统变量

  • character_set_server：服务器级别的字符集
  • collation_server：服务器级别的比较规则

可通过show variables语句查看，示例如下

1
2
show variables like 'character_set_server';
show variables like 'collation_server';

本机结果如下所示

figure 8.jpeg

欲修改服务器级别的字符集、比较规则配置，可通过SET语句实现。若期望永久修改，则可以在MySQL配置文件的[server]选项组进行配置，示例如下

1
2
3
[server]
character_set_server=utf8
collation_server=utf8_general_ci

数据库级别

关于数据库级别的字符集、比较规则，MySQL中提供了以下两个系统变量。故如果查看需要某数据库的字符集、比较规则，则必须先使用use语句选择该数据库，然后再通过show variables语句查看

  • character_set_database：当前数据库的字符集
  • collation_database：当前数据库的比较规则

由于上述两个系统变量是只读的，故无法直接通过修改系统变量方式来改变数据库的字符集、比较规则。为此在MySQL中，
当我们创建、修改数据库时支持显式地设置数据库的字符集或比较规则。SQL语句语法如下

1
2
3
4
5
6
7
8
9
-- 创建数据库并设置其字符集或比较规则
CREATE DATABASE 数据库名
    [ {CHARACTER SET|CHARSET} 字符集名称]
    [COLLATE 比较规则名称];

-- 修改数据库以设置其字符集或比较规则
ALTER DATABASE 数据库名
    [ {CHARACTER SET|CHARSET} 字符集名称]
    [COLLATE 比较规则名称];

现在我们来创建一个名为test2的数据库，并显式地设置数据库字符集为utf8。语句如下

1
2
3
4
5
6
7
8
-- 创建数据库test2, 并设置其字符集为utf8
create database test2
charset utf8;

-- 查看test2数据库的字符集、比较规则
use test2;
show variables like 'character_set_database';
show variables like 'collation_database';

结果如下，符合预期

figure 9.png

表级别

类似地，当我们创建、修改表时同样支持显式地设置表的字符集或比较规则。SQL语句语法如下

1
2
3
4
5
6
7
8
9
-- 创建表并设置其字符集或比较规则
CREATE TABLE 表名 <字段信息>
    [ {CHARACTER SET|CHARSET} 字符集名称]
    [COLLATE 比较规则名称]]
    
-- 修改表以设置其字符集或比较规则
ALTER TABLE 表名
    [ {CHARACTER SET|CHARSET} 字符集名称]
    [COLLATE 比较规则名称]

关于表的字符集、比较规则，可通过下面的语句查看

1
2
3
4
-- 通过查看表的创建语句来查看其字符集、比较规则
show create table <表名>;
-- 通过此语句可以查看表的比较规则
show table status from <数据库名> like '<表名>';

现在，就让我们通过一个示例来验证下。SQL语句如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 创建task表，并指定使用utf8字符集
use test1;
create table task
(
    idx int auto_increment,
    primary key (idx)
) charset utf8;

-- 修改task表的字符集为utf8mb4
alter table task
charset utf8mb4;

-- 查看task表的字符集
show create table task;	
-- 查看task表的比较规则
show table status from test1 like 'task';

查询结果如下，符合预期

figure 10.jpeg

列级别

对于字符串类型的列(字段)而言，一个表中的不同列(字段)也可以设置不同的字符集、比较规则。具体地，可在创建、修改列(字段)时显式地设置其字符集或比较规则。SQL语句语法如下

1
2
3
4
5
6
7
8
9
10
11
-- 定义列(字段)并设置其字符集或比较规则
CREATE TABLE 表名(
    字段名 字符串类型 [ {CHARACTER SET|CHARSET} 字符集名称] [COLLATE 比较规则名称],
    其他列信息...
);

-- 修改已有列(字段)以设置其字符集或比较规则
ALTER TABLE 表名 MODIFY 字段名 字符串类型 [ {CHARACTER SET|CHARSET} 字符集名称] [COLLATE 比较规则名称];

-- 添加列(字段)并设置其字符集或比较规则
ALTER TABLE 表名 ADD 字段名 字符串类型 [ {CHARACTER SET|CHARSET} 字符集名称] [COLLATE 比较规则名称];

同样地，如果期望查看列(字段)的字符集、比较规则，亦可通过查看该表创建语句的方式来实现，即

1
2
-- 通过查看表的创建语句来查看其字符集、比较规则
show create table <表名>;

现在，就让我们通过一个示例来验证下。SQL语句如下

1
2
3
4
-- 向report表添加一个名为name2的字段，并设置其字符集为utf8
alter table report add name2 varchar(255) charset utf8;
-- 查看表的创建语句
SHOW CREATE TABLE report;

查询结果如下，符合预期

figure 11.jpeg

交互时的字符集转换

在MySQL客户端与服务端交互的过程中，会出现字符集的转换。具体如下：

  1. 我们知道客户端发给服务端的SQL语句，本质上就是一个字符串
  2. 服务端在收到客户端的SQL语句后，会先使用系统变量character_set_client所指定的字符集对其进行解码，然后再使用系统变量character_set_connection所指定的字符集对其进行编码
  3. 如果 系统变量character_set_connection所指定的字符集 与 SQL语句所指向的某列(字段)的字符集 不一致，则SQL语句还需要再次进行解码-编码操作
  4. 最后，将查询结果先使用 具体的列(字段)使用的字符集 进行解码，再使用系统变量character_set_results所指定的字符集进行编码，并返回给客户端

通过以上的过程，我们可以更加清楚的理解下面三个系统变量的具体作用

  • character_set_client：服务器解码客户端请求时使用的字符集
  • character_set_connection：服务器处理请求时会把请求字符串从character_set_client所指定的字符集转为character_set_connection所指定的字符集
  • character_set_results：服务器向客户端返回结果时使用的字符集

故当系统变量character_set_client、character_set_results的设置与当前客户端使用的字符集不符时，很容易出现一些奇怪的现象。当然原因也很简单，一方面服务端无法正确解码客户端的请求，另一方面客户端也无法正确解码服务端的结果。通常情况下，客户端所使用的字符集与其所在的操作系统使用的字符集一致。具体地，类Unix系统使用utf8字符集，Windows系统使用gbk字符集

为了减少、避免上述提到的这些不必要的字符集转换过程，可通过下面的命令将上述三个系统变量均设置为客户端所使用的字符集

1
2
3
SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;

有时候，三条命令过于麻烦。为了方便MySQL中提供了下面的命令，同样可实现上述效果

1
2
3
-- 设置系统变量character_set_client、character_set_connection、character_set_results
-- 为指定的字符集
SET NAMES 字符集名称

如果期望在启动客户端的时候，将上述三个系统变量设置为相同的。可在MySQL配置文件的client选项组添加如下配置项

1
2
[client]
default-character-set=字符集名

Note

这里描述SQL语法语句的括号释义，与Linux命令说明的括号释义保持一致，即

  • [] : 方括号表示可选的
  • <> : 尖括号表示必选的
  • {} : 大括号表示必须在{}内给出的选择里选一个
推荐文章
请我喝杯咖啡捏～

欢迎关注我的微信公众号：青灯抽丝