mysql數(shù)據(jù)校驗(yàn)過(guò)程中的字符集問(wèn)題處理
場(chǎng)景:
主庫(kù)DB:utf8字符集
備庫(kù)DB:gbk字符集
需求:
校驗(yàn)主備數(shù)據(jù)是否一致,并且修復(fù)
校驗(yàn)過(guò)程:
設(shè)置主庫(kù)連接為utf8,設(shè)置備庫(kù)連接為gbk,分別進(jìn)行查詢,將返回的的結(jié)果集按記錄逐字段比較。
顯示結(jié)果:
原本相同的漢字字符,數(shù)據(jù)校驗(yàn)認(rèn)為不一致。
原因分析:
對(duì)于主庫(kù)而已,由于建立連接的字符集為UTF8,則返回的漢字字符編碼為UTF8格式;對(duì)于備庫(kù)而言則是GBK格式,而程序中通過(guò)字符串比較函數(shù)strcasecmp進(jìn)行比較,顯然不同的字符集編碼,相同的字符有不同的二進(jìn)制,因此結(jié)果肯定不會(huì)相等。
進(jìn)一步分析:
那么對(duì)于這種情況,建立連接應(yīng)該采用哪種字符集呢?GBK or UTF8。其實(shí)選擇任何一種字符集都是OK的,只要是訪問(wèn)主庫(kù)和備庫(kù)的字符集保持一致即可,唯一的區(qū)別在于,若選擇的字符集與客戶端的字符集不一致,可能導(dǎo)致無(wú)法正常顯示字符,即字符顯示為亂碼。
我們以客戶端的字符集為例,詳細(xì)說(shuō)說(shuō)三種情況:【這里的客戶端可以認(rèn)為是SecureCRT】
備注:綠色框代表DB字符集,黃色框代表連接字符集,橙色框代表客戶端
第一種情況:
就是上述的情況,主庫(kù)返回字符的GBK編碼,備庫(kù)返回字符的UTF8編碼,因此進(jìn)行字段比對(duì),則會(huì)出現(xiàn)誤差。
第二種情況:
訪問(wèn)主庫(kù)的連接不變,備庫(kù)連接由UTF8變?yōu)镚BK,因此進(jìn)行返回時(shí),數(shù)據(jù)庫(kù)會(huì)將DB的字符集轉(zhuǎn)為GBK返回給客戶端,那么對(duì)于客戶端而已,相同字符都是通過(guò)GBK編碼表示,因此二進(jìn)制相等,校驗(yàn)結(jié)果正確。
第三種情況:
訪問(wèn)主庫(kù)和備庫(kù)的連接都是UTF8,因此對(duì)于主庫(kù)而已,返回給客戶端的字符編碼由GBK轉(zhuǎn)為UTF8,此時(shí)主庫(kù)和備庫(kù)都是UTF8編碼,校驗(yàn)結(jié)果正確。但由于客戶端實(shí)質(zhì)是GBK編碼方式顯示,因此返回的漢字字符都是亂碼,但不影響校驗(yàn)結(jié)果的正確性。
修復(fù):
既然選擇與主備庫(kù)任一一個(gè)相同的字符集去訪問(wèn),都不會(huì)影響校驗(yàn)結(jié)果的正確性,那么影響修復(fù)呢?由于UTF8的編碼范圍比GBK編碼范圍要大,因此若采用GBK連接訪問(wèn)UTF8編碼DB,有可能出現(xiàn)部分字符GBK不能表示的情況。
我們拿第二種情況說(shuō)明,此時(shí)主庫(kù)為GBK,備庫(kù)為UTF8,使用GBK訪問(wèn)UTF8。假設(shè)存在UTF8轉(zhuǎn)為GBK過(guò)程中部分字符丟失,這時(shí)候主備庫(kù)肯定是不一致的,因?yàn)榇嬖诓糠肿址鸊BK無(wú)法表示。 假設(shè)修復(fù)語(yǔ)句如下:
Update t set c1=master_value where c1=slave_value and id=?
其中t表示表名,id是主鍵表示某一行,master_value為主庫(kù)c1列的值,slave_value為備庫(kù)c1列的值。此時(shí),slave_value由于UTF8轉(zhuǎn)為GBK已經(jīng)丟失,因此語(yǔ)句執(zhí)行最終影響0行記錄,無(wú)法修復(fù)。
結(jié)論:
客戶端訪問(wèn)兩個(gè)不同字符集庫(kù)進(jìn)行數(shù)據(jù)校驗(yàn)時(shí),連接采用表示范圍更大的字符集。比如我們常用的字符集表示范圍如下:
Latin<gb2312<gbk<utf8
附:mysql客戶端與服務(wù)器通信時(shí)字符集編碼轉(zhuǎn)換流程
相關(guān)參數(shù):
– character_set_client:客戶端來(lái)源數(shù)據(jù)使用的字符集
– character_set_connection:連接層字符集
– character_set_results:查詢結(jié)果字符集
– character_set_database:當(dāng)前選中數(shù)據(jù)庫(kù)的默認(rèn)字符集
– character_set_system:系統(tǒng)元數(shù)據(jù)(字段名等)字符集
1.客戶端請(qǐng)求服務(wù)器
1)將client的字符集轉(zhuǎn)為connection字符集
2)將connection字符集轉(zhuǎn)為DB內(nèi)部的字符集
2.服務(wù)器返回結(jié)果給客戶端
1)將DB內(nèi)部字符集轉(zhuǎn)為connection字符集
2)將connection字符集轉(zhuǎn)為character_set_results字符集
3.設(shè)置字符集命令:set names 字符編碼
指定客戶端與服務(wù)器通信的字符集,包括請(qǐng)求與返回。
SET NAMES 'x' 等價(jià)于:
SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;
附圖:
相關(guān)文章
MySQL全文索引、聯(lián)合索引、like查詢、json查詢速度哪個(gè)快
這篇文章主要介紹了MySQL全文索引、聯(lián)合索引、like查詢、json查詢速度大比拼,通過(guò)實(shí)例代碼截圖的形式給大家介紹的非常詳細(xì),具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-02-02關(guān)于MySQL主從復(fù)制的幾種復(fù)制方式總結(jié)
這篇文章主要給大家介紹了關(guān)于MySQL主從復(fù)制的幾種復(fù)制方式,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-08-08MySQL查詢隨機(jī)數(shù)據(jù)的4種方法和性能對(duì)比
從MySQL隨機(jī)選取數(shù)據(jù)也是我們最常用的一種發(fā)發(fā),其最簡(jiǎn)單的辦法就是使用”O(jiān)RDER BY RAND()”,本文介紹了包括ORDER BY RAND()的4種獲取隨機(jī)數(shù)據(jù)的方法,并分析了各自的優(yōu)缺點(diǎn)。2014-04-04