2020重新出發,MySql基礎,MySql字符集

夜雨流雲發表於2020-08-31

MySQL字符集和校對規則詳解

先來簡單瞭解一下字元、字符集和字元編碼。

字元(Character):是計算機中字母、數字、符號的統稱,一個字元可以是一箇中文漢字、一個英文字母、一個阿拉伯數字、一個標點符號等。

  • 計算機是以二進位制的形式來儲存資料的。平時我們在顯示器上看到的數字、英文、標點符號、漢字等字元都是二進位制數轉換之後的結果。

字符集(Character set):定義了字元和二進位制的對應關係,為字元分配了唯一的編號。常見的字符集有 ASCII、GBK、IOS-8859-1 等。

字元編碼(Character encoding):也可以稱為字集碼,規定了如何將字元的編號儲存到計算機中。大部分字符集都只對應一種字元編碼。

  • 例如:ASCII、IOS-8859-1、GB2312、GBK,都是既表示了字符集又表示了對應的字元編碼。所以一般情況下,可以將兩者視為同義詞。Unicode 字符集除外,Unicode 有三種編碼方案,即 UTF-8、UTF-16 和 UTF-32。最為常用的是 UTF-8 編碼。

校對規則(Collation):也可以稱為排序規則,是指在同一個字符集內字元之間的比較規則。字符集和校對規則是一對多的關係,每個字符集都有一個預設的校對規則。字符集和校對規則相輔相成,相互依賴關聯。

簡單來說,字符集用來定義 MySQL 儲存字串的方式,校對規則用來定義 MySQL 比較字串的方式。

有些資料庫並沒有清晰的區分開字符集和校對規則。

  • 例如,在 SQL Server 中建立資料庫時,選擇字符集就相當於選定了字符集和校對規則。

而在 MySQL 中,字符集和校對規則是區分開的,必須設定字符集和校對規則。一般情況下,沒有特殊需求,只設定其一即可。只設定字符集時,MySQL 會將校對規則設定為字符集中對應的預設校對規則。

可以通過SHOW VARIABLES LIKE 'character%';命令檢視當前 MySQL 使用的字符集,命令和執行結果如下:

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | latin1                                                  |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.01 sec)

上述執行結果說明如下表所示:

  • character_set_client :MySQL 客戶端使用的字符集
  • character_set_connection :連線資料庫時使用的字符集
  • character_set_database : 建立資料庫使用的字符集
  • character_set_filesystem :MySQL 伺服器檔案系統使用的字符集,預設值為 binary,不做任何轉換
  • character_set_results : 資料庫給客戶端返回資料時使用的字符集
  • character_set_server : MySQL 伺服器使用的字符集,建議由系統自己管理,不要人為定義
  • character_set_system :資料庫系統使用的字符集,預設值為 utf8,不需要設定 character_sets_dir 字符集的安裝目錄

注意:亂碼時,不需要關心 character_set_filesystem、character_set_system 和 character_sets_dir 這 3 個系統變數,它們不會影響亂碼 。

可以通過SHOW VARIABLES LIKE 'collation\_%';命令檢視當前 MySQL 使用的校對規則,命令和執行結果如下:

mysql> SHOW VARIABLES LIKE 'collation\_%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | gbk_chinese_ci    |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set, 1 warning (0.01 sec)

對上述執行結果說明如下:

  • collation_connection:連線資料庫時使用的校對規則
  • collation_database:建立資料庫時使用的校對規則
  • collation_server:MySQL 伺服器使用的校對規則

校對規則命令約定如下:

  • 以校對規則所對應的字符集名開頭
  • 以國家名居中(或以 general 居中)
  • 以 ci、cs 或 bin 結尾,ci 表示大小寫不敏感,cs 表示大小寫敏感,bin 表示按二進位制編碼值比較。

MySQL字符集的轉換過程

MySQL 中字符集的轉換過程如下:

  1. 在命令提示符視窗(cmd 命令列)中執行 MySQL 命令或 sql 語句時,這些命令或語句從“命令提示符視窗字符集”轉換為“character_set_client”定義的字符集。
  2. 使用命令提示符視窗成功連線 MySQL 伺服器後,就建立了一條“資料通訊鏈路”,MySQL 命令或 sql 語句沿著“資料鏈路”傳向 MySQL 伺服器,由 character_set_client 定義的字符集轉換為 character_set_connection 定義的字符集。
  3. MySQL 服務例項收到資料通訊鏈路中的 MySQL 命令或 sql 語句後,將 MySQL 命令或 sql 語句從 character_set_connection 定義的字符集轉換為 character_set_server 定義的字符集。
  4. 若 MySQL 命令或 sql 語句針對於某個資料庫進行操作,此時將 MySQL 命令或 sql 語句從 character_set_server 定義的字符集轉換為 character_set_database 定義的字符集。
  5. MySQL 命令或 sql 語句執行結束後,將執行結果設定為 character_set_results 定義的字符集。
  6. 執行結果沿著開啟的資料通訊鏈路原路返回,將執行結果從 character_set_results 定義的字符集轉換為 character_set_client 定義的字符集,最終轉換為命令提示符視窗字符集,顯示到命令提示符視窗中。

