字符集和比較規(guī)則簡介

字符集簡介

我們知道在計算機中只能以二進制的方式對數(shù)據(jù)進行存儲，那么他們之間是怎樣對應(yīng)并進行轉(zhuǎn)換的？我們需要了解兩個概念：

• 字符范圍：我們可以將哪些字符轉(zhuǎn)換成二進制數(shù)據(jù)，也就是規(guī)定好字符范圍。
• 轉(zhuǎn)換過程：將一個字符轉(zhuǎn)換成一個二進制數(shù)據(jù)的過程也叫做編碼 ，將一個二進制數(shù)據(jù)轉(zhuǎn)換成一個字符的過程叫做解碼 。

我們抽象出一個字符集的概念來描述某個字符范圍的編碼規(guī)則。比方說我們來自定義一個名稱為 margu的字符集，它包含的字符范圍和編碼規(guī)則如下：包含字符 ‘a(chǎn)’ 、 ‘b’ 、 ‘A’ 、 ‘B’ 。
編碼規(guī)則如下：
采用1個字節(jié)編碼一個字符的形式，字符和字節(jié)的映射關(guān)系如下：
‘a(chǎn)’ -> 00000001 (十六進制：0x01)
‘b’ -> 00000010 (十六進制：0x02)
‘A’ -> 00000011 (十六進制：0x03)
‘B’ -> 00000100 (十六進制：0x04)
如果我們規(guī)定使用margu這個字符集，我們就可以用二進制形式表示一些字符串了，下邊是一些字符串用 margu 字符集編碼后的二進制表示：
‘Ba’ -> 0000010000000001 (十六進制：0x0401)
‘bAB’ -> 000000100000001100000100 (十六進制：0x020304)
‘CD’ -> 無法表示，字符集‘margu‘不包含字符’C’和’D’

所以，如果你想表示某個字符，一定要你選用的字符集支持。

比較規(guī)則簡介

在我們確定了 margu字符集表示字符的范圍以及編碼規(guī)則后，怎么比較兩個字符的大小呢？比較簡單的就是直接比較這兩個字符對應(yīng)的二進制編碼的大小，比方說字符 ‘a(chǎn)’ 的編碼為 0x01 ，字符 ‘b’ 的編碼為 0x02 ，所以 ‘a(chǎn)’ 小于 ‘b’ ，這種簡單的比較規(guī)則也可以被稱為二進制比較規(guī)則，英文名為 binary collation 。
二進制比較規(guī)則是簡單，但有時候并不符合現(xiàn)實需求，比如在很多場合對于英文字符我們都是不區(qū)分大小寫的，也就是說 ‘a(chǎn)’ 和 ‘A’ 是相等的，在這種場合下二進制比較規(guī)則就不再適用了，這時候我們可以這樣指定比較規(guī)則：
1 . 將兩個大小寫不同的字符全都轉(zhuǎn)為大寫或者小寫。
2 . 再比較這兩個字符對應(yīng)的二進制數(shù)據(jù)。
這是一種稍微復(fù)雜一點的比較規(guī)則，但是實際生活中的字符不止英文字符一種，比如我們的漢字就有上萬個，對于某一種字符集來說，比較兩個字符大小的規(guī)則可以制定出很多種，也就是說同一種字符集可以有多種比較規(guī)則，后面會介紹目前常用的一些字符集以及它們的一些比較規(guī)則。

一些重要的字符集

目前字符集的種類有很多，它們表示的字符范圍和用到的編碼規(guī)則可能都不一樣。下面是一些常用的字符集：

ASCII 字符集

共收錄128個字符，包括空格、標(biāo)點符號、數(shù)字、大小寫字母和一些不可見字符。由于總共128個字符，所以可以使用1個字節(jié)（8個bit位）來進行編碼，我們看一些字符的編碼方式：
‘A’ -> 01000001（十六進制：0x41，十進制：65）
‘H’ -> 01001000（十六進制：0x48，十進制：72）

ISO 8859-1 字符集

共收錄256個字符，是在 ASCII 字符集的基礎(chǔ)上又?jǐn)U充了128個西歐常用字符(包括德法兩國的字母)，也可以使用1個字節(jié)來進行編碼。這個字符集也有一個別名 latin1 。

GB2312 字符集

