mysql 字元集相關文章

基本概念

• 字元(Character)是指人類語言中最小的表義符號。例如’A'、’B'等；

• 給定一系列字元，對每個字元賦予一個數值，用數值來代表對應的字元，這一數值就是字元的編碼(Encoding)。例如，我們給字元’A'賦予數值0，給字元’B'賦予數值1，則0就是字元’A'的編碼；

• 給定一系列字元並賦予對應的編碼後，所有這些字元和編碼對組成的集合就是字元集(Character Set)。例如，給定字元列表為{‘A’,'B’}時，{‘A’=>0, ‘B’=>1}就是一個字元集；

• 字元序(Collation)是指在同一字元集內字元之間的比較規則；

• 確定字元序後，才能在一個字元集上定義什麼是等價的字元，以及字元之間的大小關係；

• 每個字元序唯一對應一種字元集，但一個字元集可以對應多種字元序，其中有一個是預設字元序(Default Collation)；

• MySQL中的字元序名稱遵從命名慣例：以字元序對應的字元集名稱開頭；以_ci(表示大小寫不敏感)、_cs(表示大小寫敏感)或_bin(表示按編碼值比較)結尾。例如：在字元序“utf8_general_ci”下，字元“a”和“A”是等價的；

MySQL字元集設定

• 系統變數：

– character_set_server：預設的內部操作字元集

– character_set_client：用戶端來來源資料使用的字元集

– character_set_connection：串連層字元集

– character_set_results：查詢結果字元集

– character_set_database：當前選中資料庫的預設字元集

– character_set_system：系統中繼資料(欄位名等)字元集

– 還有以collation_開頭的同上面對應的變數，用來描述字元序。

• 用introducer指定文本字串的字元集：

– 格式為：[_charset] ‘string’ [COLLATE collation]

– 例如：

• SELECT _latin1 ‘string’;

• SELECT _utf8 ‘你好’ COLLATE utf8_general_ci;

– 由introducer修飾的文本字串在請求過程中不經過多餘的轉碼，直接轉換為內部字元集處理。

MySQL中的字元集轉換過程

1. MySQL Server收到請求時將請求資料從character_set_client轉換為character_set_connection；

2. 進行內部操作前將請求資料從character_set_connection轉換為內部操作字元集，其確定方法如下：

• 使用每個資料欄位的CHARACTER SET設定值；

• 若上述值不存在，則使用對應資料表的DEFAULT CHARACTER SET設定值(MySQL擴充，非SQL標準)；

• 若上述值不存在，則使用對應資料庫的DEFAULT CHARACTER SET設定值；

• 若上述值不存在，則使用character_set_server設定值。

3. 將操作結果從內部操作字元集轉換為character_set_results。

常見問題解析

• 向預設字元集為utf8的資料表插入utf8編碼的資料前沒有設定串連字元集，查詢時設定串連字元集為utf8

– 插入時根據MySQL伺服器的預設設定，character_set_client、character_set_connection和character_set_results均為latin1；

– 插入操作的資料將經過latin1=>latin1=>utf8的字元集轉換過程，這一過程中每個插入的漢字都會從原始的3個位元組變成6個位元組儲存；

– 查詢時的結果將經過utf8=>utf8的字元集轉換過程，將儲存的6個位元組原封不動返回，產生亂碼……

• 向預設字元集為latin1的資料表插入utf8編碼的資料前設定了串連字元集為utf8

– 插入時根據串連字元集設定，character_set_client、character_set_connection和character_set_results均為utf8；

– 插入資料將經過utf8=>utf8=>latin1的字元集轉換，若未經處理資料中含有\u0000~\u00ff範圍以外的Unicode字 符，會因為無法在latin1字元集中表示而被轉換為“?”(0x3F)符號，以後查詢時不管串連字元集設定如何都無法恢複其內容了。

檢測字元集問題的一些手段

• SHOW CHARACTER SET;

• SHOW COLLATION;

• SHOW VARIABLES LIKE ‘character%’;

• SHOW VARIABLES LIKE ‘collation%’;

• SQL函數HEX、LENGTH、CHAR_LENGTH

• SQL函數CHARSET、COLLATION

使用MySQL字元集時的建議

• 建立資料庫/表和進行資料庫操作時盡量顯式指出使用的字元集，而不是依賴於MySQL的預設設定，否則MySQL升級時可能帶來很大困擾；

• 資料庫和串連字元集都使用latin1時雖然大部分情況下都可以解決亂碼問題，但缺點是無法以字元為單位來進行SQL操作，一般情況下將資料庫和串連字元集都置為utf8是較好的選擇；

• 使用mysql C API時，初始化資料庫控制代碼後馬上用mysql_options設定MYSQL_SET_CHARSET_NAME屬性為utf8，這樣就不用顯式地用 SET NAMES語句指定串連字元集，且用mysql_ping重連斷開的長串連時也會把串連字元集重設為utf8；

• 對於mysql PHP API，一般頁面級的PHP程式總已耗用時間較短，在串連到資料庫以後顯式用SET NAMES語句設定一次串連字元集即可；但當使用長串連時，請注意保持串連通暢並在斷開重連後用SET NAMES語句顯式重設串連字元集。

其他注意事項

• my.cnf中的default_character_set設定隻影響mysql命令串連伺服器時的串連字元集，不會對使用libmysqlclient庫的應用程式產生任何作用！

• 對欄位進行的SQL函數操作通常都是以內部操作字元集進行的，不受串連字元集設定的影響。

• SQL語句中的裸字串會受到串連字元集或introducer設定的影響，對於比較之類的操作可能產生完全不同的結果，需要小心！

MySQL 亂碼問題終極揭秘

hehe，在標題黨橫行的年代，不搞一個聳人聽聞的標題就不能引人矚目。不過，就我個人經驗看，對這個問題搞個“終極揭秘”倒也不是什麼難事兒

 

MySQL 的亂碼問題，大概是系統開發起步時出現頻率最高的問題了。見招拆招（有時是以錯對錯）的話，面上總是能解決問題，但隱患不除，沒準什麼時候又冒出來；有人給出簡單徹底的方案：所有庫表都用 UTF-8 編碼，本該天下太平了，但有時候還是出問題。

 

這個問題真的那麼複雜嗎？怎麼就那麼難以馴服呢？其實，問題遠比想象的簡單得多！

 

關鍵是要看穿一點：問題不出在資料庫、表的編碼方式上，只要弄好“串連”的編碼方式就 OK 了。

 

·串連的編碼方式

 

MySQL 足夠聰明，只要你跟它說話時發音準確，它就會很好地執行命令，而你完全不用關心它的內部儲存方式這種細節。

所以，你只需做好兩件事：

 

1. 明確地告訴 MySQL 你將在 SQL 陳述式中使用何種編碼方式；

 

2. 在發送 SQL 陳述式時，準確地使用前面聲明的編碼方式。

 

為了做到第一點，最簡單的辦法就是在建立了資料庫連接後，不管三七二十一，先執行一個 "SET NAMES ..." 再說。

 

至於第二點，就跟具體的程式設計語言有關係了。對於 PHP 語言，“字串”作為一個值，本身就是一個按某種編碼方式組織的“位元組串”，如果不符合要求的話可以用 iconv() 做轉換。對於 Java 語言來說，因為書寫 SQL 陳述式的 String 本身一定是 UCS2 編碼的，你可以用串連串裡的 useUnicode 和 characterEncoding 來告訴 Driver 把語句中的字串值轉換成何種編碼方式，或者自己先把 String 轉換成特定編碼方式的 byte[] 後再用 setBytes()、setBinaryStream()
等方式傳遞給 PreparedStatement。

 

只要做好上面兩點，包你不會再遇到 MySQL 亂碼問題。當然，對於整個系統而言，還有其它環節可能產生亂碼，比如網頁編碼，只要細心分辨，都不難解決。

 

·資料庫表的編碼方式

 

上面我們看到，要讓 MySQL 不出現亂碼，要點是在於資料庫連接的編碼方式，而資料庫表的編碼方式並不重要，MySQL 會自動做好編碼轉換工作。

 

那麼在做資料庫設計的時候，怎麼選擇編碼方式呢？其實這時候考慮的應該是另外幾個因素，比如：

 

1. 字元集的相容性。如果預期將在一個欄位中同時儲存簡體、繁體、日文等字元，那你就必須選擇一個能同時容納這幾種字元集的編碼方式，一般來說，UTF-8 是最好的選擇。

 

2. 儲存效率。如果預期僅儲存簡體中文，那麼選擇 GBK 會比 UTF-8 的儲存效率高一些。不過，似乎大多數情況下，這一因素並不很重要。

 

3. 結合實際的應用情況，盡量減少資料庫訪問過程中的編碼轉換環節。如所示的傳輸過程，其中“C轉換”是用戶端的轉換工作（可能是應用程式負責，也可能是 Driver 負責，比如 JDBC Driver），“S轉換”是服務端的編碼轉換（由 MySQL 自動進行）。

用戶端程式 ------------> 用戶端介面 ----------> 服務端介面 --------------> 資料庫儲存
　UCS2/GBK/…　　C轉換　　　　　　　　編碼傳輸　　　　　　　　　S轉換　　　UTF-8/GBK/…

用戶端程式 <------------ 用戶端介面 <---------- 服務端介面 <------------ 資料庫儲存
　UCS2/GBK/…　　C轉換　　　　　　　　編碼傳輸　　　　　　　　　S轉換　　　UTF-8/GBK/…