MySQL字符集的轉換過程

MySQL檢視字符集和校對規則

在 MySQL 中,檢視可用字符集的命令和執行過程如下:

SHOW CHARACTER set
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 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.02 sec)

其中:

  • 第一列(Charset)為字符集名稱;
  • 第二列(Description)為字符集描述;
  • 第三列(Default collation)為字符集的預設校對規則;
  • 第四列(Maxlen)表示字符集中一個字元佔用的最大位元組數。

常用的字符集如下:

  • latin1 支援西歐字元、希臘字元等。
  • gbk 支援中文簡體字元。
  • big5 支援中文繁體字元。
  • utf8 幾乎支援所有國家的字元。

也可以通過查詢 information_schema.character_set 表中的記錄,來檢視 MySQL 支援的字符集。SQL 語句和執行過程如下:

mysql> SELECT * FROM information_schema.character_sets;
+--------------------+----------------------+---------------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION                     | MAXLEN |
+--------------------+----------------------+---------------------------------+--------+
| big5               | big5_chinese_ci      | Big5 Traditional Chinese        |      2 |
| dec8               | dec8_swedish_ci      | DEC West European               |      1 |
| cp850              | cp850_general_ci     | DOS West European               |      1 |
| hp8                | hp8_english_ci       | HP West European                |      1 |
......

可以使用 SHOW COLLATION LIKE '***';命令來檢視相關字符集的校對規則。

mysql> SHOW COLLATION LIKE 'gbk%';
+----------------+---------+----+---------+----------+---------+
| Collation      | Charset | Id | Default | Compiled | Sortlen |
+----------------+---------+----+---------+----------+---------+
| gbk_chinese_ci | gbk     | 28 | Yes     | Yes      |       1 |
| gbk_bin        | gbk     | 87 |         | Yes      |       1 |
+----------------+---------+----+---------+----------+---------+
2 rows in set (0.00 sec)

上面執行結果為 GBK 字符集所對應的校對規則,其中 gbk_chinese_ci 是預設的校對規則,對大小寫不敏感。而 gbk_bin 按照二進位制編碼的值進行比較,對大小寫敏感。

也可以通過查詢 information_schema.COLLATIONS 表中的記錄,來檢視 MySQL 中可用的校對規則。SQL 語句和執行過程如下:

mysql> SELECT * FROM information_schema.COLLATIONS;
+--------------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME           | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------------+--------------------+-----+------------+-------------+---------+
| big5_chinese_ci          | big5               |   1 | Yes        | Yes         |       1 |
| big5_bin                 | big5               |  84 |            | Yes         |       1 |
| dec8_swedish_ci          | dec8               |   3 | Yes        | Yes         |       1 |
| dec8_bin                 | dec8               |  69 |            | Yes         |       1 |
| cp850_general_ci         | cp850              |   4 | Yes        | Yes         |       1 |
| cp850_bin                | cp850              |  80 |            | Yes         |       1 |
......

在實際應用中,我們應事先確認應用需要按照什麼樣方式排序,是否區分大小寫,然後選擇相應的校對規則。

MySQL設定預設字符集和校對規則

MySQL 伺服器可以支援多種字符集,在同一臺伺服器、同一個資料庫甚至同一個表的不同欄位中,都可以使用不同的字符集。Oracle 等其它資料庫管理系統都只能使用相同的字符集,相比之下,MySQL 明視訊記憶體在更大的靈活性。

MySQL 的字符集和校對規則有 4 個級別的預設設定,即伺服器級、資料庫級、表級和欄位級。它們分別在不同的地方設定,作用也不相同。

伺服器字符集和校對規則

修改伺服器預設字符集和校對規則的方法如下。

可以在 my.ini 配置檔案中設定伺服器字符集和校對規則,新增內容如下:

[mysqld]
character-set-server=字符集名稱

