1 引子
MySQL備份時選擇字符集是一個難題，特別是字符集不定的業務。mysqldump預設使用utf8，而官方也推薦使用utf8。但實際上，對於中文，部分相當一部分gbk編碼字元沒有對應的unicode編碼，也就是說這部分字符集使用utf8備份會導致資料丟失。那麼有沒有解決方法呢？
當然，最直接的方法是將這部分編碼的對映加上。但是，這部分的字符集數量並不是少數，而且，更蛋疼的是，似乎找不到這部分字符集權威的對映標準。那麼，還有其它方法嗎？
實際上，如果使用binary進行備份，就不會存在字符集的轉換過程，也就不會存在上述問題。那麼，使用binary是否就解決了gbk所有的問題呢？答案是NO。
2 binary的問題
在講binary的問題之前。需要弄清2個問題。對於MySQL備份，分兩部分：schema資訊和實際資料。而Schema資訊一律使用utf8編碼，但是，default value除外。這正是問題的來源。
2.1 utf8備份
(1)檔案.frm會儲存table的schema資訊，並通過一個實際的記錄來儲存各個field的預設值。Schema對應的資訊(包括comment)使用utf8儲存，但是default value使用table指定的字符集進行儲存。
(2)當執行show create table語句時，mysqld會將frm中的預設值從table指定的編碼轉成utf8編碼。
(3)當mysqld執行create table語句，會將default value從utf8轉成table指定的字符集。
2.2 binary備份
如果指定binary進行備份。在匯入時，在建立table之前，雖然將character_set_client指定為utf8，但collation_connection還是binary。所以，儲存預設值時不會進行utf8到table指定的字符集的轉換。如果table指定為gbk編碼，匯入必然失敗。
示例：
|
CREATE TABLE `t1`( `iNetbarId` int(11) NOT NULL DEFAULT '0', `iUin` bigint(20) NOT NULL DEFAULT '0', `vNetbarName` varchar(80) NOT NULL DEFAULT '“-”', PRIMARY KEY (`iNetbarId`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
insert into t1 values(1,1,'xxxx'); |
可以看到，正常匯出的表，匯入卻出現1067 Invalid default value的錯誤。
3 解決方法
mysqldump時，在執行create table語句之前，增加對character_set_connection 的設定。
/*!40101 SET character_set_connection = utf8 */
這也算是MySQL一個bug，既然schema資訊從頭到尾都使用utf8，在執行create table之前，就應該將連線的字符集變數設定成utf8，而不是隻設定client的字符集變數。
作者：YY哥
出處：http://www.cnblogs.com/hustcat/
本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段宣告，且在文章頁面明顯位置給出原文連線，否則保留追究法律責任的權利。