MySQL 数据库的字符集和校对集介绍

[TOC]

### 1. 字符集
__字符集：它是二级制与字符的对应关系，是各类编码方式的集合__
数据库系统通过配置文件将一些特性保存成了变量，字符集便是其中的一种特性，所以当我们在创建数据库、表或字段时，如果没有指定字符集，那么数据库系统便使用自己的配置变量来对数据进行编码，当新增的数据使用数据库编码时便可能会报错，例如汉字在默认的utf8mb3下保存便会报错，这是因为utf8mb3占用3个字节，而汉字的GBK则是占用2个字节，保存时便会出现`拆字`的情形，这时编码不通过便报错了

#### 1.1. 查询数据库支持的字符集
```SQL
-- 查询数据支持的所有字符集
show charater set;
```
__以下便是MySQL支持的全部编码格式：__

| 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 |
| utf8mb3  | UTF-8 Unicode                   | utf8mb3_general_ci  |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |

#### 1.2. 查看服务器默认字符集
MySQL数据库系统会有一款支持所有语言编码类型的字符集，将对应MySQL配置变量设置为支持所有编码格式的字符集，便可解决语言不兼容的问题，例如utf8mb4便是这样的字符集

```SQL
-- 查看服务器默认配置的字符集
show variables like 'character_set%';
```
__以下为MySQL数据库关于字符集的配置__

| Variable_name（变量名称）| Value（值）|说明|
|--------------------------|--|--|
| character_set_client| gbk|服务器默认的客户端的数据的字符集，即服务端接收客户端数据时采用的字符集方式|
| character_set_connection| gbk|连接层字符集|
| character_set_database| utf8mb4|当前所在数据库的字符集|
| character_set_filesystem| binary||
| character_set_results| gbk|服务器默认的给外部数据的字符集，即服务器将数据返回给客户端时采用的字符集方式|
| character_set_server| utf8mb4||
| character_set_system| utf8mb3||
| character_sets_dir| C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |字符集所在物理位置|

#### 1.3. 修改服务器字符集配置
__语法：__<mark>set 配置项变量 = 字符集</mark>
使用上述语法可以对MySQL的配置项进行修改操作，修改时注意值的类型，建议先查询项对应值后再操作，使用set修改是会话级别的修改，就是说这次修改只对当前客户端当前连接有效，关闭便失效

```SQL
-- 将服务器端接收时采用的字符集修改为utf8mb4
set character_set_client = utf8mb4
-- 将服务器端返回客户端数据时采用的字符集修改为utf8mb4
set character_set_results = utf8mb4
```

要将中文插入和显示不出现乱码的情况，需要多次修改服务变量，这样操作繁复，这时可以使用快捷命令`set name 字符集`

```SQL
set name gbk;
```
> 上面的命令等效于同时给character_set_client、character_set_connection、character_set_results三个变量设置了gbk字符集，character_set_connection是连接层是字符集转变的中间层，如果设置与character_set_client和character_set_results一致会提高数据库效率，不一致也不会有问题

__说明：修改服务器字符集不会影响数据存储的数据编码方式，存储的数据编码由创建表时申明的编码方式决定，修改服务器字符集只对此次与客户端的交互有影响__

### 2. 校对集
__校对集就是数据比较的方式，主要有以下几种格式__

|格式|说明|
|--|--|
|`_bin`|binary，二进制比较，取出二进制位，一位一位进行比较，区分大小写|
|`_cs`|case sensitive，大小写敏感，区分大小写|
|`_ci`|case insensitive，大小写不敏感，不区分大小写|
|`_ai`|accent insensitive，不区分重音（声调）|
|`_as`|accent sensitive，区分重音（声调）|
|`_ks`|Kana 对假名敏感，用于日语|

#### 2.1. 查询数据库支持的校对集
__语法：__<mark>show collation [where 属性 = '值']</mark>
属性有Collation、Charset、Id、Default、Compiled、Sortlen、Pad_attribute

```SQL
-- 查询数据支持的所有校对集
show collation;
-- 查询字符集为utf8mb4的所有校对集
show collation where  Charset = 'utf8mb4';
```

__以下便是字符集utf8mb4支持的全部校对集种类：__