連線 MySQL 伺服器時指定字符集:

mysql --default-character-set=字符集名稱 -h 主機IP地址 -u 使用者名稱 -p 密碼

如果沒有指定伺服器字符集,MySQL 會預設使用 latin1 作為伺服器字符集。如果只指定了字符集,沒有指定校對規則,MySQL 會使用該字符集對應的預設校對規則。如果要使用字符集的非預設校對規則,需要在指定字符集的同時指定校對規則。

可以用 SHOW VARIABLES LIKE 'character_set_server'SHOW VARIABLES LIKE 'collation_server' 命令查詢當前伺服器的字符集和校對規則。

mysql> SHOW VARIABLES LIKE 'character_set_server';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| character_set_server | gbk    |
+----------------------+--------+
1 row in set, 1 warning (0.01 sec)

mysql> SHOW VARIABLES LIKE 'collation_server';
+------------------+-------------------+
| Variable_name    | Value             |
+------------------+-------------------+
| collation_server | gbk_chinese_ci    |
+------------------+-------------------+
1 row in set, 1 warning (0.01 sec)

資料庫字符集和校對規則

資料庫的字符集和校對規則在建立資料庫時指定,也可以在建立完資料庫後通過 ALTER DATABASE 命令進行修改

需要注意的是,如果資料庫裡已經存在資料,修改字符集後,已有的資料不會按照新的字符集重新存放,所以不能通過修改資料庫的字符集來修改資料的內容

設定資料庫字符集的規則如下:

  • 如果指定了字符集和校對規則,則使用指定的字符集和校對規則;
  • 如果指定了字符集沒有指定校對規則,則使用指定字符集的預設校對規則;
  • 如果指定了校對規則但未指定字符集,則字符集使用與該校對規則關聯的字符集;
  • 如果沒有指定字符集和校對規則,則使用伺服器字符集和校對規則作為資料庫的字符集和校對規則。

為了避免受到預設值的影響,推薦在建立資料庫時指定字符集和校對規則。

可以使用 SHOW VARIABLES LIKE 'character_set_database'SHOW VARIABLES LIKE 'collation_database' 命令檢視當前資料庫的字符集和校對規則。

mysql> SHOW VARIABLES LIKE 'character_set_database';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW VARIABLES LIKE 'collation_database';
+--------------------+-------------------+
| Variable_name      | Value             |
+--------------------+-------------------+
| collation_database | latin1_swedish_ci |
+--------------------+-------------------+
1 row in set, 1 warning (0.00 sec)

表字符集和校對規則

表的字符集和校對規則在建立表的時候指定,也可以在建立完表後通過 ALTER TABLE 命令進行修改。

同樣,如果表中已有記錄,修改字符集後,原有的記錄不會按照新的字符集重新存放。表的欄位仍然使用原來的字符集。

設定表的字符集規則和設定資料庫字符集的規則基本類似:

  • 如果指定了字符集和校對規則,使用指定的字符集和校對規則;
  • 如果指定了字符集沒有指定校對規則,使用指定字符集的預設校對規則;
  • 如果指定了校對規則但未指定字符集,則字符集使用與該校對規則關聯的字符集;
  • 如果沒有指定字符集和校對規則,使用資料庫字符集和校對規則作為表的字符集和校對規則。

為了避免受到預設值的影響,推薦在建立表的時候指定字符集和校對規則。

可以使用 SHOW CREATE TABLE 命令檢視當前表的字符集和校對規則,SQL 語句和執行結果如下:

mysql> SHOW CREATE TABLE tb_students_info \G
*************************** 1. row ***************************
       Table: tb_students_info
Create Table: CREATE TABLE `tb_students_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `height` float DEFAULT NULL,
  `course_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

列字符集和校對規則

MySQL 可以定義列級別的字符集和校對規則,主要是針對相同表的不同欄位需要使用不同字符集的情況。一般遇到這種情況的機率比較小,這只是 MySQL 提供給我們一個靈活設定的手段。

列字符集和校對規則的定義可以在建立表時指定,或者在修改表時調整。語法格式如下:

ALTER TABLE 表名 MODIFY 列名 資料型別 CHARACTER SET 字符集名;

如果在建立列的時候沒有特別指定字符集和校對規則,預設使用表的字符集和校對規則。

連線字符集和校對規則

上面所講的 4 種設定方式,確定的都是資料儲存的字符集和校對規則。實際應用中,還需要設定客戶端和伺服器之間互動的字符集和校對規則。

