關於GreatSQL字元集的總結

来源:https://www.cnblogs.com/greatsql/archive/2023/12/20/17915828.html
-Advertisement-
Play Games

關於GreatSQL字元集的總結 前言 最近的SQL優化工作中經常遇到因字元集或校驗規則不一致導致索引使用不了的問題，修改表的字元集或校驗規則相當於把表重構，表中數據量大時，處理起來費時費力，希望應用開發者在設計之初時註意到此問題，讓後期接手運維的小伙伴少一些負擔。GreatSQL的字元集和校驗規則 ...


關於GreatSQL字元集的總結

前言

最近的SQL優化工作中經常遇到因字元集或校驗規則不一致導致索引使用不了的問題，修改表的字元集或校驗規則相當於把表重構，表中數據量大時，處理起來費時費力，希望應用開發者在設計之初時註意到此問題，讓後期接手運維的小伙伴少一些負擔。GreatSQL的字元集和校驗規則種類繁多，提供靈活性的同時，也帶來使用混亂的煩惱。本文對字元集做一個總結，讓讀者對GreatSQL的字元集有一個全面的瞭解。

一、字元集介紹

電腦存儲的都是二進位數據，十進位數字可以轉換為二進位，那麼字元串要轉換為二進位數據就需要一個映射關係，字元轉換為二進位叫編碼，二進位轉換為字元叫解碼，這個其實就是字元集的概念，描述某個字元範圍的編碼規則，不同的字元集包含的字元範圍不一樣。

下麵介紹一些重要的字元集。

1. ASCII字元集

共收錄128個字元，包括空格、標點符號、數字、大小寫字母和一些不可見字元。不可見字元主要指控制字元（比如換行、回車等）和通信字元（比如文頭SOH、文尾EOT等）等。ASCII字元集總共128個字元，可以使用1個位元組來進行編碼。

2. ISO8859-1字元集

共收錄256個字元，是在ASCII字元集的基礎上又擴充了128個西歐常用字元（包括德法兩國的字母）。ISO8859-1字元集也可以使用1個位元組來進行編碼。這個字元集還有一個別名Latin1。

3. GB2312字元集

收錄了漢字以及拉丁字母、希臘字母、日文平假名及片假名字母、俄語西里爾字母。收錄漢字6763個，收錄其他文字元號682個。這種字元集同時相容ASCII字元集，所以如果字元在ASCII字元集中則採用1位元組編碼，否則採用2位元組編碼。

4. GBK字元集

GBK對GB2312字元集進行了擴充，編碼方式相容GB2312字元集。同GB2312字元集，如果字元在ASCII字元集中則採用一位元組編碼，否則採用2位元組編碼。

5. UTF-8字元集

幾乎收錄了當今世界各個國家地區使用的字元，而且還在不斷擴充。這種字元集相容ASCII字元集，採用變長編碼方式，編碼一個字元需要使用1~4個位元組。通常一個漢字是3個位元組編碼，一個字母是一個位元組編碼。UTF-8是Unicode的一種編碼方案，此外還有UTF-16、UTF-32編碼方案。

我們可以看出，ISO8859-1、GB2312、GBK、UTF-8字元集都包含了ASCIIS字元集，GBK包含了GB2312字元集, UTF-8收錄的字元涵蓋GBK、GB2312、ISO8859-1的字元，但是它們編碼規則是不一樣的。

比如漢字“我”的編碼方式：

GBK中的編碼：1100111011010010

UTF-8的編碼：111001101000100010010001

二、GreatSQL字元集與比較規則

1.查看GreatSQL支持的字元集

通過語句show (character set|charset) [like 匹配的模式] 來查看支持的字元集,數據來源於表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是指字元集預設的比較規則，Maxlen指字元集最多需要幾個位元組來表示一個字元。

GreatSQL5.7版本里的utf8指的是utf8mb3,是“閹割”過的UTF-8字元集，只使用1~3個位元組表示字元。 utf8mb4是正宗的UTF-8字元集，使用1~4個位元組表示字元，utf8mb4比utf8mb3多存儲一些字元，比如emoj表情等。Mysql已經在很大程度上優化了utf8mb4字元集性能，是預設的字元集。

2.查看GreatSQL比較規則

可以通過語句show collation [like 匹配的模式] 來查看支持的比較規則，數據來源於表information_schema.collations

每種字元集都有若幹種比較規則。查詢一下utf8mb4的比較規則。

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)

當前查詢版本為GreatSQL8.0.32，這裡utf8mb4比較規則有89種。Collation的命名規則也有規律，以字元集的名字開頭，後面有尾綴_ai,_as,_ci,_cs,_bin。這幾個尾綴的含義如下：