收錄了漢字以及拉丁字母、希臘字母、日文平假名及片假名字母、俄語西里爾字母。其中收錄漢字6763個，其他文字符號682個。同時這種字符集又兼容 ASCII 字符集，所以在編碼方式上顯得有些奇怪：

• 如果該字符在 ASCII 字符集中，則采用1字節(jié)編碼。
• 否則采用2字節(jié)編碼。

這種表示一個字符需要的字節(jié)數(shù)可能不同的編碼方式稱為變長編碼方式 。比方說字符串 ‘愛u’ ，其中 ‘愛’ 需要用2個字節(jié)進行編碼，編碼后的十六進制表示為 0xCED2 ， ‘u’ 需要用1個字節(jié)進行編碼，編碼后的十六進制表示為 0x75 ，所以拼合起來就是 0xCED275 。

注意：如何區(qū)分某個字節(jié)代表一個單獨的字符還是代表某個字符的一部分呢？前面說過ASCII字符集只收錄128個字符，使用0～127就可以表示全部字符，所以如果某個字節(jié)是在0～127之內(nèi)的，就意味著一個字節(jié)代表一個單獨的字符，否則就是兩個字節(jié)代表一個單獨的字符。

GBK 字符集

GBK 字符集只是在收錄字符范圍上對 GB2312 字符集作了擴充，編碼方式上兼容 GB2312 。

utf8

字符集收錄地球上能想到的所有字符，而且還在不斷擴充。這種字符集兼容 ASCII 字符集，采用變長編碼方式，編碼一個字符需要使用1～4個字節(jié)，比如：
‘A’ -> 01000001（十六進制：0x41）
‘啊’ -> 111001011001010110001010（十六進制：0xE5958A）
注意：其實準(zhǔn)確的說，utf8只是Unicode字符集的一種編碼方案，Unicode字符集可以采用utf8、utf16、utf32這幾種編碼方案，utf8使用1～4個字節(jié)編碼一個字符，utf16使用2個或4個字節(jié)編碼一個字符，utf32使用4個字節(jié)編碼一個字符。
MySQL中并不區(qū)分字符集和編碼方案的概念，所以后邊嘮叨的時候把utf8、utf16、utf32都當(dāng)作
一種字符集對待。

對于同一個字符，不同字符集也可能有不同的編碼方式。比如對于漢字 ‘我’ 來說， ASCII 字符集中沒有收錄這個字符， utf8 和 gb2312 字符集對漢字 我 的編碼方式如下：
utf8編碼：111001101000100010010001 (3個字節(jié)，十六進制表示是：0xE68891)
gb2312編碼：1100111011010010 (2個字節(jié)，十六進制表示是：0xCED2)

MySQL中支持的字符集和排序規(guī)則

MySQL中的utf8和utf8mb4

上邊說過utf8 字符集表示一個字符需要使用1～4個字節(jié)，但是我們常用的一些字符使用1～3個字節(jié)就可以表示了。而在Mysql中字符集表示一個字符所用最大字節(jié)長度在某些方面會影響系統(tǒng)的存儲和性能，所以Mysql衍生出utf8的兩個子字符集概念：
utf8mb3 ：精簡版的utf8 字符集，只使用1～3個字節(jié)表示字符。
utf8mb4 ：標(biāo)準(zhǔn)版的utf8 字符集，使用1～4個字節(jié)表示字符。
重點注意：在 MySQL 中 utf8 是 utf8mb3 的別名，所以之后在 MySQL 中提到 utf8 就意味著使用1~3個字節(jié)來表示一個字符，如果要想使用4字節(jié)編碼一個字符的情況，比如存儲一些emoji表情，那么需要完整指定使用utf8mb4 字符集。

字符集的查看

MySQL 支持好多好多種字符集，查看當(dāng)前 MySQL 中支持的字符集可以用下邊這個語句：
SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];
其中 CHARACTER SET 和 CHARSET 是同義詞，用任意一個都可以。

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 |
......
41 rows in set (0.00 sec)

從返回的結(jié)果中可以看到，目前使用的mysql（5.7版本）一共支持41種字符集，其中的 Default collation 列表示這種字符集中一種默認(rèn)的比較規(guī)則，ci結(jié)尾的表示都是忽略大小寫 。最后一列的Maxlen ，它代表該種字符集表示一個字符最多需要幾個字節(jié)。大家需要對常用的有印象，如下：

