關(guān)于GreatSQL字符集的總結(jié)
前言
最近的SQL優(yōu)化工作中經(jīng)常遇到因字符集或校驗(yàn)規(guī)則不一致導(dǎo)致索引使用不了的問題疆瑰，修改表的字符集或校驗(yàn)規(guī)則相當(dāng)于把表重構(gòu)，表中數(shù)據(jù)量大時(shí)膨报，處理起來費(fèi)時(shí)費(fèi)力此蜈，希望應(yīng)用開發(fā)者在設(shè)計(jì)之初時(shí)注意到此問題，讓后期接手運(yùn)維的小伙伴少一些負(fù)擔(dān)袄秩。GreatSQL的字符集和校驗(yàn)規(guī)則種類繁多阵翎，提供靈活性的同時(shí)，也帶來使用混亂的煩惱之剧。本文對(duì)字符集做一個(gè)總結(jié)郭卫，讓讀者對(duì)GreatSQL的字符集有一個(gè)全面的了解。
一背稼、字符集介紹
計(jì)算機(jī)存儲(chǔ)的都是二進(jìn)制數(shù)據(jù)贰军，十進(jìn)制數(shù)字可以轉(zhuǎn)換為二進(jìn)制，那么字符串要轉(zhuǎn)換為二進(jìn)制數(shù)據(jù)就需要一個(gè)映射關(guān)系蟹肘，字符轉(zhuǎn)換為二進(jìn)制叫編碼词疼，二進(jìn)制轉(zhuǎn)換為字符叫解碼俯树，這個(gè)其實(shí)就是字符集的概念，描述某個(gè)字符范圍的編碼規(guī)則贰盗，不同的字符集包含的字符范圍不一樣许饿。
下面介紹一些重要的字符集。
1. ASCII字符集
共收錄128個(gè)字符童太，包括空格米辐、標(biāo)點(diǎn)符號(hào)、數(shù)字书释、大小寫字母和一些不可見字符翘贮。不可見字符主要指控制字符（比如換行、回車等）和通信字符（比如文頭SOH爆惧、文尾EOT等）等狸页。ASCII字符集總共128個(gè)字符，可以使用1個(gè)字節(jié)來進(jìn)行編碼扯再。
2. ISO8859-1字符集
共收錄256個(gè)字符芍耘，是在ASCII字符集的基礎(chǔ)上又?jǐn)U充了128個(gè)西歐常用字符（包括德法兩國(guó)的字母）。ISO8859-1字符集也可以使用1個(gè)字節(jié)來進(jìn)行編碼熄阻。這個(gè)字符集還有一個(gè)別名Latin1斋竞。
3. GB2312字符集
收錄了漢字以及拉丁字母、希臘字母秃殉、日文平假名及片假名字母坝初、俄語西里爾字母。收錄漢字6763個(gè)钾军，收錄其他文字符號(hào)682個(gè)鳄袍。這種字符集同時(shí)兼容ASCII字符集，所以如果字符在ASCII字符集中則采用1字節(jié)編碼吏恭，否則采用2字節(jié)編碼拗小。
4. GBK字符集
GBK對(duì)GB2312字符集進(jìn)行了擴(kuò)充，編碼方式兼容GB2312字符集樱哼。同GB2312字符集哀九，如果字符在ASCII字符集中則采用一字節(jié)編碼，否則采用2字節(jié)編碼唇礁。
5. UTF-8字符集
幾乎收錄了當(dāng)今世界各個(gè)國(guó)家地區(qū)使用的字符勾栗，而且還在不斷擴(kuò)充。這種字符集兼容ASCII字符集盏筐，采用變長(zhǎng)編碼方式围俘，編碼一個(gè)字符需要使用1~4個(gè)字節(jié)。通常一個(gè)漢字是3個(gè)字節(jié)編碼，一個(gè)字母是一個(gè)字節(jié)編碼界牡。UTF-8是Unicode的一種編碼方案簿寂，此外還有UTF-16、UTF-32編碼方案宿亡。
我們可以看出常遂，ISO8859-1、GB2312挽荠、GBK克胳、UTF-8字符集都包含了ASCIIS字符集，GBK包含了GB2312字符集, UTF-8收錄的字符涵蓋GBK圈匆、GB2312漠另、ISO8859-1的字符，但是它們編碼規(guī)則是不一樣的跃赚。
比如漢字“我”的編碼方式：
GBK中的編碼：1100111011010010
UTF-8的編碼：111001101000100010010001
二笆搓、GreatSQL字符集與比較規(guī)則
1.查看GreatSQL支持的字符集
通過語句show (character set|charset) [like 匹配的模式] 來查看支持的字符集,數(shù)據(jù)來源于表information_schema.character_sets。
這其中character set與charset 是同義詞纬傲。
greatsql> show charset;
+----------+---------------------------------+---------------------+--------+
| 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 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)
上面為GreatSQL8.0的41種字符集满败， Default collation是指字符集默認(rèn)的比較規(guī)則，Maxlen指字符集最多需要幾個(gè)字節(jié)來表示一個(gè)字符叹括。
GreatSQL5.7版本里的utf8指的是utf8mb3,是“閹割”過的UTF-8字符集算墨，只使用1~3個(gè)字節(jié)表示字符。 utf8mb4是正宗的UTF-8字符集汁雷，使用1~4個(gè)字節(jié)表示字符米同，utf8mb4比utf8mb3多存儲(chǔ)一些字符，比如emoj表情等摔竿。Mysql已經(jīng)在很大程度上優(yōu)化了utf8mb4字符集性能，是默認(rèn)的字符集少孝。
2.查看GreatSQL比較規(guī)則
可以通過語句show collation [like 匹配的模式] 來查看支持的比較規(guī)則继低，數(shù)據(jù)來源于表information_schema.collations
每種字符集都有若干種比較規(guī)則。查詢一下utf8mb4的比較規(guī)則稍走。
greatsql> show collation like '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 |
+----------------------------+---------+-----+---------+----------+---------+---------------+
89 rows in set (0.00 sec)
當(dāng)前查詢版本為GreatSQL8.0.32袁翁，這里utf8mb4比較規(guī)則有89種。Collation的命名規(guī)則也有規(guī)律婿脸，以字符集的名字開頭粱胜，后面有尾綴_ai,_as,_ci,_cs,_bin。這幾個(gè)尾綴的含義如下：
| 尾綴 | 英文含義 | 中文描述 |
|---|---|---|
| _ai | accent insensitive | 不區(qū)分重音 |
| _as | accent sensitive | 區(qū)分重音 |
| _ci | case insensitive | 不區(qū)分大小寫 |
| _cs | case sensitive | 區(qū)分大小寫 |
| _bin | binary | 以二進(jìn)制方式比較 |
utf8mb4字符集默認(rèn)的校驗(yàn)規(guī)則是utf8mb4_0900_ai_ci,這個(gè)0900表示基于Unicode Collation Algorithm（UCA)9.0.0的排序規(guī)則狐树。
校對(duì)規(guī)則都有一個(gè)Pad_attribute屬性焙压，取值為 PAD SPACE或NO PAD，PAD SPACE的校對(duì)規(guī)則會(huì)忽略結(jié)尾的空格字符，NO PAD相反涯曲。
舉例說明：從上面的查詢中我們可以看到utf8mb4_0900_bin 的Pad_attribute為NO PAD野哭，表示不忽略結(jié)尾的空格。utf8mb4_bin的Pad_attribute為PAD SPACE幻件，表示會(huì)忽略結(jié)尾的空格拨黔。
greatsql> create table t1(id int ,c1 varchar(20)) character set utf8mb4 collate utf8mb4_0900_bin;
Query OK, 0 rows affected (0.02 sec)
greatsql> insert into t1 values(1,'abc ');
Query OK, 1 row affected (0.00 sec)
greatsql> select * from t1 where c1='abc';
Empty set (0.00 sec)
greatsql> select * from t1 where c1='abc ';
+------+------+
| id | c1 |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
--c1列校驗(yàn)規(guī)則為utf8mb4_0900_bin，插入時(shí)尾部帶一個(gè)空格绰沥，查詢時(shí)需要嚴(yán)格匹配空格字符才能查到數(shù)據(jù)篱蝇。
下面修改c1校驗(yàn)規(guī)則為utf8mb4_bin,查詢條件中帶不帶空格都能查詢到數(shù)據(jù)。
greatsql> alter table t1 modify c1 varchar(20) character set utf8mb4 collate utf8mb4_bin;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
greatsql> select * from t1 where c1='abc';
+------+------+
| id | c1 |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
greatsql> select * from t1 where c1='abc ';
+------+------+
| id | c1 |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
3.字符集和比較規(guī)則的使用級(jí)別徽曲。
GreatSQL字符集與比較規(guī)則使用級(jí)別零截，分別為服務(wù)器級(jí)別、數(shù)據(jù)庫級(jí)別疟位、表級(jí)別瞻润、列級(jí)別。
(1)服務(wù)器級(jí)別（server）：
提供兩個(gè)系統(tǒng)變量來表示甜刻。character_set_server與collation_server绍撞。啟動(dòng)服務(wù)器程序時(shí)通過啟動(dòng)選項(xiàng)或程序運(yùn)行中通過set 語句進(jìn)行修改這兩個(gè)變量。比如配置文件中配置：
[server]
character_set_server=utf8mb4
collation_server=utf8mb4_bin
（2）數(shù)據(jù)庫級(jí)別（database）：
提供兩個(gè)系統(tǒng)變量來表示得院。character_set_database與collation_database, 這兩個(gè)變量不能直接修改的傻铣，只能通過創(chuàng)建庫或修改庫語句來修改。創(chuàng)建庫語句不指定字符集將使用服務(wù)器級(jí)別的設(shè)置祥绞。
對(duì)應(yīng)的語句：
create/alter database 數(shù)據(jù)庫名 [character set 字符集名稱][collate 比較規(guī)則名稱] ;
例如：
create database test character set utf8mb4 collate utf8mb4_0900_bin;
alter database test character set utf8mb4 collate utf8mb4_0900_ai_ci;
| 級(jí)別 | 用法 |
|---|---|
| 數(shù)據(jù)庫級(jí)別(database) | |
| 表級(jí)別 | create table table_name(列信息)[character set 字符集名稱][collate 比較規(guī)則名稱];alter table table_name[character set 字符集名稱][collate 比較規(guī)則名稱]; |
| 列級(jí)別 | create table table_name(列名 字符串類型 [character set 字符集名稱] [collate 比較規(guī)則名稱]非洲，其他列...);alter table table_name modify 列名 字符串類型 [character set 字符集名稱] [collate 比較規(guī)則名稱];修改列信息時(shí)注意：修改列時(shí)不指定字符集，即使創(chuàng)建時(shí)指定了也會(huì)使用表的字符集和比較規(guī)則蜕径。如果修改后的列字符集不能表示列中存儲(chǔ)的數(shù)據(jù)两踏，則會(huì)報(bào)錯(cuò)。 |
(3)表級(jí)別：
創(chuàng)建和修改表時(shí)指定兜喻。同一個(gè)database里的不同表可以使用不同字符集梦染。創(chuàng)建表的語句中沒有指明字符集和比較規(guī)則，則使用該表所在數(shù)據(jù)庫的字符集與比較規(guī)則朴皆。
對(duì)應(yīng)的語句：
create table table_name(列信息)
[character set 字符集名稱][collate 比較規(guī)則名稱];
alter table table_name
[character set 字符集名稱][collate 比較規(guī)則名稱];
例如：
create table t1(id int,c1 varchar(30)) character set utf8mb4 collate utf8mb4_0900_bin;
alter table t1 character set utf8 collate utf8mb4_0900_ai_ci;
這里注意一下帕识，字符集和比較規(guī)則是相互關(guān)聯(lián)的，如果在修改時(shí)僅指定了字符集遂铡，那么比較規(guī)則也會(huì)隨之變?yōu)樾薷暮蟮淖址J(rèn)的比較規(guī)則肮疗。如果僅指定了比較規(guī)則，那么字符集也會(huì)變?yōu)楸容^規(guī)則對(duì)應(yīng)的字符集扒接。這么說來的話伪货，只需要指定比較規(guī)則就夠了们衙。
修改表的默認(rèn)字符集只對(duì)后面添加的列有效，已存在的列的校驗(yàn)規(guī)則保持不變超歌。
(4)列級(jí)別：
對(duì)于存儲(chǔ)字符串的列砍艾，同一個(gè)表中不同列可以有不同的字符集和比較規(guī)則∥【伲可以在創(chuàng)建和修改列信息時(shí)指定該列的字符集與比較規(guī)則脆荷。如果創(chuàng)建和修改列時(shí)不指定，則使用該表的字符集與比較規(guī)則懊悯。
對(duì)應(yīng)的語句：
create table table_name(列名 字符串類型 [character set 字符集名稱] [collate 比較規(guī)則名稱]蜓谋，其他列...);
alter table table_name modify 列名 字符串類型 [character set 字符集名稱] [collate 比較規(guī)則名稱];
修改列信息時(shí)注意：修改列時(shí)不指定字符集，即使創(chuàng)建時(shí)指定了字符集炭分，也會(huì)使用表的字符集和比較規(guī)則桃焕。如果修改后的列字符集不能表示列中存儲(chǔ)的數(shù)據(jù)，則會(huì)報(bào)錯(cuò)捧毛。
4.客戶端和服務(wù)器端通信過程中使用的字符集观堂。
字符串在計(jì)算機(jī)中就是一個(gè)二進(jìn)制字節(jié)序列，如果編碼和解碼使用不同的字符集呀忧，那最后得到的結(jié)果肯定是你不認(rèn)識(shí)的亂碼师痕。
如果客戶端和服務(wù)器端使用的字符集是不同的，那么就會(huì)涉及一個(gè)字符集轉(zhuǎn)換的過程而账。從用戶角度看胰坟，客戶端發(fā)送請(qǐng)求以及服務(wù)器返回的響應(yīng)都是字符串。從機(jī)器的角度看泞辐，客戶端發(fā)送的請(qǐng)求和服務(wù)器返回的響應(yīng)本質(zhì)上就是一個(gè)字節(jié)序列笔横，在這個(gè)“客戶端發(fā)送請(qǐng)求、服務(wù)器返回響應(yīng)”的過程中咐吼，其中經(jīng)歷了多次字符集轉(zhuǎn)換吹缔。
我們以linux系統(tǒng)為例來說明一下這個(gè)過程。
a. 客戶端發(fā)送請(qǐng)求锯茄。
一般情況下涛菠，客戶端編碼請(qǐng)求字符串時(shí)使用的字符集與操作系統(tǒng)當(dāng)前使用的字符集一致。Linux系統(tǒng)的LANG環(huán)境變量決定了操作系統(tǒng)當(dāng)前使用的哪種字符集撇吞。
[root@greatdb-1 ~]# echo $LANG
en_US.UTF-8
如果啟動(dòng)客戶端程序時(shí) 指定了選項(xiàng) default-character-set,則客戶端使用此選項(xiàng)指定的字符集。
b. 服務(wù)器接收請(qǐng)求
從本質(zhì)上說服務(wù)器接收到的請(qǐng)求就是一個(gè)二進(jìn)制字節(jié)序列礁叔。服務(wù)器怎么對(duì)這個(gè)字節(jié)序列進(jìn)行解碼牍颈，或者說服務(wù)器解碼使用什么樣的字符集，這取決于系統(tǒng)變量character_set_client琅关，此變量為session級(jí)別的煮岁〖ケ危客戶端編碼實(shí)際使用的是什么字符集，服務(wù)器不知道画机，服務(wù)器就以系統(tǒng)變量chacter_set_client設(shè)置的字符集進(jìn)行解碼接收到的請(qǐng)求冶伞。一般情況下我們要盡量保證這兩個(gè)字符集是一致的，否則就會(huì)出現(xiàn)雞同鴨講的事情步氏。
c. 服務(wù)器處理請(qǐng)求
服務(wù)器真正處理請(qǐng)求時(shí)响禽，又會(huì)將請(qǐng)求的字節(jié)序列轉(zhuǎn)換為系統(tǒng)變量character_set_connection對(duì)應(yīng)的字符集進(jìn)行編碼的字節(jié)序列,同時(shí)還有配套的系統(tǒng)變量collation_connection來表示這些字符串應(yīng)該使用哪種比較規(guī)則。那么為什么還要做這種轉(zhuǎn)換呢荚醒，我認(rèn)為這樣設(shè)計(jì)充分體現(xiàn)了它的靈活性芋类。以下面的例子為例：
客戶端發(fā)出請(qǐng)求：select 'a'='A'; 這個(gè)應(yīng)該返回true還是false呢？
greatsql> show variables like '%colla%';
+-------------------------------+--------------------+
| Variable_name | Value |
+-------------------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)
greatsql> select 'a'='A';
+---------+
| 'a'='A' |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
greatsql> set collation_connection=utf8mb4_0900_bin;
Query OK, 0 rows affected (0.00 sec)
greatsql> show variables like '%colla%';
+-------------------------------+--------------------+
| Variable_name | Value |
+-------------------------------+--------------------+
| collation_connection | utf8mb4_0900_bin |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)
greatsql> select 'a'='A';
+---------+
| 'a'='A' |
+---------+
| 0 |
+---------+
1 row in set (0.00 sec)
從上面的例子可以看出睬辐，開始比較規(guī)則collation_connection='utf8mb4_0900_ai_ci'谒撼，對(duì)大小寫字母不敏感组贺，'a'='A'是返回true的，修改比較規(guī)則collation_connection='utf8mb4_0900_bin'后贮竟，按二進(jìn)制比較，'a'='A'是返回false的较剃。
那如果傳遞一個(gè)字面量與表中列的存儲(chǔ)值進(jìn)行比較呢咕别？
greatsql> show variables like 'collation_connection';
+----------------------+------------------+
| Variable_name | Value |
+----------------------+------------------+
| collation_connection | utf8mb4_0900_bin |
+----------------------+------------------+
1 row in set (0.01 sec)
greatsql> create table t0(name varchar(20) character set utf8mb4 collate utf8mb4_0900_ai_ci);
Query OK, 0 rows affected (0.02 sec)
greatsql> insert into t0 values('a');
Query OK, 1 row affected (0.02 sec)
greatsql> insert into t0 values('A');
Query OK, 1 row affected (0.01 sec)
greatsql> select * from t0 where name='A';
+------+
| name |
+------+
| a |
| A |
+------+
2 rows in set (0.00 sec)
從上面的例子可以看出列的字符集與比較規(guī)則比系統(tǒng)變量collation_connection的優(yōu)先級(jí)要高，比較時(shí)會(huì)把請(qǐng)求中的字符串轉(zhuǎn)換為列的字符集重付，按照列的比較規(guī)則去比較顷级。
d. 服務(wù)器生成響應(yīng)。
服務(wù)器以什么樣的字符集編碼的字節(jié)序列發(fā)送到客戶端呢确垫，這取決于系統(tǒng)變量character_set_results的值弓颈。還以上面建的t0表為例。
greatsql> insert into t0 values('我');
Query OK, 1 row affected (0.01 sec)
greatsql> select * from t0;
+------+
| name |
+------+
| a |
| A |
| 我 |
+------+
3 rows in set (0.00 sec)
greatsql> show variables like 'character_set_result';
Empty set (0.00 sec)
greatsql> show variables like 'character_set_results';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| character_set_results | utf8mb4 |
+-----------------------+---------+
1 row in set (0.00 sec)
greatsql> set character_set_results=ascii;
Query OK, 0 rows affected (0.00 sec)
greatsql> select * from t0;
+------+
| name |
+------+
| a |
| A |
| ? |
+------+
3 rows in set (0.00 sec)
greatsql> select * from t0 where name='我';
+------+
| name |
+------+
| ? |
+------+
1 row in set (0.00 sec)
從上面的例子可以看出删掀，開始character_set_results設(shè)置的utf8mb4,可以正常顯示漢字”我”,當(dāng)修改為ascii字符集時(shí)翔冀，漢字以？代替披泪，ascii字符集不包含漢字纤子，無法表示漢字。就是說服務(wù)器生成響應(yīng)的會(huì)轉(zhuǎn)換為character_set_results設(shè)定的字符集的字節(jié)序列發(fā)送到客戶端款票。
e. 客戶端接收響應(yīng)
客戶端接收到的響應(yīng)也是一個(gè)字節(jié)序列控硼，再將這個(gè)字節(jié)序列按照操作系統(tǒng)當(dāng)前使用的字符集來解釋這個(gè)字節(jié)序列，如果指定了啟動(dòng)選項(xiàng)default-character-set艾少，則以這個(gè)選項(xiàng)的設(shè)置解碼收到的字節(jié)序列卡乾，顯示成人類能看懂的字符。
通過上面介紹的過程缚够，我們可以看出在客戶端與服務(wù)器的通信過程中是經(jīng)過了多次字符集轉(zhuǎn)換的幔妨，我們以一個(gè)表格來總結(jié)一下這個(gè)轉(zhuǎn)換過程鹦赎。
| 客戶端發(fā)送請(qǐng)求 | 字符串按照操作系統(tǒng)當(dāng)前使用的字符集或客戶端程序啟動(dòng)選項(xiàng)default-character-set指定的字符集進(jìn)行編碼 |
|---|---|
| 服務(wù)器接收請(qǐng)求 | 服務(wù)器認(rèn)為請(qǐng)求是按照character_set_client變量設(shè)置的字符集編碼的，因此按照這個(gè)變量設(shè)置的字符集進(jìn)行解碼误堡。 |
| 服務(wù)器處理請(qǐng)求 | 把請(qǐng)求字節(jié)序列從character_set_client字符集轉(zhuǎn)換為character_set_connection字符集古话，配套collation_connection作為比較規(guī)則進(jìn)行處理。處理數(shù)據(jù)庫列中數(shù)據(jù)時(shí)锁施，以列的字符集和比較規(guī)則進(jìn)行處理陪踩，如果character_set_connection與之不一致，將會(huì)再被轉(zhuǎn)換為列的字符集沾谜。 |
| 服務(wù)器生成響應(yīng) | 服務(wù)器采用character_set_results設(shè)置的字符集編碼的字節(jié)序列發(fā)送給客戶端膊毁。 |
| 客戶端接收響應(yīng) | 客戶端收到的響應(yīng)字節(jié)序列，再按照操作系統(tǒng)當(dāng)前使用的字符集或客戶端程序啟動(dòng)選項(xiàng)default-character-set指定的字符集進(jìn)行解釋基跑，顯示成人類能看懂的字符婚温。 |
注意這3個(gè)系統(tǒng)變量character_set_client、character_set_connection媳否、character_set_results栅螟，都是session級(jí)別的系統(tǒng)變量。在連接服務(wù)器時(shí)篱竭，客戶端將默認(rèn)的字符集信息與用戶名力图、密碼等信息一起發(fā)送給服務(wù)器，服務(wù)器收到后會(huì)將這3個(gè)系統(tǒng)變量初始化為客戶端的默認(rèn)字符集掺逼。這幾個(gè)變量可以使用set 語句進(jìn)行修改吃媒，但無特殊需要也沒有必要去修改。要注意吕喘，使用set語句修改這三個(gè)系統(tǒng)變量赘那，并不會(huì)改變客戶端在編碼請(qǐng)求字符串時(shí)使用的字符集，也不會(huì)修改客戶端默認(rèn)的字符集氯质。
使用總結(jié)
- GreatSQL 8.0版本募舟，character_set_server默認(rèn)為utf8mb4，collation_server默認(rèn)為utf8mb4_0900_ai_ci闻察。這個(gè)是全局級(jí)別的默認(rèn)設(shè)置拱礁。創(chuàng)建database不指定字符集與校驗(yàn)規(guī)則，會(huì)使用character_set_server與collation_server參數(shù)指定的字符集與校驗(yàn)規(guī)則辕漂。注意一點(diǎn)如果指定character set而不指定 collate呢灶，則collate會(huì)取指定字符集的default collation，而不是collation_server指定的校驗(yàn)規(guī)則钉嘹。
- 創(chuàng)建表時(shí)不指定字符集與校驗(yàn)規(guī)則填抬，會(huì)使用所屬數(shù)據(jù)庫的字符集與校驗(yàn)規(guī)則，注意一點(diǎn)如果指定character set而不指定collate隧期，則collate會(huì)取指定字符集的default collation飒责，而不是數(shù)據(jù)庫的校驗(yàn)規(guī)則。
- 創(chuàng)建表時(shí)仆潮，列上不指定字符集與校驗(yàn)規(guī)則宏蛉，會(huì)使用表上指定的字符集與校驗(yàn)規(guī)則，表上也沒有指定性置，那就是使用所屬database的字符集與校驗(yàn)規(guī)則拾并。
- 切記如果只指定字符集，但是不指定校驗(yàn)規(guī)則鹏浅，校驗(yàn)規(guī)則會(huì)取指定字符集的default collation嗅义，有可能結(jié)果不符合預(yù)期。所以創(chuàng)建數(shù)據(jù)庫隐砸，數(shù)據(jù)表之碗，表上的列，最好不要只指定字符集季希⊥誓牵可以字符集與校驗(yàn)規(guī)則都省略，也可以只指定校驗(yàn)規(guī)則式塌。
- 一定要保證關(guān)聯(lián)表上關(guān)聯(lián)字段的字符集與校驗(yàn)規(guī)則保持一致博敬，才能很好的使用索引。
Enjoy GreatSQL :)
關(guān)于GreatSQL
GreatSQL數(shù)據(jù)庫是一款開源免費(fèi)數(shù)據(jù)庫峰尝，可在普通硬件上滿足金融級(jí)應(yīng)用場(chǎng)景偏窝，具有高可用、高性能武学、高兼容祭往、高安全等特性，可作為MySQL或Percona Server for MySQL的理想可選替換劳淆。