對於客戶端和伺服器的互動操作,MySQL 提供了 3 個不同的引數:character_set_client、character_set_connection 和 character_set_results,分別代表客戶端、連線和返回結果的字符集。通常情況下,這 3 個字符集是相同的,這樣可以確保正確讀出使用者寫入的資料,尤其是中文字元。字符集不同時,容易導致寫入的記錄不能正確讀出。

設定客戶端和伺服器連線的字符集和校對規則有以下幾種方法:

  • 在 my.ini 配置檔案中,設定以下語句:
[mysql]
default-character-set=gbk

這樣伺服器啟動後,所有連線預設使用 GBK 字符集進行連線。

  • 可以通過以下命令來設定連線的字符集和校對規則,這個命令可以同時修改以上 3 個引數(character_set_client、character_set_connection 和 character_set_results)的值。
SET NAMES gbk;

使用這個方法可以“臨時一次性地”修改客戶端和伺服器連線時的字符集為 gbk。

  • MySQL 還提供了下列 MySQL 命令“臨時地”修改 MySQL“當前會話的”字符集和校對規則。
set character_set_client = gbk;
set character_set_connection = gbk;
set character_set_database = gbk;
set character_set_results = gbk;
set character_set_server = gbk;
set collation_connection = gbk_chinese_ci;
set collation_database = gbk_chinese_ci;
set collation_server = gbk_chinese_ci;

MySQL字符集的選擇

由於資料庫中儲存的資料大部分都是各種文字,所以字符集對資料庫的儲存、處理效能,以及日後系統的移植、推廣都會有影響。對資料庫來說,字符集非常重要。不論是在 MySQL 資料庫還是其它資料庫,都存在字符集的選擇問題。

如果在建立資料庫時沒有正確選擇字符集,在後期就可能需要更換字符集,而更換字符集是代價比較高的操作,也存在一定的風險。所以推薦在應用開始階段,就按照實際需求,正確的選擇合適的字符集,避免後期不必要的調整。

目前 MySQL 5.7 支援幾十種字符集,包括 UCS-2、UTF-16、UTF-16LE、UTF-32、 UTF-8 和 utf8mb4 等 Unicode 字符集。

那麼面對眾多的字符集,我們該如何選擇呢?

在選擇資料庫字符集時,可以根據應用的需求,結合字符集的特點來權衡,主要考慮以下幾方面的因素。

  1. 滿足應用支援語言的需求。如果應用要處理各種各樣的文字,或者將釋出到使用不同語言的國家或地區,就應該選擇 Unicode 字符集。對 MySQL 來說,目前就是 UTF-8。
  2. 如果應用中涉及已有資料的匯入,就要充分考慮資料庫字符集對已有資料的相容性。假如已有資料的字符集是 GBK,如果選擇 GB 2312-80 為資料庫字符集,就很可能出現某些文字無法正確匯入。
  3. 如果資料庫只需要支援一般中文,資料量很大,效能要求也很高,那就應該選擇雙位元組定長編碼的中文字符集,比如 GBK。
    • 因為,相對於 UTF-8 而言,GBK 比較“小”,每個漢字只佔 2 個位元組,而 UTF-8 漢字編碼需要 3 個位元組,這樣可以減少磁碟 I/O、資料庫 Cache 以及網路傳輸的時間,從而提高效能。相反,如果應用主要處理英文字元,僅有少量漢字資料,那麼選擇 UTF-8 更好,因為 GBK、UCS-2、UTF-16 的西文字元編碼都是 2 個位元組,會造成很多不必要的開銷。
  4. 如果資料庫需要做大量的字元運算,如比較、排序等,那麼選擇定長字符集可能更好,因為定長字符集的處理速度要比變長字符集的處理速度快。
  5. 如果所有客戶端程式都支援相同的字符集,則應該優先選擇該字符集作為資料庫字符集。這樣可以避免因字符集轉換帶來的效能開銷和資料損失。
  6. 在多種字符集都能夠滿足應用的前提下,應儘量使用小的字符集。因為更小的字符集意味著能夠節省空間、減少網路傳輸位元組數,同時由於儲存空間的較小間接的提高了系統的效能。

注意:有很多字符集都可以儲存漢字,比如 UTF-8、GB2312、GBK、Latin1 等等。但是常用的是 GB2312 和 GBK。因為 GB2312 字型檔比 GBK 字型檔小,有些偏僻字(例如:洺)不能儲存,因此在選擇字符集的時候一定要權衡這些偏僻字出現的機率,一般情況下,最好選用 GBK。

相關文章