utf8字符集下的比較規(guī)則-魔扣目錄

前言：

在MySQL中，比較常用的字符集是utf8和utf8mb4。這兩個字符集是類似的，utf8是utf8mb3的別名，所以之后在MySQL中提到utf8就意味著使用1~3個字節(jié)來表示一個字符，如果大家有使用4字節(jié)編碼一個字符的情況，比如存儲一些emoji表情啥的，需要使用utf8mb4。其實每個字符集下對應(yīng)著若干個比較規(guī)則（也可以翻譯為排序規(guī)則或校對規(guī)則，英文是COLLATE），同一字符集下，使用不同的比較規(guī)則會影響字符字段的比較和排序。本文以utf8為例，介紹下常用的幾個比較規(guī)則的不同。

1.utf8下比較規(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 |
+--------------------------+---------+-----+---------+----------+---------+

這些比較規(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ū)分大小寫每種字符集都有一種默認的比較規(guī)則，SHOW COLLATION的返回結(jié)果中的Default列的值為YES的就是該字符集的默認比較規(guī)則，比方說utf8字符集默認的比較規(guī)則就是utf8_general_ci。

比較規(guī)則可以作用于四個級別，分別是：服務(wù)器級別、數(shù)據(jù)庫級別、表級別、列級別。服務(wù)器級別的比較規(guī)則由collation_server參數(shù)控制，如果創(chuàng)建數(shù)據(jù)庫、表、列時沒有顯式的指定比較規(guī)則，則會繼承上一級的比較規(guī)則。下面給出創(chuàng)建及修改庫、表、列的比較規(guī)則的示例語句：

# 創(chuàng)建數(shù)據(jù)庫指定比較規(guī)則 修改數(shù)據(jù)庫的比較規(guī)則
CREATE DATABASE 數(shù)據(jù)庫名
    [[DEFAULT] CHARACTER SET 字符集名稱]
    [[DEFAULT] COLLATE 比較規(guī)則名稱];

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

# 創(chuàng)建表時指定比較規(guī)則 修改表的比較規(guī)則
CREATE TABLE 表名 (列的信息)
    [[DEFAULT] CHARACTER SET 字符集名稱]
    [COLLATE 比較規(guī)則名稱]]

ALTER TABLE 表名
    [[DEFAULT] CHARACTER SET 字符集名稱]
    [COLLATE 比較規(guī)則名稱]

# 創(chuàng)建時指定列的比較規(guī)則 修改列的比較規(guī)則
CREATE TABLE 表名(
    列名 字符串類型 [CHARACTER SET 字符集名稱] [COLLATE 比較規(guī)則名稱],
    其他列...
);

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

2.幾種比較規(guī)則對比

utf8字符集下默認的比較規(guī)則是utf8_general_ci，日常中會用到的有utf8_general_ci，utf8_unicode_ci，utf8_bin三種比較規(guī)則，其他比較規(guī)則基本很少會用，下面簡單了解下這三種比較規(guī)則的異同。

首先utf8_bin的比較方法其實就是直接將所有字符看作二進制串，然后從最高位往最低位比對。所以很顯然它是區(qū)分大小寫的。而utf8_general_ci和utf8_unicode_ci是不區(qū)分大小寫的。

utf8_general_ci和utf8_unicode_ci對中、英文來說沒有實質(zhì)的差別。utf8_unicode_ci的最主要的特色是支持擴展，即當把一個字母看作與其它字母組合相等時。例如，在德語和一些其它語言中‘ß'等于‘ss'。utf8_general_ci是一個遺留的比較規(guī)則，不支持擴展。它僅能夠在字符之間進行逐個比較。這意味著utf8_general_ci比較規(guī)則進行的比較速度很快，但是與utf8_unicode_ci相比，比較正確性較差。

下面我們來實踐下三種比較規(guī)則的異同：

# 創(chuàng)建表 指定列為不同的比較規(guī)則
mysql> create table utf8_test (
    -> col_general varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci,
    -> col_unicode varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci,
    -> col_bin varchar(20) CHARACTER SET utf8 COLLATE utf8_bin
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 # 插入數(shù)據(jù) 每行數(shù)據(jù)各個字段值一樣
 mysql> select * from utf8_test;
+-------------+-------------+---------+
| col_general | col_unicode | col_bin |
+-------------+-------------+---------+
| MySQL       | MySQL       | MySQL   |
| mysql       | mysql       | mysql   |
| A           | A           | A       |
| a           | a           | a       |
| aA          | aA          | aA      |
+-------------+-------------+---------+

# 查詢 驗證utf8_bin比較規(guī)則區(qū)分大小寫
mysql> select * from utf8_test where col_general='mysql';
+-------------+-------------+---------+
| col_general | col_unicode | col_bin |
+-------------+-------------+---------+
| MySQL       | MySQL       | MySQL   |
| mysql       | mysql       | mysql   |
+-------------+-------------+---------+
mysql> select * from utf8_test where col_unicode='MySQL';
+-------------+-------------+---------+
| col_general | col_unicode | col_bin |
+-------------+-------------+---------+
| MySQL       | MySQL       | MySQL   |
| mysql       | mysql       | mysql   |
+-------------+-------------+---------+
mysql> select * from utf8_test where col_bin='mysql';       
+-------------+-------------+---------+
| col_general | col_unicode | col_bin |
+-------------+-------------+---------+
| mysql       | mysql       | mysql   |
+-------------+-------------+---------+

# 排序 發(fā)現(xiàn)不同排序規(guī)則對順序有影響
mysql> select * from utf8_test order by col_general;
+-------------+-------------+---------+
| col_general | col_unicode | col_bin |
+-------------+-------------+---------+
| A           | A           | A       |
| a           | a           | a       |
| aA          | aA          | aA      |
| MySQL       | MySQL       | MySQL   |
| mysql       | mysql       | mysql   |
+-------------+-------------+---------+
mysql> select * from utf8_test order by col_bin;
+-------------+-------------+---------+
| col_general | col_unicode | col_bin |
+-------------+-------------+---------+
| A           | A           | A       |
| MySQL       | MySQL       | MySQL   |
| a           | a           | a       |
| aA          | aA          | aA      |
| mysql       | mysql       | mysql   |
+-------------+-------------+---------+

3.關(guān)于比較規(guī)則的選擇與建議

對于MySQL 5.7版本，一般情況下建議將字符集改為utf8，比較規(guī)則選擇默認的utf8_general_ci。utf8_general_ci相對于utf8_unicode_ci來說校對速度快，但是如果你的應(yīng)用有德語、法語或者俄語，建議使用utf8_unicode_ci。如果某個表或列字段要求區(qū)分大小寫，可以單獨指定該表或字段使用utf8_bin比較規(guī)則

最后以思維導(dǎo)圖的方式總結(jié)下本文的主要內(nèi)容：