字符集名稱	描述	默認(rèn)比較規(guī)則	最長字節(jié)數(shù)
ascii	US ASCII	ascii_general_ci	1
latin1	cp1252 West European	latin1_swedish_ci	1
gb2312	GB2312 Simplified Chinese	gb2312_chinese_ci	2
gbk	GBK Simplified Chinese	gbk_chinese_ci	2
utf8	UTF-8 Unicode	utf8_general_ci	3
utf8mb4	UTF-8 Unicode	utf8mb4_general_ci	4

比較規(guī)則查看

查看 MySQL 中支持的比較規(guī)則的命令如下：
SHOW COLLATION [LIKE 匹配的模式];
一種字符集可能對應(yīng)有多種比較規(guī)則， MySQL 支持的字符集就已經(jīng)非常多了，所以支持的比較規(guī)則更多，我們先只查看一下 utf8 字符集下的比較規(guī)則：

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_general_mysql500_ci | utf8    | 223 |         | Yes      |       1 |
+--------------------------+---------+-----+---------+----------+---------+
27 rows in set (0.00 sec)

從返回的結(jié)果可以看到關(guān)于utf8的比較規(guī)則都有27種，這些比較規(guī)則的命名有一定的規(guī)律，具體規(guī)律如下：

• 比較規(guī)則名稱以與其關(guān)聯(lián)的字符集的名稱開頭。如上圖的查詢結(jié)果的比較規(guī)則名稱都是以 utf8 開頭的。
• 后邊緊跟著該比較規(guī)則主要作用于哪種語言，比如 utf8_spanish_ci 表示以西班牙語的規(guī)則比較，utf8_roman_ci 是以羅馬語的規(guī)則比較， utf8_general_ci 是一種通用的比較規(guī)則。
  名稱后綴意味著該比較規(guī)則是否區(qū)分語言中的重音、大小等，具體可以用的值如下：
  • _ai：accent insensitive ，不區(qū)分重音（insentive：不敏感的）
  • _as：accent sensitive ，區(qū)分重音
  • _ci：case insensitive ，不區(qū)分大小寫
  • _cs：case sensitive，區(qū)分大小寫
  • _bin：binary ，以二進制方式進行比較
    比如 utf8_general_ci 這個比較規(guī)則是以 ci 結(jié)尾的，說明不區(qū)分大小寫。
    每種字符集對應(yīng)若干種比較規(guī)則，每種字符集都有一種默認(rèn)的比較規(guī)則， SHOW COLLATION 的返回結(jié)果中的Default 列的值為 YES 的就是該字符集的默認(rèn)比較規(guī)則，比方說 utf8 字符集默認(rèn)的比較規(guī)則就是utf8_general_ci 。

字符集和比較規(guī)則的應(yīng)用

各級別的字符集和比較規(guī)則

MySQL 有4個級別的字符集和比較規(guī)則，分別是：

• 服務(wù)器級別
• 數(shù)據(jù)庫級別
• 表級別
• 列級別
  接下來看看怎么查看和設(shè)置這幾個級別的字符集和比較規(guī)則。

服務(wù)器級別

MySQL 提供了兩個系統(tǒng)變量來表示服務(wù)器級別的字符集和比較規(guī)則：

系統(tǒng)變量	說明
character_set_server	服務(wù)器級別的字符集
collation_server	服務(wù)器級別的比較規(guī)則

我們看一下這兩個系統(tǒng)變量的值，記不住全稱的可以用%進行匹配。

mysql> show variables like '%_server';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| character_set_server | utf8            |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
2 rows in set (0.01 sec)

可以看到在我的計算機中服務(wù)器級別默認(rèn)的字符集是 utf8 ，默認(rèn)的比較規(guī)則是utf8_general_ci 。
我們可以在啟動服務(wù)器程序時通過啟動選項或者在服務(wù)器程序運行過程中使用 SET 語句修改這兩個變量的值。比如我們可以在配置文件中這樣配置：
[server]
character_set_server=gbk
collation_server=gbk_chinese_ci
當(dāng)服務(wù)器啟動的時候讀取這個配置文件后這兩個系統(tǒng)變量的值便修改了。