尾綴 英文含義 中文描述
_ai accent insensitive 不區分重音
_as accent sensitive 區分重音
_ci case insensitive 不區分大小寫
_cs case sensitive 區分大小寫
_bin binary 以二進位方式比較

utf8mb4字元集預設的校驗規則是utf8mb4_0900_ai_ci,這個0900表示基於Unicode Collation Algorithm（UCA)9.0.0的排序規則。

校對規則都有一個Pad_attribute屬性，取值為 PAD SPACE或NO PAD，PAD SPACE的校對規則會忽略結尾的空格字元，NO PAD相反。

舉例說明：從上面的查詢中我們可以看到utf8mb4_0900_bin 的Pad_attribute為NO PAD，表示不忽略結尾的空格。utf8mb4_bin的Pad_attribute為PAD SPACE，表示會忽略結尾的空格。

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列校驗規則為utf8mb4_0900_bin，插入時尾部帶一個空格，查詢時需要嚴格匹配空格字元才能查到數據。
下麵修改c1校驗規則為utf8mb4_bin,查詢條件中帶不帶空格都能查詢到數據。
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.字元集和比較規則的使用級別。

GreatSQL字元集與比較規則使用級別，分別為伺服器級別、資料庫級別、表級別、列級別。

(1)伺服器級別（server）：

提供兩個系統變數來表示。character_set_server與collation_server。啟動伺服器程式時通過啟動選項或程式運行中通過set 語句進行修改這兩個變數。比如配置文件中配置：

 [server] 
 character_set_server=utf8mb4  
 collation_server=utf8mb4_bin

（2）資料庫級別（database）：

提供兩個系統變數來表示。character_set_database與collation_database, 這兩個變數不能直接修改的，只能通過創建庫或修改庫語句來修改。創建庫語句不指定字元集將使用伺服器級別的設置。

對應的語句：

create/alter database 資料庫名 [character set 字元集名稱][collate 比較規則名稱] ;
例如：
create database test character set utf8mb4 collate utf8mb4_0900_bin;
alter database test character set utf8mb4 collate utf8mb4_0900_ai_ci;
級別 用法
資料庫級別(database)
表級別 create table table_name(列信息)[character set 字元集名稱][collate 比較規則名稱];alter table table_name[character set 字元集名稱][collate 比較規則名稱];
列級別 create table table_name(列名 字元串類型 [character set 字元集名稱] [collate 比較規則名稱]，其他列...);alter table table_name modify 列名 字元串類型 [character set 字元集名稱] [collate 比較規則名稱];修改列信息時註意：修改列時不指定字元集，即使創建時指定了也會使用表的字元集和比較規則。如果修改後的列字元集不能表示列中存儲的數據，則會報錯。

(3)表級別：

創建和修改表時指定。同一個database里的不同表可以使用不同字元集。創建表的語句中沒有指明字元集和比較規則，則使用該表所在資料庫的字元集與比較規則。

對應的語句：

create table table_name(列信息)
[character set 字元集名稱][collate 比較規則名稱];
alter table table_name
[character set 字元集名稱][collate 比較規則名稱];

例如：
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;

這裡註意一下，字元集和比較規則是相互關聯的，如果在修改時僅指定了字元集，那麼比較規則也會隨之變為修改後的字元集預設的比較規則。如果僅指定了比較規則，那麼字元集也會變為比較規則對應的字元集。這麼說來的話，只需要指定比較規則就夠了。

修改表的預設字元集只對後面添加的列有效，已存在的列的校驗規則保持不變。

(4)列級別：

對於存儲字元串的列，同一個表中不同列可以有不同的字元集和比較規則。可以在創建和修改列信息時指定該列的字元集與比較規則。如果創建和修改列時不指定，則使用該表的字元集與比較規則。

對應的語句：

create table table_name(列名 字元串類型 [character set 字元集名稱] [collate 比較規則名稱]，其他列...);
alter table table_name modify 列名 字元串類型 [character set 字元集名稱] [collate 比較規則名稱];

修改列信息時註意：修改列時不指定字元集，即使創建時指定了字元集，也會使用表的字元集和比較規則。如果修改後的列字元集不能表示列中存儲的數據，則會報錯。

4.客戶端和伺服器端通信過程中使用的字元集。

字元串在電腦中就是一個二進位位元組序列，如果編碼和解碼使用不同的字元集，那最後得到的結果肯定是你不認識的亂碼。

