MySQL字元集知識總結

MySQL字元集知識總結

字元集&字元編碼方式

字元集(Character set)是多個字元的集合，字元集種類較多，每個字元集包含的字元個數不同，這裡的字元可以是英文字元，漢字字元，或者其他國家語言字元。

常見字元集包括：ASCII字元集、LATIN1字元集、GB2312字元集、GBK字元集、GB18030字元集、Unicode字元集等。字元編碼方式是用一個或多個位元組表示字元集中的一個字元。每種字元集都有自己特有的編碼方式，因此同一個字元，在不同字元集的編碼方式下，會產生不同的二進位。ASCII是基於羅馬字母表的一套字元集，它採用1個位元組的低7位表示字元，高位始終為0。LATIN1字元集相對於ASCII字元集做了擴充，仍然使用一個位元組表示字元，但啟用了高位，擴充了字元集的表示範圍。GB2312、GBK、GB18030字元集是支援中文的字元集，字元集範圍GB2312<GBK< GB18030。GBK字元集的字元有一位元組編碼和兩位元組編碼方式。對於00-7F的字元與ASCII保持一致，漢字採用2個位元組表示。第一位元組範圍是81-FE，避免與00-7F衝突。Unicode字元集是電腦科學領域裡的一項業界標準，支援了所有國家的文字字元。Unicode字元集有好幾種編碼方式，比如常見的utf-8，utf-16,utf-32等。Utf8採用1-4個位元組表示字元，utf-16採用固定的2個位元組，utf-32則採用4個位元組儲存。

MySQL與字元集

只要涉及到文字的地方，就會存在字元集和編碼方式。對於MySQL資料庫系統而言，使用者從MySQL client端敲入一條sql語句，通過TCP/IP傳遞給MySQL server進程，到最終存入server端的檔案，每個環節都涉及到字元儲存。涉及到字元儲存的地方，就涉及到字元集編碼，通過MySQL提供的系統變數就可見一斑。MySQL字元集設定系統變數以及含義如下表： 

變數名	含義
character_set_server	預設的內部操作字元集
character_set_client	用戶端來來源資料使用的字元集
character_set_connection	串連層字元集
character_set_results	查詢結果字元集
character_set_database	當前選中資料庫的預設字元集
character_set_system	系統中繼資料(欄位名等)字元集

以上這些參數如何起作用

1.庫、表、列字元集的由來
(1).建庫時，若未明確指定字元集，則採用character_set_server指定的字元集。
(2).建表時，若未明確指定字元集，則採用當前庫所採用的字元集。
(3).新增，修改表欄位時，若未明確指定字元集，則採用當前表所採用的字元集。

2.更新、查詢涉及到得字元集變數

使用者在更新(插入，刪除，修改)，查詢資料庫時，最常使用的字元集變數主要包含character_set_client，character_set_connection，character_set_result。
更新流程字元集轉換過程：character_set_client-》character_set_connection-》表字元集。
查詢流程字元集轉換過程：表字元集-》character_set_result

PS：個人認為character_set_connection串連字元集設定有點冗餘，因為最終都是要轉換到表字元集的。

3.character_set_database

這個參數是當前預設資料庫的字元集，比如執行use xxx後，當前資料庫變為xxx，若xxx的字元集為utf8，那麼這個變數值就變為utf8。因此這個參數是供系統設定，無需人工設定。

mysql字元編碼轉換流程

      如果以上各個系統變數的設定不一致，比如character_set_client為UTF8，而character_set_database為GBK，則會出現需要進行編碼轉換的情況。那麼字元集轉換的原理是什嗎？假設GBK字元集的字串“小明”，需要轉為UTF8字元集儲存，實際就是對於“小明”字串中的每個漢字去UTF8編碼錶裡面查詢對應的二進位，然後儲存，僅此而已，編碼轉換並不涉及到複雜的演算法。mysql字元集轉換主要涉及到幾個步驟：

1) 將資料從character_set_client設定轉換為character_set_connection設定；