數(shù)據(jù)庫級別

我們在創(chuàng)建和修改數(shù)據(jù)庫的時候可以指定該數(shù)據(jù)庫的字符集和比較規(guī)則，具體語法如下：
CREATE DATABASE 數(shù)據(jù)庫名
[[DEFAULT] CHARACTER SET 字符集名稱]
[[DEFAULT] COLLATE 比較規(guī)則名稱];

ALTER DATABASE 數(shù)據(jù)庫名
[[DEFAULT] CHARACTER SET 字符集名稱]
[[DEFAULT] COLLATE 比較規(guī)則名稱];
其中的 DEFAULT 可以省略，并不影響語句的語義。比方說我們新創(chuàng)建一個名叫 charset_demo_db 的數(shù)據(jù)庫，在創(chuàng)
建的時候指定它使用的字符集為 gb2312 ，比較規(guī)則為 gb2312_chinese_ci ：

mysql> create  database charset_demo_db character set gb2312 collate gb2312_chinese_ci;
Query OK, 1 row affected (0.00 sec)mysql>

如果想查看當(dāng)前數(shù)據(jù)庫使用的字符集和比較規(guī)則，可以查看下面兩個系統(tǒng)變量的值（前提是使用 USE 語句選擇要查看的數(shù)據(jù)庫，如果沒有指定要查看的數(shù)據(jù)庫，則變量與相應(yīng)的服務(wù)器級系統(tǒng)變量具有相同的值）：

#未指定數(shù)據(jù)庫，值與服務(wù)器級別的系統(tǒng)變量一致
mysql> show variables like '%_database';
+------------------------+-----------------+
| Variable_name          | Value           |
+------------------------+-----------------+
| character_set_database | utf8            |
| collation_database     | utf8_general_ci |
| skip_show_database     | OFF             |
+------------------------+-----------------+
3 rows in set (0.00 sec)
mysql> use charset_demo_db;
Database changed
mysql> show variables like '%_database';
+------------------------+-------------------+
| Variable_name          | Value             |
+------------------------+-------------------+
| character_set_database | gb2312            |
| collation_database     | gb2312_chinese_ci |
| skip_show_database     | OFF               |
+------------------------+-------------------+
3 rows in set (0.01 sec)

可以看到這個 charset_demo_db 數(shù)據(jù)庫的字符集和比較規(guī)則就是我們在創(chuàng)建語句中指定的。需要注意的是：數(shù)據(jù)庫中存在的兩個變量(character_set_database、collation_database)都是只讀的，沒法通過修改這兩個變量的值來修改數(shù)據(jù)的字符集和比較規(guī)則，想要修改數(shù)據(jù)庫的字符集和比較規(guī)則，需要在創(chuàng)建或者修改數(shù)據(jù)庫的時手動指定character set和collation變量的值，如果不指定的話，則默認(rèn)使用服務(wù)器級別的字符集和比較規(guī)則。

表級別

我們也可以在創(chuàng)建和修改表的時候指定表的字符集和比較規(guī)則，語法如下：
CREATE TABLE 表名 (列的信息)
[[DEFAULT] CHARACTER SET 字符集名稱]
[COLLATE 比較規(guī)則名稱]]

ALTER TABLE 表名
[[DEFAULT] CHARACTER SET 字符集名稱]
[COLLATE 比較規(guī)則名稱]
比方說我們在剛剛創(chuàng)建的 charset_demo_db 數(shù)據(jù)庫中創(chuàng)建一個名為 test 的表，并指定這個表的字符集和比較規(guī)則：

mysql> create table test(name varchar(10) ) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.08 sec)mysql> 

如果創(chuàng)建和修改表的語句中沒有指明字符集和比較規(guī)則，將使用該表所在數(shù)據(jù)庫的字符集和比較規(guī)則作為該表的字符集和比較規(guī)則。假設(shè)我們的創(chuàng)建表 test 的語句是這么寫的：
CREATE TABLE test(name varchar(10) ) ;
因為表test的建表語句中并沒有明確指定字符集和比較規(guī)則，則表test 的字符集和比較規(guī)則將繼承所在數(shù)據(jù)庫charset_demo_db 的字符集和比較規(guī)則，也就是 gb2312 和gb2312_chinese_ci 。