如果客戶端和伺服器端使用的字元集是不同的，那麼就會涉及一個字元集轉換的過程。從用戶角度看，客戶端發送請求以及伺服器返回的響應都是字元串。從機器的角度看，客戶端發送的請求和伺服器返回的響應本質上就是一個位元組序列，在這個“客戶端發送請求、伺服器返迴響應”的過程中，其中經歷了多次字元集轉換。

我們以linux系統為例來說明一下這個過程。

a. 客戶端發送請求。

一般情況下，客戶端編碼請求字元串時使用的字元集與操作系統當前使用的字元集一致。Linux系統的LANG環境變數決定了操作系統當前使用的哪種字元集。

[root@greatdb-1 ~]# echo $LANG

en_US.UTF-8

如果啟動客戶端程式時 指定了選項 default-character-set,則客戶端使用此選項指定的字元集。

b. 伺服器接收請求

從本質上說伺服器接收到的請求就是一個二進位位元組序列。伺服器怎麼對這個位元組序列進行解碼，或者說伺服器解碼使用什麼樣的字元集，這取決於系統變數character_set_client，此變數為session級別的。客戶端編碼實際使用的是什麼字元集，伺服器不知道，伺服器就以系統變數chacter_set_client設置的字元集進行解碼接收到的請求。一般情況下我們要儘量保證這兩個字元集是一致的，否則就會出現雞同鴨講的事情。

c. 伺服器處理請求

伺服器真正處理請求時，又會將請求的位元組序列轉換為系統變數character_set_connection對應的字元集進行編碼的位元組序列,同時還有配套的系統變數collation_connection來表示這些字元串應該使用哪種比較規則。那麼為什麼還要做這種轉換呢，我認為這樣設計充分體現了它的靈活性。以下麵的例子為例：

客戶端發出請求：select 'a'='A'; 這個應該返回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)

從上面的例子可以看出，開始比較規則collation_connection='utf8mb4_0900_ai_ci'，對大小寫字母不敏感，'a'='A'是返回true的，修改比較規則collation_connection='utf8mb4_0900_bin'後，按二進位比較，'a'='A'是返回false的。

那如果傳遞一個字面量與表中列的存儲值進行比較呢？

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)

從上面的例子可以看出列的字元集與比較規則比系統變數collation_connection的優先順序要高，比較時會把請求中的字元串轉換為列的字元集，按照列的比較規則去比較。

d. 伺服器生成響應。

伺服器以什麼樣的字元集編碼的位元組序列發送到客戶端呢，這取決於系統變數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設置的utf8mb4,可以正常顯示漢字”我”,當修改為ascii字元集時，漢字以？代替，ascii字元集不包含漢字，無法表示漢字。就是說伺服器生成響應的會轉換為character_set_results設定的字元集的位元組序列發送到客戶端。

e. 客戶端接收響應

客戶端接收到的響應也是一個位元組序列，再將這個位元組序列按照操作系統當前使用的字元集來解釋這個位元組序列，如果指定了啟動選項default-character-set，則以這個選項的設置解碼收到的位元組序列，顯示成人類能看懂的字元。

通過上面介紹的過程，我們可以看出在客戶端與伺服器的通信過程中是經過了多次字元集轉換的，我們以一個表格來總結一下這個轉換過程。

客戶端發送請求 字元串按照操作系統當前使用的字元集或客戶端程式啟動選項default-character-set指定的字元集進行編碼
伺服器接收請求 伺服器認為請求是按照character_set_client變數設置的字元集編碼的，因此按照這個變數設置的字元集進行解碼。
伺服器處理請求 把請求位元組序列從character_set_client字元集轉換為character_set_connection字元集，配套collation_connection作為比較規則進行處理。處理資料庫列中數據時，以列的字元集和比較規則進行處理，如果character_set_connection與之不一致，將會再被轉換為列的字元集。
伺服器生成響應 伺服器採用character_set_results設置的字元集編碼的位元組序列發送給客戶端。
客戶端接收響應 客戶端收到的響應位元組序列，再按照操作系統當前使用的字元集或客戶端程式啟動選項default-character-set指定的字元集進行解釋，顯示成人類能看懂的字元。

註意這3個系統變數character_set_client、character_set_connection、character_set_results，都是session級別的系統變數。在連接伺服器時，客戶端將預設的字元集信息與用戶名、密碼等信息一起發送給伺服器，伺服器收到後會將這3個系統變數初始化為客戶端的預設字元集。這幾個變數可以使用set 語句進行修改，但無特殊需要也沒有必要去修改。要註意，使用set語句修改這三個系統變數，並不會改變客戶端在編碼請求字元串時使用的字元集，也不會修改客戶端預設的字元集。

