程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL中湧現亂碼成績的最終處理寶典

MySQL中湧現亂碼成績的最終處理寶典

編輯：MySQL綜合教程

MySQL中湧現亂碼成績的最終處理寶典。本站提示廣大學習愛好者：（MySQL中湧現亂碼成績的最終處理寶典）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL中湧現亂碼成績的最終處理寶典正文

MySQL湧現亂碼的緣由

要懂得為何會湧現亂碼，我們就先要懂得：從客戶端提議要求，到MySQL存儲數據，再到下次從表取回客戶真個進程中，哪些環節會有編碼/解碼的行動。為了更好的說明這個進程，博主制造了兩張流程圖，分離對應存入和掏出兩個階段。
存入MySQL閱歷的編碼轉換進程

2015819115042380.png (1358×395)

上圖中有3次編碼/解碼的進程（白色箭頭）。三個白色箭頭分離對應：客戶端編碼，MySQL Server解碼，Client編碼向表編碼的轉換。個中Terminal可所以一個Bash，一個web頁面又或許是一個APP。本文中我們假定Bash是我們的Terminal，即用戶真個輸出和展現界面。圖中每個框格對應的行動以下：

在terminal中應用輸出法輸出
terminal依據字符編碼轉換成二進制流
二進制流暢過MySQL客戶端傳輸到MySQL Server
Server經由過程character-set-client解碼
斷定character-set-client和目的表的charset能否分歧
假如紛歧致則停止一次從client-charset到table-charset的一次字符編碼轉換
將轉換後的字符編碼二進制流存入文件中

從MySQL表中掏出數據閱歷的編碼轉換進程

2015819115113558.png (1358×395)

上圖有3次編碼/解碼的進程（白色箭頭）。上圖中三個白色箭頭分離對應：客戶端解碼展現，MySQL Server依據character-set-client編碼，表編碼向character-set-client編碼的轉換。

從文件讀出二進制數據流
用表字符集編碼停止解碼
將數據轉換為character-set-client的編碼
應用character-set-client編碼為二進制流
Server經由過程收集傳輸到遠端client
client經由過程bash設置裝備擺設的字符編碼展現查詢成果

形成MySQL亂碼的緣由
1. 存入和掏出時對應環節的編碼紛歧致