列級別

需要注意的是，對于存儲字符串的列，同一個表中的不同的列也可以有不同的字符集和比較規(guī)則。我們在創(chuàng)建和修改列定義的時候可以指定該列的字符集和比較規(guī)則，語法如下：
CREATE TABLE 表名(
列名 字符串類型 [CHARACTER SET 字符集名稱] [COLLATE 比較規(guī)則名稱],
其他列…
);
ALTER TABLE 表名 MODIFY 列名 字符串類型 [CHARACTER SET 字符集名稱] [COLLATE 比較規(guī)則名稱];

比如我們修改一下表 test 中列 name的字符集和比較規(guī)則可以這么寫：

mysql> alter  table test modify name varchar(10) charset gbk collate gbk_chinese_ci;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

對于某個列來說，如果在創(chuàng)建和修改的語句中沒有指明字符集和比較規(guī)則，將使用該列所在表的字符集和比較規(guī)則作為該列的字符集和比較規(guī)則。比方說表 test的字符集是 utf8 ，比較規(guī)則是 utf8_general_ci ，修改列 name的
語句是這么寫的：
ALTER TABLE test MODIFY name VARCHAR(10);
那列name的字符集和編碼將使用表test的字符集和比較規(guī)則，也就是utf8和utf8_general_ci 。
注意：在轉(zhuǎn)換列的字符集時需要注意，如果轉(zhuǎn)換前列中存儲的數(shù)據(jù)不能用轉(zhuǎn)換后的字符集進行表示會發(fā)生錯誤。比方說原先列使用的字符集是utf8，列中存儲了一些漢字，而現(xiàn)在把列的字符集轉(zhuǎn)換為ascii的話就會出錯，因為ascii字符集并不能表示漢字字符。

僅修改字符集或僅修改比較規(guī)則

由于字符集和比較規(guī)則是互相有聯(lián)系的，如果我們只修改了字符集，比較規(guī)則也會跟著變化，如果只修改了比較規(guī)則，字符集也會跟著變化，具體規(guī)則如下：

• 只修改字符集，則比較規(guī)則將變?yōu)樾薷暮蟮淖址J(rèn)的比較規(guī)則。
• 只修改比較規(guī)則，則字符集將變?yōu)樾薷暮蟮谋容^規(guī)則對應(yīng)的字符集。

不論哪個級別的字符集和比較規(guī)則，這兩條規(guī)則都適用，下面以服務(wù)器級別的字符集和比較規(guī)則為例測試一下：

• 只修改字符集，則比較規(guī)則將變?yōu)樾薷暮蟮淖址J(rèn)的比較規(guī)則。

mysql> set  character_set_server = gb2312;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%_server';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| character_set_server | gb2312            |
| collation_server     | gb2312_chinese_ci |
+----------------------+-------------------+
2 rows in set (0.00 sec)

• 只修改比較規(guī)則，則字符集將變?yōu)樾薷暮蟮谋容^規(guī)則對應(yīng)的字符集。

mysql> set  collation_server = utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%_server';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| character_set_server | utf8            |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
2 rows in set (0.00 sec)

各級別字符集和比較規(guī)則小結(jié)

下面總結(jié)以上4個級別字符集和比較規(guī)則之間的聯(lián)系：

• 如果創(chuàng)建或修改列時沒有顯式的指定字符集和比較規(guī)則，則該列默認(rèn)用表的字符集和比較規(guī)則
• 如果創(chuàng)建或修改表時沒有顯式的指定字符集和比較規(guī)則，則該表默認(rèn)用數(shù)據(jù)庫的字符集和比較規(guī)則
• 如果創(chuàng)建或修改數(shù)據(jù)庫時沒有顯式的指定字符集和比較規(guī)則，則該數(shù)據(jù)庫默認(rèn)用服務(wù)器的字符集和比較規(guī)則

知道了這些規(guī)則之后，對于給定的表，我們應(yīng)該知道它的各個列的字符集和比較規(guī)則是什么，從而根據(jù)這個列的類型來確定存儲數(shù)據(jù)時每個列的實際數(shù)據(jù)占用的存儲空間大小了。具體使用的字符集類型可以通過show create table test;進行查看，注意表名需要修改成自己要查詢的表。