| Collation                   | Charset  | Id  | Default | Compiled | Sortlen | Pad_attribute |
|-----------------------------|----------|-----|---------|----------|---------|---------------|
| utf8mb4_0900_ai_ci          | utf8mb4  | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_ci          | utf8mb4  | 305 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_cs          | utf8mb4  | 278 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_bin            | utf8mb4  | 309 |         | Yes      |       1 | NO PAD        |
| utf8mb4_bg_0900_ai_ci       | utf8mb4  | 318 |         | Yes      |       0 | NO PAD        |
| utf8mb4_bg_0900_as_cs       | utf8mb4  | 319 |         | Yes      |       0 | NO PAD        |
| utf8mb4_bin                 | utf8mb4  |  46 |         | Yes      |       1 | PAD SPACE     |
| utf8mb4_bs_0900_ai_ci       | utf8mb4  | 316 |         | Yes      |       0 | NO PAD        |
| utf8mb4_bs_0900_as_cs       | utf8mb4  | 317 |         | Yes      |       0 | NO PAD        |
| utf8mb4_croatian_ci         | utf8mb4  | 245 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_cs_0900_ai_ci       | utf8mb4  | 266 |         | Yes      |       0 | NO PAD        |
| utf8mb4_cs_0900_as_cs       | utf8mb4  | 289 |         | Yes      |       0 | NO PAD        |
| utf8mb4_czech_ci            | utf8mb4  | 234 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_danish_ci           | utf8mb4  | 235 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_da_0900_ai_ci       | utf8mb4  | 267 |         | Yes      |       0 | NO PAD        |
| utf8mb4_da_0900_as_cs       | utf8mb4  | 290 |         | Yes      |       0 | NO PAD        |
| utf8mb4_de_pb_0900_ai_ci    | utf8mb4  | 256 |         | Yes      |       0 | NO PAD        |
| utf8mb4_de_pb_0900_as_cs    | utf8mb4  | 279 |         | Yes      |       0 | NO PAD        |
| utf8mb4_eo_0900_ai_ci       | utf8mb4  | 273 |         | Yes      |       0 | NO PAD        |
| utf8mb4_eo_0900_as_cs       | utf8mb4  | 296 |         | Yes      |       0 | NO PAD        |
| utf8mb4_esperanto_ci        | utf8mb4  | 241 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_estonian_ci         | utf8mb4  | 230 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_es_0900_ai_ci       | utf8mb4  | 263 |         | Yes      |       0 | NO PAD        |
| utf8mb4_es_0900_as_cs       | utf8mb4  | 286 |         | Yes      |       0 | NO PAD        |
| utf8mb4_es_trad_0900_ai_ci  | utf8mb4  | 270 |         | Yes      |       0 | NO PAD        |
| utf8mb4_es_trad_0900_as_cs  | utf8mb4  | 293 |         | Yes      |       0 | NO PAD        |
| utf8mb4_et_0900_ai_ci       | utf8mb4  | 262 |         | Yes      |       0 | NO PAD        |
| utf8mb4_et_0900_as_cs       | utf8mb4  | 285 |         | Yes      |       0 | NO PAD        |
| utf8mb4_general_ci          | utf8mb4  |  45 |         | Yes      |       1 | PAD SPACE     |
| utf8mb4_german2_ci          | utf8mb4  | 244 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_gl_0900_ai_ci       | utf8mb4  | 320 |         | Yes      |       0 | NO PAD        |
| utf8mb4_gl_0900_as_cs       | utf8mb4  | 321 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hr_0900_ai_ci       | utf8mb4  | 275 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hr_0900_as_cs       | utf8mb4  | 298 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hungarian_ci        | utf8mb4  | 242 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_hu_0900_ai_ci       | utf8mb4  | 274 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hu_0900_as_cs       | utf8mb4  | 297 |         | Yes      |       0 | NO PAD        |
| utf8mb4_icelandic_ci        | utf8mb4  | 225 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_is_0900_ai_ci       | utf8mb4  | 257 |         | Yes      |       0 | NO PAD        |
| utf8mb4_is_0900_as_cs       | utf8mb4  | 280 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ja_0900_as_cs       | utf8mb4  | 303 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ja_0900_as_cs_ks    | utf8mb4  | 304 |         | Yes      |      24 | NO PAD        |
| utf8mb4_latvian_ci          | utf8mb4  | 226 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_la_0900_ai_ci       | utf8mb4  | 271 |         | Yes      |       0 | NO PAD        |
| utf8mb4_la_0900_as_cs       | utf8mb4  | 294 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lithuanian_ci       | utf8mb4  | 236 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_lt_0900_ai_ci       | utf8mb4  | 268 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lt_0900_as_cs       | utf8mb4  | 291 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lv_0900_ai_ci       | utf8mb4  | 258 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lv_0900_as_cs       | utf8mb4  | 281 |         | Yes      |       0 | NO PAD        |
| utf8mb4_mn_cyrl_0900_ai_ci  | utf8mb4  | 322 |         | Yes      |       0 | NO PAD        |
| utf8mb4_mn_cyrl_0900_as_cs  | utf8mb4  | 323 |         | Yes      |       0 | NO PAD        |
| utf8mb4_nb_0900_ai_ci       | utf8mb4  | 310 |         | Yes      |       0 | NO PAD        |
| utf8mb4_nb_0900_as_cs       | utf8mb4  | 311 |         | Yes      |       0 | NO PAD        |
| utf8mb4_nn_0900_ai_ci       | utf8mb4  | 312 |         | Yes      |       0 | NO PAD        |
| utf8mb4_nn_0900_as_cs       | utf8mb4  | 313 |         | Yes      |       0 | NO PAD        |
| utf8mb4_persian_ci          | utf8mb4  | 240 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_pl_0900_ai_ci       | utf8mb4  | 261 |         | Yes      |       0 | NO PAD        |
| utf8mb4_pl_0900_as_cs       | utf8mb4  | 284 |         | Yes      |       0 | NO PAD        |
| utf8mb4_polish_ci           | utf8mb4  | 229 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_romanian_ci         | utf8mb4  | 227 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_roman_ci            | utf8mb4  | 239 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_ro_0900_ai_ci       | utf8mb4  | 259 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ro_0900_as_cs       | utf8mb4  | 282 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ru_0900_ai_ci       | utf8mb4  | 306 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ru_0900_as_cs       | utf8mb4  | 307 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sinhala_ci          | utf8mb4  | 243 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_sk_0900_ai_ci       | utf8mb4  | 269 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sk_0900_as_cs       | utf8mb4  | 292 |         | Yes      |       0 | NO PAD        |
| utf8mb4_slovak_ci           | utf8mb4  | 237 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_slovenian_ci        | utf8mb4  | 228 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_sl_0900_ai_ci       | utf8mb4  | 260 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sl_0900_as_cs       | utf8mb4  | 283 |         | Yes      |       0 | NO PAD        |
| utf8mb4_spanish2_ci         | utf8mb4  | 238 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_spanish_ci          | utf8mb4  | 231 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_sr_latn_0900_ai_ci  | utf8mb4  | 314 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sr_latn_0900_as_cs  | utf8mb4  | 315 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sv_0900_ai_ci       | utf8mb4  | 264 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sv_0900_as_cs       | utf8mb4  | 287 |         | Yes      |       0 | NO PAD        |
| utf8mb4_swedish_ci          | utf8mb4  | 232 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_tr_0900_ai_ci       | utf8mb4  | 265 |         | Yes      |       0 | NO PAD        |
| utf8mb4_tr_0900_as_cs       | utf8mb4  | 288 |         | Yes      |       0 | NO PAD        |
| utf8mb4_turkish_ci          | utf8mb4  | 233 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_unicode_520_ci      | utf8mb4  | 246 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_unicode_ci          | utf8mb4  | 224 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_vietnamese_ci       | utf8mb4  | 247 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_vi_0900_ai_ci       | utf8mb4  | 277 |         | Yes      |       0 | NO PAD        |
| utf8mb4_vi_0900_as_cs       | utf8mb4  | 300 |         | Yes      |       0 | NO PAD        |
| utf8mb4_zh_0900_as_cs       | utf8mb4  | 308 |         | Yes      |       0 | NO PAD        |