使用總結

  1. GreatSQL 8.0版本，character_set_server預設為utf8mb4，collation_server預設為utf8mb4_0900_ai_ci。這個是全局級別的預設設置。創建database不指定字元集與校驗規則，會使用character_set_server與collation_server參數指定的字元集與校驗規則。註意一點如果指定character set而不指定 collate，則collate會取指定字元集的default collation，而不是collation_server指定的校驗規則。
  2. 創建表時不指定字元集與校驗規則，會使用所屬資料庫的字元集與校驗規則，註意一點如果指定character set而不指定collate，則collate會取指定字元集的default collation，而不是資料庫的校驗規則。
  3. 創建表時，列上不指定字元集與校驗規則，會使用表上指定的字元集與校驗規則，表上也沒有指定，那就是使用所屬database的字元集與校驗規則。
  4. 切記如果只指定字元集，但是不指定校驗規則，校驗規則會取指定字元集的default collation，有可能結果不符合預期。所以創建資料庫，數據表，表上的列，最好不要只指定字元集。可以字元集與校驗規則都省略，也可以只指定校驗規則。
  5. 一定要保證關聯表上關聯欄位的字元集與校驗規則保持一致，才能很好的使用索引。

Enjoy GreatSQL

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 第十一單元 事務與鎖
    create database step2_unit13; go use step2_unit13; go -- 創建數據表 CREATE TABLE account ( id INT PRIMARY KEY identity, NAME VARCHAR(10), balance decimal(1 ...
  • 精準掌握.NET依賴註入：DI自動註冊服務輕鬆搞定
    概述：.NET依賴註入（DI）通過反射自動註冊服務，示例展示了註冊指定類、帶特性類、項目下所有介面實現的類。簡化配置，提高可維護性。 在.NET中，進行依賴註入（DI）的自動註冊，可以通過反射機制和程式集掃描來實現。以下是詳細的步驟以及相應的C#源代碼示例，包括註冊指定類、註冊帶有自定義特性的類、以 ...
  • Qt程式接收串口數據存在延遲解決辦法
    問題 在調試接收串口數據的Qt程式中發現，數據存在延遲和粘包現象。下位機發送數據包頻率是100Hz，一包56位元組，波特率115200，在列印port->readAll()的值的時候發現並不是每10ms讀到一包數據，而是大概每50ms左右一次接收到5包數據，在其他電腦上調試，以及下載其他串口助手調試後 ...
  • Flink處理函數解析（ProcessFunction和KeyedProcessFunction）
    Flink中的處理函數（ProcessFunction和KeyedProcessFunction）在對於數據進行顆粒化的精確計算時使用較多，處理函數提供了一個定時服務（TimerService），可以向未來註冊一個定時服務， ...
  • MySQL運維13-Mycat分庫分表之按月分片
    一、按照月分片 使用場景為按照自然月來分片，每個自然月為一個分片，但是一年有12個月，是不是要有12個數據節點才行呢？並不是。例如我現在只有三個分片資料庫，這樣就可以1月在第一個數據分片中，2月在第二個數據分片中，3月在第三個數據分片中，當來到4月的時候，就會重新開始分片，4月在第一個數據分片，5月 ...
  • MySQL InnoDB加鎖規則分析
    1. 基礎知識回顧 1、索引的有序性，索引本身就是有序的 2、InnoDB中間隙鎖的唯一目的是防止其他事務插入間隙。間隙鎖可以共存。一個事務取得的間隙鎖並不會阻止另一個事務取得同一間隙上的間隙鎖。共用和獨占間隔鎖之間沒有區別。它們彼此之間不衝突，並且執行相同的功能。 3、MySQL預設隔離級別是 R ...
  • 基於FastGPT和芋道源碼挑戰一句話生成代碼
    芋道源碼相信很多朋友都很瞭解了，今天我們試著基於FastGPT實現芋道框架的代碼生成。芋道的代碼生成，是基於資料庫表欄位實現的，那我們的思路就是看看如何使用GPT幫我們生成資料庫表結構，只要資料庫表欄位有了，代碼也就生成好了。實現這個需求我們就需要用到FastGPT的高級編排功能。編排的整體思路如下 ...
  • 實時湖倉技術選型，企業如何借實時湖倉贏在“數據驅動”時代
    在之前三期的實時湖倉系列文章中，我們從業務側、產品側、應用側等幾個方向，為大家介紹了實時湖倉方方面面的內容，包括實時湖倉對於企業數字化佈局的重要性以及如何進行實時湖倉的落地實踐等。 本文將從純技術的角度，為大家解析實時湖倉的存儲原理以及生態選型，為企業建設實時湖倉給出技術方面的參考意見。 實時湖倉能 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類