客戶端和服務(wù)器通信中的字符集

編碼和解碼使用的字符集不一致的后果

說到底，字符串在計算機上的體現(xiàn)就是一個字節(jié)串，如果你使用不同字符集去解碼這個字節(jié)串，最后得到的結(jié)果可能千奇百怪。
我們知道字符 ‘我’ 在 utf8 字符集編碼下的字節(jié)串長這樣: 0xE68891 ，如果一個程序把這個字節(jié)串發(fā)送到另一個程序里，另一個程序用不同的字符集去解碼這個字節(jié)串，假設(shè)使用的是 gbk 字符集來解釋這串字節(jié)，解碼過程就是這樣的:

1. 首先看第一個字節(jié) 0xE6 ，它的值大于 0x7F (十進制:127)，說明是兩字節(jié)編碼，繼續(xù)讀一字節(jié)后是 0xE688 ，然后從 gbk 編碼表中查找字節(jié)為 0xE688 對應(yīng)的字符，發(fā)現(xiàn)是字符 ‘鎴’
2. 繼續(xù)讀一個字節(jié) 0x91 ，它的值也大于 0x7F ，再往后讀一個字節(jié)發(fā)現(xiàn)木有了，所以這是半個字符。
3. 所以 0xE68891 被 gbk 字符集解釋成一個字符 ‘鎴’ 和半個字符。

假設(shè)用 latin1 字符集去解釋這串字節(jié)，解碼過程如下:

1. 先讀第一個字節(jié) 0xE6 ，它對應(yīng)的 latin1 字符為 ? 。
2. 再讀第二個字節(jié) 0x88 ，它對應(yīng)的 latin1 字符為 ? 。
3. 再讀第二個字節(jié) 0x91 ，它對應(yīng)的 latin1 字符為 ‘ 。
4. 所以整串字節(jié) 0xE68891 被 latin1 字符集解釋后的字符串就是 ‘??‘’
   可見，如果對于同一個字符串編碼和解碼使用的字符集不一樣，會產(chǎn)生意想不到的結(jié)果，作為使用者的我們看上去就像是產(chǎn)生了亂碼一樣。

字符集轉(zhuǎn)換的概念

如果接收 0xE68891 這個字節(jié)串的程序按照 utf8 字符集進行解碼，然后又把它按照 gbk 字符集進行編碼，最后編碼后的字節(jié)串就是 0xCED2 ，我們把這個過程稱為 字符集的轉(zhuǎn)換 ，也就是字符串 ‘我’ 從 utf8 字符集轉(zhuǎn)換為gbk 字符集。

Mysql中字符集的轉(zhuǎn)換

我們知道從客戶端發(fā)往服務(wù)器的請求本質(zhì)上就是一個字符串，服務(wù)器向客戶端返回的結(jié)果本質(zhì)上也是一個字符串，而字符串其實是使用某種字符集編碼的二進制數(shù)據(jù)。這個字符串可不是使用一種字符集的編碼方式一條道走到黑的，從發(fā)送請求到返回結(jié)果這個過程中伴隨著多次字符集的轉(zhuǎn)換，在這個過程中會用到3個系統(tǒng)變量，我們先把它們寫出來看一下:

系統(tǒng)變量	描述
character_set_client	服務(wù)器解碼請求時使用的字符集
character_set_connection	服務(wù)器處理請求時會把請求字符串從 character_set_client 轉(zhuǎn)為 character_set_connection
character_set_results	服務(wù)器向客戶端返回數(shù)據(jù)時使用的字符集

這幾個系統(tǒng)變量在我的計算機上的默認(rèn)值如下(不同操作系統(tǒng)的默認(rèn)值可能不同)，注意只關(guān)注上面給的幾個變量，其他的在測試過程中應(yīng)該手動改過。

mysql> show variables like 'character_set_%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | gb2312                           |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

可以看到這三個系統(tǒng)變量的值都是 utf8 ，為了體現(xiàn)出字符集在請求處理過程中的變化，我們這里特意修改 一個系統(tǒng)變量的值:
mysql> set character_set_connection = gbk;
Query OK, 0 rows affected (0.00 sec)
所以現(xiàn)在系統(tǒng)變量 character_set_client 和 character_set_results 的值還是 utf8 ，而 character_set_connection 的值為 gbk ?，F(xiàn)在假設(shè)我們客戶端發(fā)送的請求是下邊這個字符串:
SELECT * FROM t WHERE s = ‘我’;
為了方便大家理解這個過程，我們只分析字符 ‘我’ 在這個過程中字符集的轉(zhuǎn)換。 現(xiàn)在看一下在請求從發(fā)送到結(jié)果返回過程中字符集的變化:

