帶你搞懂MySQL字符集和比較規(guī)則

326

本篇文章給大家?guī)砹岁P(guān)于mysql中字符集比較的相關(guān)知識，其中包括了mysql中支持的字符集和排序規(guī)則，希望對大家有幫助。

帶你搞懂MySQL字符集和比較規(guī)則

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

字符集簡介

我們知道在計算機中只能存儲二進制數(shù)據(jù)，那該怎么存儲字符串呢？當(dāng)然是建立字符與二進制數(shù)據(jù)的映射關(guān)系了，建立這個關(guān)系最起碼要搞清楚兩件事兒：

  1. 你要把哪些字符映射成二進制數(shù)據(jù)？

    也就是界定清楚字符范圍。

  2. 怎么映射？

    將一個字符映射成一個二進制數(shù)據(jù)的過程也叫做編碼，將一個二進制數(shù)據(jù)映射到一個字符的過程叫做解碼。

人們抽象出一個字符集的概念來描述某個字符范圍的編碼規(guī)則。比方說我們來自定義一個名稱為xiaohaizi的字符集，它包含的字符范圍和編碼規(guī)則如下：

  • 包含字符’a’、’b’、’A’、’B’。

  • 編碼規(guī)則如下：

    采用1個字節(jié)編碼一個字符的形式，字符和字節(jié)的映射關(guān)系如下：

    'a' -> 00000001 (十六進制：0x01) 'b' -> 00000010 (十六進制：0x02) 'A' -> 00000011 (十六進制：0x03) 'B' -> 00000100 (十六進制：0x04)

有了xiaohaizi字符集，我們就可以用二進制形式表示一些字符串了，下邊是一些字符串用xiaohaizi字符集編碼后的二進制表示：

'bA' -> 0000001000000011  (十六進制：0x0203) 'baB' -> 000000100000000100000100  (十六進制：0x020104) 'cd' -> 無法表示，字符集xiaohaizi不包含字符'c'和'd'

比較規(guī)則簡介

在我們確定了xiaohaizi字符集表示字符的范圍以及編碼規(guī)則后，怎么比較兩個字符的大小呢？最容易想到的就是直接比較這兩個字符對應(yīng)的二進制編碼的大小，比方說字符’a’的編碼為0x01，字符’b’的編碼為0x02，所以’a’小于’b’，這種簡單的比較規(guī)則也可以被稱為二進制比較規(guī)則，英文名為binary collation。

二進制比較規(guī)則是簡單，但有時候并不符合現(xiàn)實需求，比如在很多場合對于英文字符我們都是不區(qū)分大小寫的，也就是說’a’和’A’是相等的，在這種場合下就不能簡單粗暴的使用二進制比較規(guī)則了，這時候我們可以這樣指定比較規(guī)則：

  1. 將兩個大小寫不同的字符全都轉(zhuǎn)為大寫或者小寫。
  2. 再比較這兩個字符對應(yīng)的二進制數(shù)據(jù)。

這是一種稍微復(fù)雜一點點的比較規(guī)則，但是實際生活中的字符不止英文字符一種，比如我們的漢字有幾萬之多，對于某一種字符集來說，比較兩個字符大小的規(guī)則可以制定出很多種，也就是說同一種字符集可以有多種比較規(guī)則，我們稍后就要介紹各種現(xiàn)實生活中用的字符集以及它們的一些比較規(guī)則。

一些重要的字符集