這個會形成亂碼是不言而喻的。我們把存入階段的三次編解碼應用的字符集編號為C1,C2,C3（圖一從左到右）；掏出時的三個字符集順次編號為C1',C2',C3'（從左到右）。那末存入的時刻bash C1用的是UTF-8編碼，掏出的時刻,C1'我們卻應用了windows終端（默許是GBK編碼），那末成果簡直必定是亂碼。又或許存入MySQL的時刻set names utf8(C2)，而掏出的時刻卻應用了set names gbk(C2')，那末成果也必定是亂碼
2. 單個流程中三步的編碼紛歧致

即下面隨意率性一幅圖中的同偏向的三步中，只需兩步或許兩部以上的編碼有紛歧致就有能夠湧現編解碼毛病。假如差別的兩個字符集之間沒法停止無損編碼轉換（下文會具體引見），那末就必定會湧現亂碼。例如：我們的shell是UTF8編碼，MySQL的character-set-client設置裝備擺設成了GBK，而表構造卻又是charset=utf8，那末毫無疑問的必定會湧現亂碼。

這裡我們就簡略演示下這類情形：

  master [localhost] {msandbox} (test) > create table charset_test_utf8 (id int primary key auto_increment, char_col varchar(50)) charset = utf8;
  Query OK, 0 rows affected (0.04 sec)
   
  master [localhost] {msandbox} (test) > set names gbk;
  Query OK, 0 rows affected (0.00 sec)
   
  master [localhost] {msandbox} (test) > insert into charset_test_utf8 (char_col) values ('中文');
  Query OK, 1 row affected, 1 warning (0.01 sec)
   
  master [localhost] {msandbox} (test) > show warnings;
  +---------+------+---------------------------------------------------------------------------+
  | Level  | Code | Message                                  |
  +---------+------+---------------------------------------------------------------------------+
  | Warning | 1366 | Incorrect string value: '\xAD\xE6\x96\x87' for column 'char_col' at row 1 |
  +---------+------+---------------------------------------------------------------------------+
  1 row in set (0.00 sec)
   
  master [localhost] {msandbox} (test) > select id,hex(char_col),char_col from charset_test_utf8;
  +----+----------------+----------+
  | id | hex(char_col) | char_col |
  +----+----------------+----------+
  | 1 | E6B6933FE69E83 | ????    |
  +----+----------------+----------+
  1 row in set (0.01 sec)

關於MySQL的編/解碼

既然體系之間是依照二進制流停止傳輸的，那直接把這串二進制流直接存入表文件就好啦。為何在存儲之前還要停止兩次編解碼的操作呢？

Client to Server的編解碼的緣由是MySQL須要對傳來的二進制流做語法和詞法解析。假如不做編碼解析和校驗，我們乃至沒法曉得傳來的一串二進制流是insert照樣update。
File to Engine的編解碼是為曉得二進制流內的分詞情形。舉個簡略的例子：我們想要從內外掏出某個字段的前兩個字符，履行了一句形如select left(col,2) from table的語句，存儲引擎從文件讀入該column的值是E4B8ADE69687。那末這個時刻假如我們依照GBK把這個值朋分成E4B8,ADE6,9687三個字，並那末前往客戶真個值就應當是E4B8ADE6；假如依照UTF8朋分成E4B8AD,E69687，那末就應當前往E4B8ADE69687兩個字。可見，假如在從數據文件讀入數據後，不停止編解碼的話在存儲引擎外部是沒法停止字符級其余操作的。
關於錯進錯出

在MySQL中最多見的亂碼成績的原由就是把錯進錯入迷話。所謂的錯進錯出就是，客戶端(web或shell)的字符編碼和終究表的字符編碼格局分歧，然則只需包管存和取兩次的字符集編碼分歧就依然可以或許取得沒有亂碼的輸入的這類景象。然則，錯進錯出其實不是關於隨意率性兩種字符集編碼的組合都是有用的。我們假定客戶真個編碼是C，MySQL表的字符集編碼是S。那末為了可以或許錯進錯出，須要知足以下兩個前提：

MySQL吸收要求時，從C編碼後的二進制流在被S解碼時可以或許無損
MySQL前往數據是，從S編碼後的二進制流在被C解碼時可以或許無損

編碼無損轉換

那末甚麼是有損轉換，甚麼是無損轉換呢？假定我們要把用編碼A表現的字符X，轉化為編碼B的表現情勢，而編碼B的字形集中並沒有X這個字符，那末此時我們就稱這個轉換是有損的。那末，為何會湧現兩個編碼所能表現字符聚集的差別呢？假如年夜家看過博主之前的那篇非常鐘弄清字符集和字符編碼，或許對字符編碼有基本懂得的話，就應當曉得每一個字符集所支撐的字符數目是無限的，而且各個字符集涵蓋的文字之間存在差別。UTF8和GBK所能表現的字符數目規模以下：

GBK單個字符編碼後的取值規模是：8140 - FEFE 個中不包含**7E，總共字符數在27000閣下
UTF8單個字符編碼後，依照字節數的分歧，取值規模以下表：

2015819115139208.png (1148×316)

因為UTF-8編碼能表現的字符數目遠超GBK。那末我們很輕易就可以找到一個從UTF8到GBK的有損編碼轉換。我們用字符映照器（見下圖）找出了一個顯著就不在GBK編碼表中的字符，測驗考試存入到GBK編碼的表中。並再次掏出檢查有損轉換的行動
字符信息詳細是：? GURMUKHI LETTER A Unicode: U+0A05, UTF-8: E0 A8 85

2015819115201942.png (1146×830)

在MySQL中存儲的詳細情形以下：

  master [localhost] {msandbox} (test) > create table charset_test_gbk (id int primary key auto_increment, char_col varchar(50)) charset = gbk;
  Query OK, 0 rows affected (0.00 sec)
   
  master [localhost] {msandbox} (test) > set names utf8;
  Query OK, 0 rows affected (0.00 sec)
   
  master [localhost] {msandbox} (test) > insert into charset_test_gbk (char_col) values ('?');
  Query OK, 1 row affected, 1 warning (0.01 sec)
   
  master [localhost] {msandbox} (test) > show warnings;
  +---------+------+-----------------------------------------------------------------------+
  | Level  | Code | Message                                |
  +---------+------+-----------------------------------------------------------------------+
  | Warning | 1366 | Incorrect string value: '\xE0\xA8\x85' for column 'char_col' at row 1 |
  +---------+------+-----------------------------------------------------------------------+
  1 row in set (0.00 sec)
   
  master [localhost] {msandbox} (test) > select id,hex(char_col),char_col,char_length(char_col) from charset_test_gbk;
  +----+---------------+----------+-----------------------+
  | id | hex(char_col) | char_col | char_length(char_col) |
  +----+---------------+----------+-----------------------+
  | 1 | 3F      | ?    |           1 |
  +----+---------------+----------+-----------------------+
  1 row in set (0.00 sec)

失足的部門是在編解碼的第3步時產生的。詳細見下圖

可見MySQL外部假如沒法找到一個UTF8字符所對應的GBK字符時，就會轉換成一個毛病mark（這裡是問號）。而每一個字符集在法式完成的時刻外部都商定了當湧現這類情形時的行動和轉換規矩。例如：UTF8中沒法找到對應字符時，假如不拋錯那末就將該字符調換成? (U+FFFD)

那末是否是任何兩種字符集編碼之間的轉換都是有損的呢？並不是如許，轉換能否有損取決於以下幾點：

被轉換的字符能否同時在兩個字符集中
目的字符集能否可以或許對不支撐字符，保存其原有表達情勢

關於第一點，適才曾經經由過程試驗來說明過了。這裡來說明下形成有損轉換的第二個身分。從適才的例子我們可以看到因為GBK在處置本身沒法表現的字符時的行動是：用毛病標識替換，即0x3F。而有些字符集（例如latin1）在碰到本身沒法表現的字符時，會保存原字符集的編碼數據，並跳過疏忽該字符進而處置前面的數據。假如目的字符集具有如許的特征，那末就可以夠完成這節最開端提到的錯進錯出的後果。

我們來看上面這個例子：

master [localhost] {msandbox} (test) > create table charset_test (id int primary key auto_increment, char_col varchar(50)) charset = latin1;
  Query OK, 0 rows affected (0.03 sec)
   
  master [localhost] {msandbox} (test) > set names latin1;
  Query OK, 0 rows affected (0.00 sec)
   
  master [localhost] {msandbox} (test) > insert into charset_test (char_col) values ('中文');
  Query OK, 1 row affected (0.01 sec)
   
  master [localhost] {msandbox} (test) > select id,hex(char_col),char_col from charset_test;
  +----+---------------+----------+
  | id | hex(char_col) | char_col |
  +----+---------------+----------+
  | 2 | E4B8ADE69687 | 中文   |
  +----+---------------+----------+
  2 rows in set (0.00 sec)

詳細流程圖以下。可見在被MySQL Server吸收到今後現實上曾經產生了編碼紛歧致的情形。然則因為Latin1字符集關於本身表述規模外的字符不會做任何處置，而是保存原值。如許的行動也使得錯進錯出成了能夠。

若何防止亂碼

懂得了下面的內容，要防止亂碼就顯得很輕易了。只需做到“三位一體”，即客戶端，MySQL character-set-client，table charset三個字符集完整分歧便可以包管必定不會有亂碼湧現了。而關於曾經湧現亂碼，或許曾經遭遇有損轉碼的數據，若何修復絕對來講就會有些艱苦。下一節我們具體引見詳細辦法。
若何修復曾經編碼破壞的數據

在引見准確辦法前，我們先科普一下那些網下流傳的所謂的“准確辦法”能夠會形成的嚴重效果。
毛病辦法一

不管從語法照樣字面意思來看：ALTER TABLE ... CHARSET=xxx 無疑是最像包治亂碼的良藥了！而現實上，他關於你曾經破壞的數據一點贊助也沒有，乃至連曾經該表曾經創立列的默許字符集都沒法轉變。我們看上面這個例子

  master [localhost] {msandbox} (test) > show create table charset_test;
  +--------------+--------------------------------+
  | Table    | Create Table          |
  +--------------+--------------------------------+
  | charset_test | CREATE TABLE `charset_test` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `char_col` varchar(50) DEFAULT NULL,
   PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |
  +--------------+--------------------------------+
  1 row in set (0.00 sec)
   
  master [localhost] {msandbox} (test) > alter table charset_test charset=gbk;
  Query OK, 0 rows affected (0.03 sec)
  Records: 0 Duplicates: 0 Warnings: 0
   
  master [localhost] {msandbox} (test) > show create table charset_test;
  +--------------+--------------------------------+
  | Table    | Create Table          |
  +--------------+--------------------------------+
  | charset_test | CREATE TABLE `charset_test` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `char_col` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
   PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=gbk |
  +--------------+--------------------------------+
  1 row in set (0.00 sec)

可見該語法牢牢修正了表的默許字符集，即只對今後創立的列的默許字符集發生影響，而對曾經存在的列和數據沒有變更。
毛病辦法二

ALTER TABLE … CONVERT TO CHARACTER SET … 的相較於辦法一來講殺傷力更年夜，由於從官方文檔的說明他的感化就是用於對一個表的數據停止編碼轉換。上面是文檔的一小段摘錄：

  To change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:

  ALTER TABLE tbl_name
  CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

而現實上，這句語法只實用於以後並沒有亂碼，而且不是經由過程錯進錯出的辦法保留的表。。而關於曾經由於錯進錯出而發生編碼毛病的表，則會帶來更糟的成果。

我們用一個現實例子來說明下，這句SQL現實做了甚麼和他會形成的成果。假定我們有一張編碼是latin1的表，且之前經由過程錯進錯出存入了UTF-8的數據，然則由於經由過程terminal依然可以或許正常顯示。即上文錯進錯出章節及第例的情形。一段時光應用後我們發明了這個毛病，並盤算把表的字符集編碼改成UTF-8而且不影響原稀有據的正常顯示。這類情形下應用alter table convert to character set會有如許的效果：

master [localhost] {msandbox} (test) > create table charset_test_latin1 (id int primary key auto_increment, char_col varchar(50)) charset = latin1;
  Query OK, 0 rows affected (0.01 sec)
   
  master [localhost] {msandbox} (test) > set names latin1;
  Query OK, 0 rows affected (0.00 sec)
   
  master [localhost] {msandbox} (test) > insert into charset_test_latin1 (char_col) values ('這是中文');
  Query OK, 1 row affected (0.01 sec)
   
  master [localhost] {msandbox} (test) > select id,hex(char_col),char_col,char_length(char_col) from charset_test_latin1;
  +----+--------------------------+--------------+-----------------------+
  | id | hex(char_col)      | char_col   | char_length(char_col) |
  +----+--------------------------+--------------+-----------------------+
  | 1 | E8BF99E698AFE4B8ADE69687 | 這是中文   |          12 |
  +----+--------------------------+--------------+-----------------------+
  1 row in set (0.01 sec)
   
  master [localhost] {msandbox} (test) > alter table charset_test_latin1 convert to character set utf8;
  Query OK, 1 row affected (0.04 sec)
  Records: 1 Duplicates: 0 Warnings: 0
   
  master [localhost] {msandbox} (test) > set names utf8;
  Query OK, 0 rows affected (0.00 sec)
   
  master [localhost] {msandbox} (test) > select id,hex(char_col),char_col,char_length(char_col) from charset_test_latin1;
  +----+--------------------------------------------------------+-----------------------------+-----------------------+
  | id | hex(char_col)                     | char_col          | char_length(char_col) |
  +----+--------------------------------------------------------+-----------------------------+-----------------------+
  | 1 | C3A8C2BFE284A2C3A6CB9CC2AFC3A4C2B8C2ADC3A6E28093E280A1 | è????ˉ??-?–?        |          12 |
  +----+--------------------------------------------------------+-----------------------------+-----------------------+
  1 row in set (0.00 sec)

從這個例子我們可以看出，關於曾經錯進錯出的數據表，這個敕令不只沒有起到“撥亂橫豎”的後果，還會完全將數據浪費，連數據的二進制編碼都轉變了。
准確的辦法一 Dump & Reload

這個辦法比擬笨，但也比擬好操作和懂得。簡略的說分為以下三步：

經由過程錯進錯出的辦法，導出到文件
用准確的字符集修正新表
將之前導出的文件導回到新表中

照樣用下面誰人例子舉例，我們用UTF-8將數據“錯進”到latin1編碼的表中。如今須要將表編碼修正為UTF-8可使用以下敕令

  shell> mysqldump -u root -p -d --skip-set-charset --default-character-set=utf8 test charset_test_latin1 > data.sql
  #確保導出的文件用文本編纂器在UTF-8編碼下檢查沒有亂碼
  shell> mysql -uroot -p -e 'create table charset_test_latin1 (id int primary key auto_increment, char_col varchar(50)) charset = utf8' test
  shell> mysql -uroot -p --default-character-set=utf8 test < data.sql

准確的辦法二 Convert to Binary & Convert Back

這類辦法比擬取巧，用的是將二進制數據作為中央數據的做法來完成的。因為，MySQL再將有編碼意義的數據流，轉換為無編碼意義的二進制數據的時刻其實不做現實的數據轉換。而從二進制數據准換為帶編碼的數據時，又會用目的編碼做一次編碼轉換校驗。經由過程這兩個特征就相當於在MySQL外部模仿了一次“錯出”，將亂碼“撥亂橫豎”了。

照樣用下面誰人例子舉例，我們用UTF-8將數據“錯進”到latin1編碼的表中。如今須要將表編碼修正為UTF-8可使用以下敕令

  mysql> ALTER TABLE charset_test_latin1 MODIFY COLUMN char_col VARBINARY(50);
  mysql> ALTER TABLE charset_test_latin1 MODIFY COLUMN char_col varchar(50) character set utf8;