1. 客戶端發(fā)送請求所使用的字符集
   一般情況下客戶端所使用的字符集和當(dāng)前操作系統(tǒng)一致，不同操作系統(tǒng)使用的字符集可能不一樣，如下:

• Linux系統(tǒng)使用的是 utf8
• Windows 使用的是 gbk
  我在windows服務(wù)器上使用的xshell客戶端，并設(shè)置了編碼格式為utf8。所以字符 ‘我’ 在發(fā)送給服務(wù)器的請求中的字節(jié)形式就是: 0xE68891
  注意：如果使用的是可視化工具，比如securecrt/xshell之類的，這些工具可以使用自定義的字符集來編碼發(fā)送到服務(wù)器的字符串，而不采用操作系統(tǒng)默認(rèn)的字符集。

2. 服務(wù)器接收到客戶端發(fā)送來的請求其實是一串二進制的字節(jié)，它會認(rèn)為這串字節(jié)采用的字符集是 character_set_client ，然后把這串字節(jié)轉(zhuǎn)換為 character_set_connection 字符集編碼的字符。
   由于我的計算機上 character_set_client 的值是 utf8 ，首先會按照 utf8 字符集對字節(jié)串 0xE68891 進行解碼，得到的字符串就是 ‘我’ ，然后按照 character_set_connection 代表的字符集，也就是 gbk 進行編碼，得到的結(jié)果就是字節(jié)串 0xCED2 。
3. 因為表 test 的列 name 采用的是 gbk 字符集，與 character_set_connection 一致，所以直接到列中找字節(jié)值為 0xCED2 的記錄，最后找到了一條記錄。
   注意：如果某個列使用的字符集和character_set_connection代表的字符集不一致的話，還需要進行一次字符集轉(zhuǎn)換。
4. 上一步驟找到的記錄中的 name 列其實是一個字節(jié)串 0xCED2 ，name列是采用 gbk 進行編碼的，所以首先會將這個字節(jié)串使用 gbk 進行解碼，得到字符串 ‘我’ ，然后再把這個字符串使用 character_set_results 代表 的字符集，也就是 utf8 進行編碼，得到了新的字節(jié)串: 0xE68891 ，然后發(fā)送給客戶端。
5. 由于客戶端是用的字符集是 utf8 ，所以可以順利的將 0xE68891 解釋成字符‘我’ ，從而顯示到我們的顯示器上，所以我們使用者也正確讀懂了返回的結(jié)果。
   總的可以參照下面這個圖來總結(jié)以上的這幾個步驟：
   
   上面這個圖中我們需要注意以下幾個地方:

• 服務(wù)器認(rèn)為客戶端發(fā)送過來的請求是用 character_set_client 編碼的。假設(shè)你的客戶端采用的字符集和 character_set_client 不一樣的話（也就是編碼和解碼的規(guī)則不一樣），這就會出現(xiàn)意想不到的情況。比如我的客戶端使用的是 utf8 字符集，如果把系統(tǒng)變量 character_set_client 的值設(shè)置為 ascii 的話，服務(wù)器可能無法理解我們發(fā)送的請求，更別談處理這個請求了。
• 服務(wù)器將把得到的結(jié)果集使用 character_set_results 編碼后發(fā)送給客戶端。
  假設(shè)你的客戶端采用的字符集和 character_set_results 不一樣的話，這就可能會出現(xiàn)客戶端無法解碼結(jié)果 集的情況，結(jié)果就是在你的屏幕上出現(xiàn)亂碼。比如我的客戶端使用的是 utf8 字符集，如果把系統(tǒng)變量character_set_results 的值設(shè)置為 ascii 的話，可能會產(chǎn)生亂碼。
