從根上了解 MySQL 的字元集和比較規則

字元集

抽象的描述某個字元範圍的編碼規則（charset），比如ASCII、GBK、UTF8等。

編碼：把一個字元映射成一個二進制資料的過程
解碼：将一個二進制資料映射成一個字元的過程

比較規則

是針對某個字元集中的字元比較大小的一種規則（collation），比如gbk_chinese_ci、utf8_general_ci等。

一些重要的字元集

ASCII

一共128個字元，包括空格、标點符号、數字、大小寫字母和一些不可見字元

1個位元組編碼一個字元

例如：L
01001100（二進制）
0x4c（十六進制）
76（十進制）

ISO 8859-1

一共256個字元，在ASCII基礎擴增128個西歐常用字元

别名又叫 Latin1

GB2312

收錄漢字以及拉丁字母、希臘字母、日文字母、俄語字母，相容ASCII

漢字6763個，其它文字元号682個

1～2個位元組編碼一個字元

編碼規則：變長編碼方式（表示一個字元需要的位元組數可能不同）
1. 如果該字元在ASCII範圍内，則采用1位元組編碼1個字元
2. 否則采用2個位元組編碼1個字元

例如：i你
01101001 1100010011100011（二進制）
0x69 CE3（十六進制）
105 19227（十進制）

GBK

對GB2312進行擴充，相容GB2312

例如：'我'
1100111011010010（二進制）
CE‌D2（十六進制）

UTF8

收錄地球上能想到的所有字元，而且還在不斷擴充，相容ASCII

變長編碼方式，編碼1個字元需要1～4個位元組

例如：
'L'
01001100（二進制）
0x4C（十六進制）

'我'
111001101000100010010001（二進制）
0xE6‌88‌91（十六進制）

MySQL中支援的字元集和排序規則

utf8和utf8mb4

utf8：utf8mb3 閹割過的utf8字元集，隻使用1～3個位元組編碼字元。（預設）

utf8mb4：正宗的utf8字元集，使用1～4個位元組編碼字元。（可以存儲emoji表情）

字元集的檢視

SHOW (CHARACTER SET|CHARSET) [LIKE 比對的模式];

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 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

比較規則的檢視

SHOW COLLATION [LIKE 比對的模式];

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 |
| utf8_polish_ci           | utf8    | 197 |         | Yes      |       8 |
| utf8_estonian_ci         | utf8    | 198 |         | Yes      |       8 |
| utf8_spanish_ci          | utf8    | 199 |         | Yes      |       8 |
| utf8_swedish_ci          | utf8    | 200 |         | Yes      |       8 |
| utf8_turkish_ci          | utf8    | 201 |         | Yes      |       8 |
| utf8_czech_ci            | utf8    | 202 |         | Yes      |       8 |
| utf8_danish_ci           | utf8    | 203 |         | Yes      |       8 |
| utf8_lithuanian_ci       | utf8    | 204 |         | Yes      |       8 |
| utf8_slovak_ci           | utf8    | 205 |         | Yes      |       8 |
| utf8_spanish2_ci         | utf8    | 206 |         | Yes      |       8 |
| utf8_roman_ci            | utf8    | 207 |         | Yes      |       8 |
| utf8_persian_ci          | utf8    | 208 |         | Yes      |       8 |
| utf8_esperanto_ci        | utf8    | 209 |         | Yes      |       8 |
| utf8_hungarian_ci        | utf8    | 210 |         | Yes      |       8 |
| utf8_sinhala_ci          | utf8    | 211 |         | Yes      |       8 |
| utf8_german2_ci          | utf8    | 212 |         | Yes      |       8 |
| utf8_croatian_ci         | utf8    | 213 |         | Yes      |       8 |
| utf8_unicode_520_ci      | utf8    | 214 |         | Yes      |       8 |
| utf8_vietnamese_ci       | utf8    | 215 |         | Yes      |       8 |
| utf8_general_mysql500_ci | utf8    | 223 |         | Yes      |       1 |
| 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 |
+--------------------------+---------+-----+---------+----------+---------+
53 rows in set (0.00 sec)

