【複製】【編碼】MySQL複製中的編碼問題

標籤：des   style   blog   http   color   使用   os   io   

 編碼背景知識

Latin-1，全稱ISO 8859-1 Latin 1 對ASCII的拉丁語擴充 向下相容ASCII，其編碼範圍是0x00-0xFF，0x00-0x7F之間完全和ASCII一致，0x80-0x9F之間是控制字元，0xA0-0xFF之間是文字元號。

ASCII   沒啥好說的 0x00 – 0x7f 地球人都會查表

GBK：查表 http://ff.163.com/newflyff/gbk-list/

UTF8編碼:Unicode表的一種落地實現 (包括傳輸<大小端>，位元組儲存，解釋等)

記住 ： GBK 如果想轉換成UTF8 ， 是沒有二進位規律的，一般都是通過 
GBK 查表  --à Unicode對應坑 -à 按照規則變成- : UTF8 。

Unicode:記住在Unicode字元表中，每種語言的每個文字都佔了一個坑 。Unicode編碼由兩個位元組(常用夠了)或四個位元組(甲骨文之類)來表示。

UNICODE到UTF-8的轉換就是

1 先確定編碼所需要的UTF-8編碼位元組數

2 然後用UNICODE編碼位從低位到高位依次按規則填入空位，不足的高位以0補充

 

1. 錯誤‘Incorrect string value: ‘\xE7\xB3\xBB\xE7\xBB\x9F...‘

Last_Errno: 1366Last_Error: Error ‘Incorrect string value: ‘\xE7\xB3\xBB\xE7\xBB\x9F...‘ for column ‘content‘ at row 1‘ on query. Default database: ‘ibank‘. Query: ‘update bk_web_close set createTime=‘2014-06-14 14:30:30‘, updateTime=‘2014-08-14 21:02:31‘, version=8, content=‘<div>\r\n 系統升級中，請暫停使用。</div>\r\n‘, status=1 where id=1 and version=7‘

查看binlog：

update bk_web_close set createTime=‘2014-06-14 14:30:30‘, updateTime=‘2014-08-14 21:02:31‘, version=8, content=‘<div>\r\n <E7><B3><BB><E7><BB><9F><E5><8D><87><E7><BA><A7><E4><B8><AD><EF><BC><8C><E8><AF><B7><E6><9A><82><E5><81><9C><E4><BD><BF><E7><94><A8><E3><80><82></div>\r\n‘, status=1 where id=1 and version=7/*!*/;

 

2. 類似錯誤

Incorrect string value: ‘\xB2\xE1‘ for column ‘title‘ at row 1‘ on query. Default database: ‘db_puyitou‘. Query: ‘update CM_NEWS set htmlstatus=1, htmlname=0x3230313430373237323435302E68746D, templateid=25, columnid=7, userid=0x61646D696E, subcolumnid=12, title=0xD7A2B2E1, keyword="", description="", source="", content=0x3C703E3C7370616E207374796C653D22464F4E542D46414D494C593A20CBCECCE52C53696D53756E3B20434F4C4F523A20233539353935393B20464F4E542D53495A453A2031347078223EA2D9203C2F7370616E3E3C7370616E207374796C653D22464F4E542D46414D494C593A20CBCECCE52C53696D53756E3B20434F4C4F523A20233539353935393B20464F4E542D53495A453A2031347078223EB5E3BBF7D3D2C9CFBDC7A1BED7A2B2E1A1BFA3ACB8F9BEDDCCE1CABECCEED0B4D7A2B2E1D0C5CFA2A1A33C2F7370616E3E3C2F703E3C703E3C7370616E207374796C653D22464F4E542D46414D494C593A20CBCECCE52C53696D53756E3B20434F4C4F523A20233539353935393B20464F4E542D53495A453A2031347078223ED7A2A3BAD3C3BBA7C3FBD0EBCEAACAD6BBFABAC5A1A33C2F7370616E3E3C2F703E3C70207374796C653D22544558542D414C49474E3A2063656E7465722

查看binlog