• character_set_connection 只是服務(wù)器在將請求的字節(jié)串從 character_set_client 轉(zhuǎn)換為 character_set_connection 時使用，它是什么其實沒多重要，但是一定要注意，該字符集包含的字符范圍一定要涵蓋請求中的字符，要不然會導(dǎo)致有的字符無法使用character_set_connection 代表的字符集進行編碼。比如你把 character_set_client 設(shè)置為 utf8 ，把 character_set_connection 設(shè)置成 ascii ，那么此時你如果從客戶端發(fā)送一個漢字到服務(wù)器，那么服務(wù)器無法使用 ascii 字符集來編碼這個漢字，就會向用戶發(fā)出一個警告。

了解了在 MySQL 中從發(fā)送請求到返回結(jié)果過程里發(fā)生的各種字符集轉(zhuǎn)換，但是為啥要這樣轉(zhuǎn)來轉(zhuǎn)去的呢?不覺得很繁瑣么？
答：是的很繁瑣，所以我們通常都把 character_set_client 、character_set_connection、 character_set_results 這三個系統(tǒng)變量設(shè)置成和客戶端使用的字符集一致的情況，這樣減少了很多無謂的字符集轉(zhuǎn)換。為了方便我們設(shè)置， MySQL 提供了一條非常簡便的語句:
SET NAMES 字符集名;
這一條語句產(chǎn)生的效果和我們執(zhí)行這3條的效果是一樣的:
SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;
比方說我的客戶端使用的是 utf8 字符集，所以需要把這幾個系統(tǒng)變量的值都設(shè)置為 utf8 :

mysql> SET names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'character_set_%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | gb2312                           |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

注意: 如果使用的是Windows系統(tǒng)作為客戶端，也就是沒有用其他的終端軟件，如xshell，那應(yīng)該設(shè)置成gbk。
另外，如果你想在啟動客戶端的時候就把 character_set_client 、 character_set_connection 、 character_set_results 這三個系統(tǒng)變量的值設(shè)置成一樣的，那我們可以在啟動客戶端的時候指定一個叫 default-character-set 的啟動選項，比如在配置文件里可以這么寫:
[client]
default-character-set=utf8
它起到的效果和執(zhí)行一遍 SET NAMES utf8 是一樣一樣的，都會將那三個系統(tǒng)變量的值設(shè)置成 utf8 。

比較規(guī)則的應(yīng)用

比較規(guī)則的作用通常體現(xiàn)比較字符串大小的表達(dá)式以及對某個字符串列進行排序，所以有時候也稱為排序規(guī)則 。比方說表 test 的列 name 使用的字符集是 gbk ，使用的比較規(guī)則是 gbk_chinese_ci ，我們向里邊插入幾條記錄:

mysql> INSERT INTO test(name) VALUES('a'), ('b'), ('A'), ('B');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> select * from test order by name;
+------+
| name |
+------+
| a    |
| A    |
| b    |
| B    |
| 我   |
+------+
5 rows in set (0.00 sec)
mysql> 

可以看到在默認(rèn)的比較規(guī)則 gbk_chinese_ci 中是不區(qū)分大小寫的，我們現(xiàn)在把列 name 的比較規(guī)則修改為 gbk_bin :

mysql> ALTER TABLE test  MODIFY name VARCHAR(10) COLLATE gbk_bin;
Query OK, 5 rows affected (0.17 sec)
Records: 5  Duplicates: 0  Warnings: 0

由于 gbk_bin 是直接比較字符的編碼，所以是區(qū)分大小寫的，我們再看一下排序后的查詢結(jié)果:

mysql> select * from test order by name;
+------+
| name |
+------+
| A    |
| B    |
| a    |
| b    |
| 我   |
+------+
5 rows in set (0.01 sec)

所以如果以后大家在對字符串做比較或者對某個字符串列做排序操作時沒有得到想象中的結(jié)果，需要思考一下是不是字符規(guī)則的問題。

總結(jié)：總的來說，在選用字符集時要選擇能支持表示字符的字符集，并盡量精簡。其次，使用時，盡量保證客戶端服務(wù)端（包括服務(wù)器級別，數(shù)據(jù)庫級別，表級別，列級別）字符集及比較規(guī)則一致，避免進行多次轉(zhuǎn)化及轉(zhuǎn)換出錯。

更多關(guān)于mysql的知識分享，請前往博客主頁。編寫過程中，難免出現(xiàn)差錯，敬請指出