不幸的是，這個世界太大了，不同的人制定出了好多種字符集，它們表示的字符范圍和用到的編碼規(guī)則可能都不一樣。我們看一下一些常用字符集的情況：

  • ASCII字符集

    共收錄128個字符，包括空格、標(biāo)點符號、數(shù)字、大小寫字母和一些不可見字符。由于總共才128個字符，所以可以使用1個字節(jié)來進行編碼，我們看一些字符的編碼方式：

    'L' ->  01001100（十六進制：0x4C，十進制：76） 'M' ->  01001101（十六進制：0x4D，十進制：77）

  • ISO 8859-1字符集

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

  • GB2312字符集

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

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

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

    小貼士：  我們怎么區(qū)分某個字節(jié)代表一個單獨的字符還是代表某個字符的一部分呢？別忘了`ASCII`字符集只收錄128個字符，使用0～127就可以表示全部字符，所以如果某個字節(jié)是在0～127之內(nèi)的，就意味著一個字節(jié)代表一個單獨的字符，否則就是兩個字節(jié)代表一個單獨的字符。

  • GBK字符集

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

  • utf8字符集

    收錄地球上能想到的所有字符，而且還在不斷擴充。這種字符集兼容ASCII字符集，采用變長編碼方式，編碼一個字符需要使用1～4個字節(jié)，比方說這樣：

    'L' ->  01001100（十六進制：0x4C） '啊' ->  111001011001010110001010（十六進制：0xE5958A）
    小貼士：  其實準(zhǔn)確的說，utf8只是Unicode字符集的一種編碼方案，Unicode字符集可以采用utf8、utf16、utf32這幾種編碼方案，utf8使用1～4個字節(jié)編碼一個字符，utf16使用2個或4個字節(jié)編碼一個字符，utf32使用4個字節(jié)編碼一個字符。更詳細的Unicode和其編碼方案的知識不是本書的重點，大家上網(wǎng)查查哈～  MySQL中并不區(qū)分字符集和編碼方案的概念，所以后邊嘮叨的時候把utf8、utf16、utf32都當(dāng)作一種字符集對待。

對于同一個字符，不同字符集也可能有不同的編碼方式。比如對于漢字’我’來說，ASCII字符集中根本沒有收錄這個字符，utf8和gb2312字符集對漢字我的編碼方式如下：

utf8編碼：111001101000100010010001 (3個字節(jié)，十六進制表示是：0xE68891) gb2312編碼：1011000010101110 (2個字節(jié)，十六進制表示是：0xB0AE)

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

MySQL中的utf8和utf8mb4

我們上邊說utf8字符集表示一個字符需要使用1～4個字節(jié)，但是我們常用的一些字符使用1～3個字節(jié)就可以表示了。而在MySQL中字符集表示一個字符所用最大字節(jié)長度在某些方面會影響系統(tǒng)的存儲和性能，所以設(shè)計MySQL的大叔偷偷的定義了兩個概念：

  • utf8mb3：閹割過的utf8字符集，只使用1～3個字節(jié)表示字符。

  • utf8mb4：正宗的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 CHARSET; +----------+---------------------------------+---------------------+--------+ | Charset  | Description                     | Default collation   | Maxlen | +----------+---------------------------------+---------------------+--------+ | big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 | ... | latin1   | cp1252 West European            | latin1_swedish_ci   |      1 | | latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 | ... | ascii    | US ASCII                        | ascii_general_ci    |      1 | ... | gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 | ... | gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 | | latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 | ... | utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 | | ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 | ... | latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 | | utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 | | utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 | | utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 | ... | utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 | | binary   | Binary pseudo charset           | binary              |      1 | ... | gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 | +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.01 sec)

可以看到，我使用的這個MySQL版本一共支持41種字符集，其中的Default collation列表示這種字符集中一種默認的比較規(guī)則。大家注意返回結(jié)果中的最后一列Maxlen，它代表該種字符集表示一個字符最多需要幾個字節(jié)。為了讓大家的印象更深刻，我把幾個常用到的字符集的Maxlen列摘抄下來，大家務(wù)必記住：

字符集名稱 Maxlen
ascii 1
latin1 1
gb2312 2
gbk 2
utf8 3
utf8mb4 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_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 | +--------------------------+---------+-----+---------+----------+---------+ 27 rows in set (0.00 sec)