#### 2.2. 校对集应用
__只有当`数据产生对比`的时候校对集才会生效，校对集必须在`没有数据之前`声明好，如果有了数据后再去修改校对集，修改也是无效的，下面用一个排序的示例观察校对集的应用__

+ 不区分大小写utf8mb4_0900_ai_ci校对集
```SQL
-- 创建一个校对集为utf8mb4_0900_ai_ci的表
create table test_collation_ci(text char(1)) collate = utf8mb4_0900_ai_ci;
-- 插入数据
insert into test_collation_ci values('A'),('a'),('B'),('b');
-- 按插入顺序默认排序查询
select * from test_collation_ci;
+------+
| text |
+------+
| A    |
| a    |
| B    |
| b    |
+------+
-- 按字段text正序排列查询
select * from test_collation_ci order by text asc;
+------+
| text |
+------+
| A    |
| a    |
| B    |
| b    |
+------+
```
> 解释：从查询的结果中可以看出使用不区分大小的校对集，排序并未受到影响，A和a是一样的级别

+ 区分大小写的utf8mb4_0900_as_cs校对集
```SQL
-- 创建一个校对集为utf8mb4_0900_as_cs的表
create table test_collation_cs(text char(1)) collate = utf8mb4_0900_as_cs;
-- 插入数据
insert into test_collation_cs values('A'),('a'),('B'),('b');
-- 按插入顺序默认排序查询
 select * from test_collation_cs;
+------+
| text |
+------+
| A    |
| a    |
| B    |
| b    |
-- 按字段text正序排列查询
select * from test_collation_cs order by text asc;
+------+
| text |
+------+
| a    |
| A    |
| b    |
| B    |
+------+
```
> 解释：从查询的结果中可以看出使用区分大小的校对集，排序发生了变化，A和a不再是一个级别了，排序顺序为a < A < b < B