2) 將character_set_connection設定轉為表欄位的字元集設定；

3) 將操作結果從表欄位字元集轉為character_set_results設定。

下面我通過一個常用的情境來描述字元集轉換的流程。使用者通過mysql命令列(如果是遠端連線：SecureCRT)，敲入命令“insert into T values(1,’小明’)”，字串’小明’在流轉過程中二進位儲存內容。

a) 使用者採用的用戶端為utf8字元集，character_set_client=gbk，character_set_connection=gbk, 表T採用gbk字元集。

 

由於character_set_client、character_set_connection和表字元集均為GBK，不涉及編碼轉換。因此，表雖然為字元集雖然為GBK，但“小明”的編碼並非為GBK編碼的二進位流，而是UTF8的二進位流，兩個漢字佔用了6個位元組，而讀取則是一個逆向的過程，不涉及到編碼轉換，查詢依然能正確返回“小明”。

b)  在a)的情況下，改變character_set_client的設定為utf8，查詢插入的值。

 

可以看到返回的值是“灝忔槑”， 這是由於表的字元集是GBK，而用戶端請求是UTF8，那麼server將二進位流E5B08FE6988E對應的GBK漢字“灝忔槑”轉為UTF8漢字對應的二進位流E7818FE5BF94E6A791，因此查詢結果在SecureCRT就顯示為“灝忔槑”，即通常我們所謂的亂碼。

c) 在b)的情況下，設定SecureCRT的字元集為GBK，看看SecureCRT字元集設定對結果影響

 

可以看到返回的是另外一組字元“鐏忓繑妲�”，整個流轉過程與b)一樣,只是在第一步發生了位元組流轉換，設定SecureCRT字元集編碼，只是改變了顯示方式。

字元集相關的SQL語句

1)  查看字元集編碼設定

SHOW VARIABLES LIKE ‘%CHARACTER%’

2)  設定字元集編碼

SET NAMES xxx；

這個語句相當於設定了client的字元集，主要包含3個系統變數，character_set_client，character_set_connection和character_set_results。

3) 修改資料庫字元集

ALTER DATABASE  DATABASENAME  CHARACTER SET XXX;

這個語句只修改庫的字元集，影響後續建立的表的預設定義；對於已建立的表的字元集不受影響。

4) 修改表的字元集

ALTER TABLE TABLENAME CHARACTER SET XXX；

這個語句只修改表的字元集，影響後續該表新增列的預設定義，已有列的字元集不受影響。

ALTER TABLE TABLENAME CONVERT TO CHARACTER SET XXX;

 這個語句同時修改表字元集和已有列字元集，並將已有資料進行字元集編碼轉換。

5) 修改列字元集

ALTER TABLE `TABLE_NAME` MODIFY COLUMN `COLUMN_NAME`  CHARACTER SET xxx

6)  查詢字元的二進位編碼

SELECT HEX(COL_NAME) FROM TABLE_NAME;SELECT LENGTH(COL_NAME) FROM TABLE_NAME;

對於GBK的表，如果查出來一個字元佔用了3個位元組，比1這種情況，則肯定是字元集在某個環節設定統一，圖1就是因為用戶端是UTF8，而mysqlclient和database都是GBK造成的。

mysql預設的字元集latin1

     mysql 4.x版本之前預設採用的是latin1字元集(又稱ISO-8859-1)，latin1字元集編碼方式採用單位元組編碼。拋一個問題，latin1字元集的表，使用者寫入和讀取漢字是否有問題？答案是只要合理設定，沒有問題。假設SecureCRT為UTF8，character_set_client和表字元集均設定為latin1，參考第3節的分析，那麼使用者讀取和寫入資料的過程中，並不涉及字元集編碼轉換的問題，將UTF8的漢字字元轉為二進位流寫入database，提取出來後，secureCRT再將對應的二進位解碼為對應的漢字，所以不影響使用者的使用。但是，若character_set_client，character_set_connection，與表字元集設定等不統一，就可能出現亂碼的情況。

本文永久更新連結地址：