MySQL資料匯入匯出亂碼問題

狗福發表於2017-12-10
MySql

場景

  • 程式使用gbk編碼，表使用的是latin1編碼，而我再一次倒入資料的操作中使用了utf8的終端，指定–default-character-set=`latin1`倒入的資料是亂碼，而後來將終端換成gbk之後酒倒入成功了
  • 通過變換插入資料的終端，模擬我們平常需要倒入資料的終端
  • 通過變更查詢資料的終端，來模擬我們程式的查詢操作
  • default-character-set變更能夠正確的讀取中文字元

測試環境

  • mysql server和Linux是utf8的字符集
  • 使用xshell作為終端進行輸入
  • 建立一張表儲存字符集是latin1
  • 使用mysqlclient 進行插入和查詢，檢視查詢到的資料是否正確

實驗步驟

  1. 使用mysqlclient，–default-character-set=`latin1` 這個選測進行測試，看看他到底改變了那些字符集，如下圖所示 
    
[root@5kh4z42 goufu]#  mysql -u superdba -padmin -S /tmp/mysql3443.sock   -e `show variables like "%char%"`;

+--------------------------+----------------------------------------------+

| Variable_name            | Value                                        |

+--------------------------+----------------------------------------------+

| character_set_client     | utf8                                         |

| character_set_connection | utf8                                         |

| character_set_database   | utf8                                         |

| character_set_filesystem | binary                                       |

| character_set_results    | utf8                                         |

| character_set_server     | utf8                                         |

| character_set_system     | utf8                                         |

| character_sets_dir       | /usr/local/xywy/mysql-5.5.38/share/charsets/ |

+--------------------------+----------------------------------------------+

[root@5kh4z42 goufu]#  mysql -u superdba -padmin -S /tmp/mysql3443.sock  --default-character-set=`latin1` -e `show variables like "%char%"`;

+--------------------------+----------------------------------------------+

| Variable_name            | Value                                        |

+--------------------------+----------------------------------------------+

| character_set_client     | latin1                                       |

| character_set_connection | latin1                                       |

| character_set_database   | utf8                                         |

| character_set_filesystem | binary                                       |

| character_set_results    | latin1                                       |

| character_set_server     | utf8                                         |

| character_set_system     | utf8                                         |

| character_sets_dir       | /usr/local/xywy/mysql-5.5.38/share/charsets/ |