update CM_NEWS set htmlstatus=1, htmlname=0x3230313430373237323435302E68746D, templateid=25, columnid=7, userid=0x61646D696E, subcolumnid=12, title=0xD7A2B2E1, keyword="", description="", source="", content=0x3C703E3C7370616E207374796C653D22464F4E542D46414D494C593A20CBCECCE52C53696D53756E3B20434F4C4F523A20233539353935393B20464F4E542D53495A453A2031347078223EA2D9203C2F7370616E3E3C7370616E207374796C653D22464F4E542D46414D494C593A20CBCECCE52C53696D53756E3B20434F4C4F523A20233539353935393B20464F4E542D53495A453A2031347078223EB5E3BBF7D3D2C9CFBDC7A1BED7A2B2E1A1BFA3ACB8F9BEDDCCE1CABECCEED0B4D7A2B2E1D0C5CFA2A1A33C2F7370616E3E3C2F703E3C703E3C7370616E207374796C653D22464F4E542D46414D494C593A20CBCECCE52C53696D53756E3B20434F4C4F523A20233539353935393B20464F4E542D53495A453A2031347078223ED7A2A3BAD3C3BBA7C3FBD0EBCEAACAD6BBFABAC5A1A33C2F7370616E3E3C2F703E3C70207374796C653D22544558542D414C49474E3A2063656E746572223E3C7370616E207374796C653D22464F4E542D46414D494C593A20CBCECCE52C53696D53756E3B20434F4C4F523A20233539353935393B20464F4E542D53495A453A2031347078223E3C2F7370616E3E3C2F703E3C70207374796C653D22544558542D414C49474E3A2063656E746572223E3C7370616E207374796C653D22464F4E542D46414D494C593A20CBCECCE52C53696D53756E3B20434F4C4F523A20233539353935393B20464F4E542D53495A453A2031347078223E3C2F7370616E3E3C2F703E3C70207374796C653D22544558542D414C49474E3A2063656E746572223E3C7370616E207374796C653D22464F4E542D46414D494C593A20CBCECCE52C53696D53756E223E3C7370616E207374796C653D22464F4E542D46414D494C593A20CEA2C8EDD1C5BADA2C4D6963726F736F66742059614865693B20434F4C4F523A20233539353935393B20464F4E542D53495A453A2031347078223E3C696D67207374796C653D2257494454483A2036303070783B204845494748543A20333133707822207469746C653D2233332E504E472220626F726465723D223022206873706163653D223022207372633D22687474703A2F2F3230332E3139352E3230322E37353A383038302F636D2F6A732F75656469746F72315F335F352F6A73702F75656469746F72696D67732F32303134303830372F393431313430373337373631353530382E504E47222077696474683D2236303022206865696768743D22333133222F3E3C2F7370616E3E266E6273703B3C2F7370616E3E3C2F703E3C703E3C7370616E207374796C653D22464F4E542D46414D494C593A20CBCECCE52C53696D53756E3B20434F4C4F523A20233539353935393B20464F4E542D53495A453A2031347078223EA2DA20D7A2B2E1CDEAB3C9A3ACC7EBB8F9BEDDCCE1CABEBDF8D0D0D2F8D0D0BFA8C8CFD6A4A1A33C2F7370616E3E3C2F703E, pubdate=‘2014-08-07 00:00:00‘, begintime=‘2014-07-27 00:00:00‘, endtime=NULL, commend=1 where id=50/*!*/;

主庫中16進位檔案對應內容

<p><span style="FONT-FAMILY: 宋體,SimSun; COLOR: #595959; FONT-SIZE: 14px">① </span><span style="FONT-FAMILY: 宋體,SimSun; COLOR: #595959; FONT-SIZE: 14px">點擊右上方【註冊】，根據提示填寫註冊資訊。</span></p><p><span style="FONT-FAMILY: 宋體,SimSun; COLOR: #595959; FONT-SIZE: 14px">註：使用者名稱須為手機號。</span></p><p style="TEXT-ALIGN: center"><span style="FONT-FAMILY: 宋體,SimSun; COLOR: #595959; FONT-SIZE: 14px"></span></p><p style="TEXT-ALIGN: center"><span style="FONT-FAMILY: 宋體,SimSun; COLOR: #595959; FONT-SIZE: 14px"></span></p><p style="TEXT-ALIGN: center"><span style="FONT-FAMILY: 宋體,SimSun"><span style="FONT-FAMILY: 微軟雅黑,Microsoft YaHei; COLOR: #595959; FONT-SIZE: 14px"><img style="WIDTH: 600px; HEIGHT: 313px" title="33.PNG" border="0" hspace="0" src="http://203.195.202.75:8080/cm/js/ueditor1_3_5/jsp/ueditorimgs/20140807/9411407377615508.PNG" width="600" height="313"/></span>&nbsp;</span></p><p><span style="FONT-FAMILY: 宋體,SimSun; COLOR: #595959; FONT-SIZE: 14px">② 註冊完成，請根據提示進行銀行卡認證。</span></p>

 

上述兩例中binlog中內容均為主庫實際內容的utf8編碼對應的16進位數。