字尾	英文解釋	描述
_ai	accent insensitive	不區分重音
_as	accent sensitive	區分重音
_ci	case insensitive	不區分大小寫
_cs	case sensitive	區分大小寫
_bin	binary	以二進制方式比較

後邊緊跟着該比較規則主要作用于哪種語言，比如utf8_polish_ci表示以波蘭語的規則比較，utf8_spanish_ci是以西班牙語的規則比較，utf8_general_ci是一種通用的比較規則。

字元集和比較規則的應用

伺服器級别

檢視

SHOW VARIABLES LIKE 'character_set_server';//伺服器級别字元集
SHOW VARIABLES LIKE 'collation_server';//伺服器級别比較規則

mysql> show variables like 'character_set_server';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_server | gbk   |
+----------------------+-------+

mysql> show variables like 'collation_server';
+------------------+----------------+
| Variable_name | Value |
+------------------+----------------+
| collation_server | gbk_chinese_ci |
+------------------+----------------+

設定

通過配置檔案永久設定（當然也可以通過指令行進行目前會話設定）
[server] 
character_set_server=gbk 
collation_server=gbk_chinese_ci

資料庫級别

use 資料庫;//需先選擇對應的資料庫
SHOW VARIABLES LIKE 'character_set_database';//資料庫級别的字元集
SHOW VARIABLES LIKE 'collation_database';//資料庫級别的比較規則

mysql> use school;
Database changed
mysql> show variables like 'character_set_database';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+

mysql> show variables like 'collation_database';
+--------------------+-------------------+
| Variable_name | Value  |
+--------------------+-------------------+
| collation_database | latin1_swedish_ci |
+--------------------+-------------------+

CREATE DATABASE 資料庫名
[[DEFAULT] CHARACTER SET 字元集名稱]
[[DEFAULT] COLLATE 比較規則名稱];

ALTER DATABASE 資料庫名 
[[DEFAULT] CHARACTER SET 字元集名稱] 
[[DEFAULT] COLLATE 比較規則名稱];

備注：
character_set_database 和 collation_database 這兩個系統變量是隻讀的，我們不能通過修改這兩個變量的值而改變目前資料庫的字元集和比較規則。

CREATE DATABASE 資料庫名;
如在建立資料庫不設定字元集和比較規則的話，将使用伺服器級别的字元集和比較規則作為資料庫的字元集和比較規則。

表級别

show create table 表名 \G

CREATE TABLE 表名 (列的資訊)
[[DEFAULT] CHARACTER SET 字元集名稱]
[COLLATE 比較規則名稱]]

ALTER TABLE 表名 
[[DEFAULT] CHARACTER SET 字元集名稱] 
[COLLATE 比較規則名稱]

如在建立資料表不設定字元集和比較規則的話，将使用資料庫級别的字元集和比較規則作為資料庫的字元集和比較規則。

列級别

show create table 表名 \G

CREATE TABLE 表名(
    列名 字元串類型 [CHARACTER SET 字元集名稱] [COLLATE 比較規則名稱],
    其他列...
);

ALTER TABLE 表名 MODIFY 列名 字元串類型 [CHARACTER SET 字元集名稱] [COLLATE 比較規則名稱];

如在建立資料表列不設定字元集和比較規則的話，将使用資料表級别的字元集和比較規則作為資料庫的字元集和比較規則。

備注：

在轉換列的字元集時需要注意，如果轉換前列中存儲的資料不能用轉換後的字元集進行表示會發生錯誤。比方說原先列使用的字元集是utf8，列中存儲了一些漢字，現在把列的字元集轉換為ascii的話就會出錯，因為ascii字元集并不能表示漢字字元。

僅修改字元集或僅修改比較規則