+--------------------------+----------------------------------------------+

  2. 這裡可以看出具體的client和connection與results是取決於client的，預設是和檔案系統統一的
  3. 建立一張latin1字符集的表進行測試，看看mysql如何對字符集進行轉換，老的或者奇葩業務如何使用latin1儲存中文 
    CREATE TABLE `aa` (

`id` int(11) DEFAULT NULL,

`name` varchar(100) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

  4. 插入測試資料 
    終端切換為UTF8字符集，執行命令
mysql -u superdba -padmin -S /tmp/mysql.sock  --default-character-set=`latin1` -e `insert into  test.aa values(1,"啊啊")`;
終端切換為GBK字符集，執行命令
mysql -u superdba -padmin -S /tmp/mysql.sock  --default-character-set=`latin1` -e `insert into  test.aa values(2,"啊啊")`;
終端切換為UTF8字符集，執行命令
mysql -u superdba -padmin -S /tmp/mysql.sock   -e `insert into  test.aa values(3,"啊啊")`;
終端切換為GBK字符集，執行命令
mysql -u superdba -padmin -S /tmp/mysql.sock   -e `insert into  test.aa values(4,"啊啊")`;

  5. 查詢資料 
    終端切換為UTF8字符集，執行命令
[root@5kh4z42 goufu]# mysql -u superdba -padmin -S /tmp/mysql3443.sock  --default-character-set=`latin1` -e `select * from test.aa`;

+------+--------+

| id   | name   |

+------+--------+

|    1 | 啊啊 |

|    2 | °¡°¡   |

|    3 | ????   |

|    4 | ??     |

+------+--------+

終端切換為GBK字符集，執行命令

[root@5kh4z42 goufu]# mysql -u superdba -padmin -S /tmp/mysql3443.sock  --default-character-set=`latin1` -e `select * from test.aa`;

+------+--------+

| id   | name   |

+------+--------+

|    1 | 鍟婂晩 |

|    2 | 啊啊   |

|    3 | ????   |

|    4 | ??     |

+------+--------+

終端切換為UTF8字符集，執行命令

[root@5kh4z42 goufu]# mysql -u superdba -padmin -S /tmp/mysql3443.sock   -e `select * from test.aa`;

+------+----------------+

| id   | name           |

+------+----------------+

|    1 | å•Šå•Š         |

|    2 | °¡°¡           |

|    3 | ????           |

|    4 | ??             |

+------+----------------+

終端切換為GBK字符集，執行命令

[root@5kh4z42 goufu]# mysql -u superdba -padmin -S /tmp/mysql3443.sock   -e `select * from test.aa`;

+------+----------------+

| id   | name           |

+------+----------------+

|    1 | 氓鈥⑴犆モ€⑴?        |

|    2 | 擄隆擄隆           |

|    3 | ????           |

|    4 | ??             |

+------+----------------+

  6. 排查問題 
    檢視資料庫底層儲存的值，執行如下命令

[root@5kh4z42 goufu]# mysql -u superdba -padmin -S /tmp/mysql3443.sock   -e `select id,hex(name) from test.aa`;

+------+--------------+

| id   | hex(name)    |

+------+--------------+

|    1 | E5958AE5958A |

|    2 | B0A1B0A1     |

|    3 | 3F3F3F3F     |

|    4 | 3F3F         |

+------+--------------+

    • 問題到這裡其實有一些清晰了，結合我們之前的只是utf8儲存漢字是3位元組儲存，即2^8^3，換算成十六進位制就是2^4^2^3即6個16位數表示，所以`啊` 對應的編碼是%E5%95%8A，同理算出並驗證GBK編碼的`啊` 的編碼是%B0%A1，如我們所看到的3F其實就是?的編碼，urf8和gbk是一樣的

    • 這裡結合之前的只是，得知latin1是單位元組編碼，如果在儲存的時候他識別不了會按照單自己儲存，所以將他儲存的二進位制碼交給其他的字符集就可以進行復原，試驗中換成了ascii碼得到的結果也是一樣的，這可能就是單位元組碼的特性。
  7. 既然單位元組存在這種轉換規律那麼gbk和utf8 之間是怎樣進行轉換的呢

    • 首先，建立一張表結構如下

      create table cc (

id int ,

name varchar(100),

terminal varchar(100),

`client` varchar(100)

) charset=gbk;

    • 然後，我們分別使用utf8終端執行如下命令

      mysql -u superdba -pguzaneyR@cj7M6m -S /tmp/mysql3443.sock  --default-character-set=`gbk` -e `insert into  test_liuyaxin.cc values(1,"啊啊","utf8","gbk")`;

mysql -u superdba -pguzaneyR@cj7M6m -S /tmp/mysql3443.sock   -e `insert into  test_liuyaxin.cc values(1,"啊啊","utf8","utf8")`;

    • 然後在gbk終端下執行命令

      mysql -u superdba -pguzaneyR@cj7M6m -S /tmp/mysql3443.sock  --default-character-set=`gbk` -e `insert into  test_liuyaxin.cc values(2,"啊啊","gbk","gbk")`;

mysql -u superdba -pguzaneyR@cj7M6m -S /tmp/mysql3443.sock   -e `insert into  test_liuyaxin.cc values(2,"啊啊","gbk","utf8")`;

    • 最後使用utf8終端查詢結果

      mysql -u superdba -pguzaneyR@cj7M6m -S /tmp/mysql3443.sock  -e `select *,hex(name) from  test_liuyaxin.cc`;

+------+-----------+----------+--------+--------------+

| id   | name      | terminal | client | hex(name)    |

+------+-----------+----------+--------+--------------+

|    1 | 鍟婂晩    | utf8     | gbk    | E5958AE5958A |

|    1 | 啊啊      | utf8     | utf8   | B0A1B0A1     |

|    2 | 啊啊      | gbk      | gbk    | B0A1B0A1     |

|    2 | ????      | gbk      | utf8   | 3F3F3F3F     |

+------+-----------+----------+--------+--------------+

    • 發現了當終端–>client–>egine這個過程中，如果進行多次編碼轉換，最後就會是亂碼，而且無藥可救，因為在第二次編碼轉換的時候就已經失去了原來的字元的含義，而當這個過程中只有一次轉換的時候就算是亂碼，他仍然可以儲存下來，這樣在反向解碼的時候就可以還原，而單位元組碼如果出現不識別字元則會按照傳遞給他的編碼進行儲存，這樣在反向解碼的時候就可以識別出來

    • 這樣也可以看出來檔案系統應該是不參與解碼工作的，而是讓兩個程式（shell session和mysqlclient）進行編碼轉換，自己只是在中間將二進位制碼進行傳遞，其實mysql變數character_set_filesystem的值為binary時，表示檔案系統字符集只負責將傳遞二進位制資料
  8. 迴歸正題
    • 既然說到匯入匯出的亂碼問題，說到現在，其實解決這個問題的根源就在於，當你想吧備份檔案進行恢復的時候，首先你需要能夠正確的識別檔案，即你傳到伺服器上的檔案不是亂碼，而且要與你程式所使用的字符集一致，而你所使用的倒入mysql 資料庫的client 的字符集要和你底層的表的字符集相同，這樣就不會出現亂碼，前提是需要你使用單位元組編碼
    • 當然，說這麼多，只是為了解釋這樣儲存的可能性，並且解答了疑問，但是並不是代表著推薦這麼做，對於資料庫，當然還是希望字符集進行統一，這樣也就生了很多的麻煩


相關文章