這些比較規(guī)則的命名還挺有規(guī)律的，具體規(guī)律如下：

  • 比較規(guī)則名稱以與其關(guān)聯(lián)的字符集的名稱開頭。如上圖的查詢結(jié)果的比較規(guī)則名稱都是以utf8開頭的。

  • 后邊緊跟著該比較規(guī)則主要作用于哪種語言，比如utf8_polish_ci表示以波蘭語的規(guī)則比較，utf8_spanish_ci是以西班牙語的規(guī)則比較，utf8_general_ci是一種通用的比較規(guī)則。

  • 名稱后綴意味著該比較規(guī)則是否區(qū)分語言中的重音、大小寫啥的，具體可以用的值如下：

    后綴 英文釋義 描述
    _ai accent insensitive 不區(qū)分重音
    _as accent sensitive 區(qū)分重音
    _ci case insensitive 不區(qū)分大小寫
    _cs case sensitive 區(qū)分大小寫
    _bin binary 以二進制方式比較

    比如utf8_general_ci這個比較規(guī)則是以ci結(jié)尾的，說明不區(qū)分大小寫。

每種字符集對應(yīng)若干種比較規(guī)則，每種字符集都有一種默認的比較規(guī)則，SHOW COLLATION的返回結(jié)果中的Default列的值為YES的就是該字符集的默認比較規(guī)則，比方說utf8字符集默認的比較規(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 'character_set_server'; +----------------------+-------+ | Variable_name        | Value | +----------------------+-------+ | character_set_server | utf8  | +----------------------+-------+ 1 row in set (0.00 sec)  mysql> SHOW VARIABLES LIKE 'collation_server'; +------------------+-----------------+ | Variable_name    | Value           | +------------------+-----------------+ | collation_server | utf8_general_ci | +------------------+-----------------+ 1 row in set (0.00 sec)

可以看到在我的計算機中服務(wù)器級別默認的字符集是utf8，默認的比較規(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.01 sec)

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

系統(tǒng)變量 描述
character_set_database 當(dāng)前數(shù)據(jù)庫的字符集
collation_database 當(dāng)前數(shù)據(jù)庫的比較規(guī)則

我們來查看一下剛剛創(chuàng)建的charset_demo_db數(shù)據(jù)庫的字符集和比較規(guī)則：

mysql> USE charset_demo_db; Database changed  mysql> SHOW VARIABLES LIKE 'character_set_database'; +------------------------+--------+ | Variable_name          | Value  | +------------------------+--------+ | character_set_database | gb2312 | +------------------------+--------+ 1 row in set (0.00 sec)  mysql> SHOW VARIABLES LIKE 'collation_database'; +--------------------+-------------------+ | Variable_name      | Value             | +--------------------+-------------------+ | collation_database | gb2312_chinese_ci | +--------------------+-------------------+ 1 row in set (0.00 sec)  mysql>

可以看到這個charset_demo_db數(shù)據(jù)庫的字符集和比較規(guī)則就是我們在創(chuàng)建語句中指定的。需要注意的一點是：?character_set_database?和?collation_database?這兩個系統(tǒng)變量是只讀的，我們不能通過修改這兩個變量的值而改變當(dāng)前數(shù)據(jù)庫的字符集和比較規(guī)則。

數(shù)據(jù)庫的創(chuàng)建語句中也可以不指定字符集和比較規(guī)則，比如這樣：

CREATE DATABASE 數(shù)據(jù)庫名;

這樣的話將使用服務(wù)器級別的字符集和比較規(guī)則作為數(shù)據(jù)庫的字符集和比較規(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)建一個名為t的表，并指定這個表的字符集和比較規(guī)則：

mysql> CREATE TABLE t(     ->     col VARCHAR(10)     -> ) CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 0 rows affected (0.03 sec)

如果創(chuàng)建和修改表的語句中沒有指明字符集和比較規(guī)則，將使用該表所在數(shù)據(jù)庫的字符集和比較規(guī)則作為該表的字符集和比較規(guī)則。假設(shè)我們的創(chuàng)建表t的語句是這么寫的：

CREATE TABLE t(     col VARCHAR(10) );

因為表t的建表語句中并沒有明確指定字符集和比較規(guī)則，則表t的字符集和比較規(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ī)則名稱];

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

mysql> ALTER TABLE t MODIFY col VARCHAR(10) CHARACTER SET gbk COLLATE gbk_chinese_ci; Query OK, 0 rows affected (0.04 sec) Records: 0  Duplicates: 0  Warnings: 0  mysql>