* 隻修改字元集，則比較規則将變為修改後的字元集預設的比較規則
* 隻修改比較規則，則字元集将變為修改後的比較規則對應的字元集

例如：
mysql> show variables like 'character_set_server';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| character_set_server | gbk  |
+----------------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'collation_server';
+------------------+----------------+
| Variable_name | Value |
+------------------+----------------+
| collation_server | gbk_chinese_ci |
+------------------+----------------+
1 row in set (0.01 sec)
mysql> set character_set_server=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'character_set_server';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| character_set_server | utf8 |
+----------------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'collation_server';
+------------------+-----------------+
| Variable_name | Value  |
+------------------+-----------------+
| collation_server | utf8_general_ci |
+------------------+-----------------+
1 row in set (0.01 sec)

用戶端和伺服器通信中的字元集

SHOW VARIABLES LIKE 'character_set_client';//伺服器解碼請求時使用的字元集
SHOW VARIABLES LIKE 'character_set_connection';//伺服器處理請求時會把請求字元串從character_set_client轉為character_set_connection
SHOW VARIABLES LIKE 'character_set_results';//伺服器向用戶端傳回資料時使用的字元集

流程

1、用戶端使用作業系統的字元集編碼請求字元串，向伺服器發送的是經過編碼的一個位元組串

2、伺服器将用戶端發送來的位元組串采用character_set_client代表的字元集進行解碼，将解碼後的字元串再按照character_set_connection代表的字元集進行編碼

3、如果character_set_connection代表的字元集和具體操作的列使用的字元集一緻，則直接進行相應操作，否則的話需要将請求中的字元串從character_set_connection代表的字元集轉換為具體操作的列使用的字元集之後再進行操作

4、将從某個列擷取到的位元組串從該列使用的字元集轉換為character_set_results代表的字元集後發送到用戶端。

5、用戶端使用作業系統的字元集解析收到的結果集位元組串。

方法一：（目前會話有效）
SET NAMES 字元集名;

方法二：（目前會話有效）
SET character_set_client = 字元集名; 
SET character_set_connection = 字元集名; 
SET character_set_results = 字元集名;

方法三：配置檔案永久生效
[client] default-character-set=utf8

亂碼情況

mysql> show variables like 'character_set_client';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_client | gbk   |
+----------------------+-------+

mysql> show variables like 'character_set_connection';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| character_set_connection | utf8 |
+--------------------------+-------+

mysql> show variables like 'character_set_results';
+-----------------------+-------+
| Variable_name  | Value |
+-----------------------+-------+
| character_set_results | utf8 |
+-----------------------+-------+

mysql> show create table student \G
*************************** 1. row ***************************
 Table: student
Create Table: CREATE TABLE `student` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `name` varchar(30) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4

mysql> insert into student(name) values('張胖');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student where name = '張胖';//顯示結果出現亂碼
+----+-----------+
| id | name |
+----+-----------+
| 3 | 寮犺儢 |
+----+-----------+
1 row in set (0.00 sec)

出現亂碼原因是character_set_client設定的字元集與資料的字元集不一緻導緻的

解決辦法：把character_set_client設定成utf8即可

備注：我們通常都把 character_set_client、character_set_connection、character_set_results 這三個系統變量設定成和用戶端使用的字元集一緻的情況，這樣減少了很多無謂的字元集轉換，也可以避免亂碼情況的發生。

參考：掘金小冊《MySQL 是怎樣運作的：從根兒上了解 MySQL》書籍《MySQL高性能》

從根上了解 MySQL 的字元集和比較規則

字元集

比較規則

一些重要的字元集

ASCII

ISO 8859-1

GB2312

GBK

UTF8

MySQL中支援的字元集和排序規則

utf8和utf8mb4

字元集的檢視

比較規則的檢視

字元集和比較規則的應用

伺服器級别

檢視

設定

資料庫級别

表級别

列級别

僅修改字元集或僅修改比較規則

用戶端和伺服器通信中的字元集

流程

亂碼情況

繼續閱讀