對于某個列來說，如果在創(chuàng)建和修改的語句中沒有指明字符集和比較規(guī)則，將使用該列所在表的字符集和比較規(guī)則作為該列的字符集和比較規(guī)則。比方說表t的字符集是utf8，比較規(guī)則是utf8_general_ci，修改列col的語句是這么寫的：

ALTER TABLE t MODIFY col VARCHAR(10);

那列col的字符集和編碼將使用表t的字符集和比較規(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的比較規(guī)則。
  • 只修改比較規(guī)則，則字符集將變?yōu)樾薷暮蟮谋容^規(guī)則對應(yīng)的字符集。

不論哪個級別的字符集和比較規(guī)則，這兩條規(guī)則都適用，我們以服務(wù)器級別的字符集和比較規(guī)則為例來看一下詳細過程：

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

    mysql> SET character_set_server = gb2312; Query OK, 0 rows affected (0.00 sec)  mysql> SHOW VARIABLES LIKE 'character_set_server'; +----------------------+--------+ | Variable_name        | Value  | +----------------------+--------+ | character_set_server | gb2312 | +----------------------+--------+ 1 row in set (0.00 sec)  mysql>  SHOW VARIABLES LIKE 'collation_server'; +------------------+-------------------+ | Variable_name    | Value             | +------------------+-------------------+ | collation_server | gb2312_chinese_ci | +------------------+-------------------+ 1 row in set (0.00 sec)

    我們只修改了character_set_server的值為gb2312，collation_server的值自動變?yōu)榱薵b2312_chinese_ci。

  • 只修改比較規(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 'character_set_server'; +----------------------+-------+ | Variable_name        | Value | +----------------------+-------+ | character_set_server | utf8  | +----------------------+-------+ 1 row in set (0.00 sec)  mysql> SHOW VARIABLES LIKE 'collation_server'; +------------------+-----------------+ | Variable_name    | Value           | +------------------+-----------------+ | collation_server | utf8_general_ci | +------------------+-----------------+ 1 row in set (0.00 sec)  mysql>

    我們只修改了collation_server的值為utf8_general_ci，character_set_server的值自動變?yōu)榱藆tf8。

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

我們介紹的這4個級別字符集和比較規(guī)則的聯(lián)系如下：

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

知道了這些規(guī)則之后，對于給定的表，我們應(yīng)該知道它的各個列的字符集和比較規(guī)則是什么，從而根據(jù)這個列的類型來確定存儲數(shù)據(jù)時每個列的實際數(shù)據(jù)占用的存儲空間大小了。比方說我們向表t中插入一條記錄：

mysql> INSERT INTO t(col) VALUES('我我'); Query OK, 1 row affected (0.00 sec)  mysql> SELECT * FROM t; +--------+ | s      | +--------+ | 我我   | +--------+ 1 row in set (0.00 sec)

首先列col使用的字符集是gbk，一個字符’我’在gbk中的編碼為0xCED2，占用兩個字節(jié)，兩個字符的實際數(shù)據(jù)就占用4個字節(jié)。如果把該列的字符集修改為utf8的話，這兩個字符就實際占用6個字節(jié)啦～

客戶端和服務(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è)用iso-8859-1，也就是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)變量在我的計算機上的默認值如下（不同操作系統(tǒng)的默認值可能不同）：

mysql> SHOW VARIABLES LIKE 'character_set_client'; +----------------------+-------+ | Variable_name        | Value | +----------------------+-------+ | character_set_client | utf8  | +----------------------+-------+ 1 row in set (0.00 sec)  mysql> SHOW VARIABLES LIKE 'character_set_connection'; +--------------------------+-------+ | Variable_name            | Value | +--------------------------+-------+ | character_set_connection | utf8  | +--------------------------+-------+ 1 row in set (0.01 sec)  mysql> SHOW VARIABLES LIKE 'character_set_results'; +-----------------------+-------+ | Variable_name         | Value | +-----------------------+-------+ | character_set_results | utf8  | +-----------------------+-------+ 1 row in set (0.00 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。現(xiàn)在假設(shè)我們客戶端發(fā)送的請求是下邊這個字符串：

SELECT * FROM t WHERE s = '我';

為了方便大家理解這個過程，我們只分析字符’我’在這個過程中字符集的轉(zhuǎn)換。

現(xiàn)在看一下在請求從發(fā)送到結(jié)果返回過程中字符集的變化：

  1. 客戶端發(fā)送請求所使用的字符集

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

    • 類Unix系統(tǒng)使用的是utf8
    • Windows使用的是gbk

    例如我在使用的macOS操作系統(tǒng)時，客戶端使用的就是utf8字符集。所以字符’我’在發(fā)送給服務(wù)器的請求中的字節(jié)形式就是：0xE68891

    小貼士：  如果你使用的是可視化工具，比如navicat之類的，這些工具可能會使用自定義的字符集來編碼發(fā)送到服務(wù)器的字符串，而不采用操作系統(tǒng)默認的字符集（所以在學(xué)習(xí)的時候還是盡量用黑框框哈）。

  2. 服務(wù)器接收到客戶端發(fā)送來的請求其實是一串二進制的字節(jié)，它會認為這串字節(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. 因為表t的列col采用的是gbk字符集，與character_set_connection一致，所以直接到列中找字節(jié)值為0xCED2的記錄，最后找到了一條記錄。

    小貼士：  如果某個列使用的字符集和character_set_connection代表的字符集不一致的話，還需要進行一次字符集轉(zhuǎn)換。

  4. 上一步驟找到的記錄中的col列其實是一個字節(jié)串0xCED2，col列是采用gbk進行編碼的，所以首先會將這個字節(jié)串使用gbk進行解碼，得到字符串’我’，然后再把這個字符串使用character_set_results代表的字符集，也就是utf8進行編碼，得到了新的字節(jié)串：0xE68891，然后發(fā)送給客戶端。

  5. 由于客戶端是用的字符集是utf8，所以可以順利的將0xE68891解釋成字符我，從而顯示到我們的顯示器上，所以我們?nèi)祟愐沧x懂了返回的結(jié)果。

如果你讀上邊的文字有點暈，可以參照這個圖來仔細分析一下這幾個步驟：

帶你搞懂MySQL字符集和比較規(guī)則

從這個分析中我們可以得出這么幾點需要注意的地方：

  • 服務(wù)器認為客戶端發(fā)送過來的請求是用character_set_client編碼的。

    假設(shè)你的客戶端采用的字符集和?character_set_client?不一樣的話，這就會出現(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_client'; +----------------------+-------+ | Variable_name        | Value | +----------------------+-------+ | character_set_client | utf8  | +----------------------+-------+ 1 row in set (0.00 sec)  mysql>  SHOW VARIABLES LIKE 'character_set_connection'; +--------------------------+-------+ | Variable_name            | Value | +--------------------------+-------+ | character_set_connection | utf8  | +--------------------------+-------+ 1 row in set (0.00 sec)  mysql> SHOW VARIABLES LIKE 'character_set_results'; +-----------------------+-------+ | Variable_name         | Value | +-----------------------+-------+ | character_set_results | utf8  | +-----------------------+-------+ 1 row in set (0.00 sec)  mysql>

另外，如果你想在啟動客戶端的時候就把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)用

結(jié)束了字符集的漫游，我們把視角再次聚焦到比較規(guī)則，比較規(guī)則的作用通常體現(xiàn)比較字符串大小的表達式以及對某個字符串列進行排序中，所以有時候也稱為排序規(guī)則。比方說表t的列col使用的字符集是gbk，使用的比較規(guī)則是gbk_chinese_ci，我們向里邊插入幾條記錄：

mysql> INSERT INTO t(col) VALUES('a'), ('b'), ('A'), ('B'); Query OK, 4 rows affected (0.00 sec) Records: 4  Duplicates: 0  Warnings: 0  mysql>

我們查詢的時候按照t列排序一下：

mysql> SELECT * FROM t ORDER BY col; +------+ | col  | +------+ | a    | | A    | | b    | | B    | | 我   | +------+ 5 rows in set (0.00 sec)

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

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

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

mysql> SELECT * FROM t ORDER BY s; +------+ | s    | +------+ | A    | | B    | | a    | | b    | | 我   | +------+ 5 rows in set (0.00 sec)  mysql>

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

小貼士：  列`col`中各個字符在使用gbk字符集編碼后對應(yīng)的數(shù)字如下： 'A' -> 65 （十進制） 'B' -> 66 （十進制） 'a' -> 97 （十進制） 'b' -> 98 （十進制） '我' -> 25105 （十進制）

總結(jié)

  1. 字符集指的是某個字符范圍的編碼規(guī)則。

  2. 比較規(guī)則是針對某個字符集中的字符比較大小的一種規(guī)則。

  3. 在MySQL中，一個字符集可以有若干種比較規(guī)則，其中有一個默認的比較規(guī)則，一個比較規(guī)則必須對應(yīng)一個字符集。

  4. 查看MySQL中查看支持的字符集和比較規(guī)則的語句如下：

    SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式]; SHOW COLLATION [LIKE 匹配的模式];

  5. MySQL有四個級別的字符集和比較規(guī)則

  • 服務(wù)器級別

    character_set_server表示服務(wù)器級別的字符集，collation_server表示服務(wù)器級別的比較規(guī)則。

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

    創(chuàng)建和修改數(shù)據(jù)庫時可以指定字符集和比較規(guī)則：

    CREATE DATABASE 數(shù)據(jù)庫名     [[DEFAULT] CHARACTER SET 字符集名稱]     [[DEFAULT] COLLATE 比較規(guī)則名稱];  ALTER DATABASE 數(shù)據(jù)庫名     [[DEFAULT] CHARACTER SET 字符集名稱]     [[DEFAULT] COLLATE 比較規(guī)則名稱];

    character_set_database表示當(dāng)前數(shù)據(jù)庫的字符集，collation_database表示當(dāng)前默認數(shù)據(jù)庫的比較規(guī)則，這兩個系統(tǒng)變量是只讀的，不能修改。如果沒有指定當(dāng)前默認數(shù)據(jù)庫，則變量與相應(yīng)的服務(wù)器級系統(tǒng)變量具有相同的值。

  • 表級別

    創(chuàng)建和修改表的時候指定表的字符集和比較規(guī)則：

    CREATE TABLE 表名 (列的信息)     [[DEFAULT] CHARACTER SET 字符集名稱]     [COLLATE 比較規(guī)則名稱]];  ALTER TABLE 表名     [[DEFAULT] CHARACTER SET 字符集名稱]     [COLLATE 比較規(guī)則名稱];

  • 列級別

    創(chuàng)建和修改列定義的時候可以指定該列的字符集和比較規(guī)則：

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

  1. 從發(fā)送請求到接收結(jié)果過程中發(fā)生的字符集轉(zhuǎn)換：

    • 客戶端使用操作系統(tǒng)的字符集編碼請求字符串，向服務(wù)器發(fā)送的是經(jīng)過編碼的一個字節(jié)串。

    • 服務(wù)器將客戶端發(fā)送來的字節(jié)串采用character_set_client代表的字符集進行解碼，將解碼后的字符串再按照character_set_connection代表的字符集進行編碼。

    • 如果character_set_connection代表的字符集和具體操作的列使用的字符集一致，則直接進行相應(yīng)操作，否則的話需要將請求中的字符串從character_set_connection代表的字符集轉(zhuǎn)換為具體操作的列使用的字符集之后再進行操作。

    • 將從某個列獲取到的字節(jié)串從該列使用的字符集轉(zhuǎn)換為character_set_results代表的字符集后發(fā)送到客戶端。

    • 客戶端使用操作系統(tǒng)的字符集解析收到的結(jié)果集字節(jié)串。

    在這個過程中各個系統(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ù)時使用的字符集

    一般情況下要使用保持這三個變量的值和客戶端使用的字符集相同。

  2. 比較規(guī)則的作用通常體現(xiàn)比較字符串大小的表達式以及對某個字符串列進行排序中。

推薦學(xué)習(xí)：mysql視頻教程

? 版權(quán)聲明
THE END
數(shù)據(jù)庫
# mysql
喜歡就支持一下吧
點贊6 分享