Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen

In diesem Artikel werden die Ursachen für verstümmelte MySQL-Zeichen und spezifische Lösungen ausführlich vorgestellt

Die Gründe für verstümmelte MySQL-Zeichen

Um zu verstehen, warum verstümmelte Zeichen angezeigt werden, müssen wir zunächst verstehen, welche Links vom Zeitpunkt der Initiierung einer Anfrage durch den Client bis zum Speichern der Daten durch MySQL und dem nächsten Abruf des Clients aus der Tabelle codiert sind /Dekodierungsverhalten. Um diesen Prozess besser zu erklären, hat der Blogger zwei Flussdiagramme erstellt, die den beiden Phasen der Ein- und Auszahlung entsprechen.

Kodierungskonvertierungsprozess zum Speichern in MySQL

Im Bild oben (rote Pfeile) gibt es drei Kodierungs-/Dekodierungsprozesse. Die drei roten Pfeile entsprechen jeweils: Client-Kodierung, MySQL-Server-Dekodierung und Konvertierung von Client-Kodierung in Tabellenkodierung. Das Terminal kann ein Bash, eine Webseite oder eine APP sein. In diesem Artikel gehen wir davon aus, dass Bash unser Terminal ist, das die Eingabe- und Anzeigeschnittstelle der Benutzerseite darstellt. Das Verhalten, das jedem Feld in der Abbildung entspricht, ist wie folgt:

• Verwenden Sie zur Eingabe die Eingabemethode im Terminal.

• Das Terminal konvertiert es in einen Binärstrom entsprechend der Zeichenkodierung

• Binärstrom wird über den MySQL-Client an den MySQL-Server übertragen

• Server dekodiert durch Zeichensatz- client

• Bestimmen Sie, ob der Zeichensatz-Client mit dem Zeichensatz der Zieltabelle konsistent ist

• Wenn sie inkonsistent sind, führen Sie ein Zeichen aus Kodierungskonvertierung vom Client-Zeichensatz zum Tabellenzeichensatz

• Speichern Sie den konvertierten Zeichenkodierungs-Binärstrom in einer Datei

Der Kodierungskonvertierungsprozess des Abrufens Daten aus der MySQL-Tabelle

Das obige Bild zeigt drei Kodierungs-/Dekodierungsprozesse (rote Pfeile). Die drei roten Pfeile in der obigen Abbildung entsprechen jeweils: Anzeige der Client-Dekodierung, MySQL-Server-Kodierung gemäß Zeichensatz-Client und Konvertierung der Tabellenkodierung in die Zeichensatz-Client-Kodierung.

• Binärdatenstrom aus Datei lesen

• Dekodieren mit Tabellenzeichensatzkodierung

• Daten konvertieren zur Kodierung des Zeichensatz-Clients

• Verwenden Sie den Zeichensatz-Client, um ihn in einen Binärstrom zu kodieren

• Der Server wird an den übermittelt Netzwerk über das Netzwerk Der Remote-Client

• Der Client zeigt die Abfrageergebnisse anhand der in Bash konfigurierten Zeichenkodierung an

Der Grund, warum MySQL verstümmelt ist

1. Die Codierung der entsprechenden Links beim Ein- und Auszahlen ist inkonsistent
Es ist offensichtlich, dass dies zu verstümmelten Codes führt. Wir nummerieren die Zeichensätze, die in den drei Kodierungs- und Dekodierungsstufen der Speicherstufe verwendet werden, als C1, C2 und C3 (von links nach rechts in Abbildung 1 werden die drei Zeichensätze, die in der Abrufstufe verwendet werden, der Reihe nach als C1' nummeriert); , C2', C3' (von links nach rechts). Beim Speichern verwendet Bash C1 dann die UTF-8-Codierung, aber beim Herausnehmen verwenden wir das Windows-Terminal (Standard ist GBK-Codierung), sodass das Ergebnis mit ziemlicher Sicherheit verstümmelt ist. Oder beim Speichern in MySQL werden die festgelegten Namen utf8(C2) verwendet, aber beim Herausnehmen werden die festgelegten Namen gbk(C2') verwendet, dann muss das Ergebnis verstümmelt sein

2. Drei Schritte in Ein einzelner Prozess Die Codierung der Schritte ist inkonsistent
Das heißt, unter den drei Schritten in der gleichen Richtung in einem der obigen Bilder, solange die Codierung von zwei oder mehr Schritten inkonsistent ist, treten Codierungs- und Decodierungsfehler auf kann auftreten. Wenn keine verlustfreie Kodierungskonvertierung zwischen den beiden verschiedenen Zeichensätzen durchgeführt werden kann (siehe unten im Detail), werden mit Sicherheit verstümmelte Zeichen angezeigt. Beispiel: Unsere Shell ist UTF8-codiert, der Zeichensatz-Client von MySQL ist als GBK konfiguriert und die Tabellenstruktur ist Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen = utf8, dann wird es zweifellos verstümmelte Zeichen geben.
Hier zeigen wir einfach diese Situation

master [localhost] {msandbox} (test) > create table Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_test_utf8 (id int primary key auto_increment, char_col varchar(50)) Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen = 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 Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_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 Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_test_utf8;
+----+----------------+----------+
| id | hex(char_col)  | char_col |
+----+----------------+----------+
|  1 | E6B6933FE69E83 | �?��        |
+----+----------------+----------+
1 row in set (0.01 sec)

Informationen zur MySQL-Kodierung/-Dekodierung

Da Systeme gemäß Binärströmen übertragen werden, wird diese Zeichenfolge direkt übertragen. Speichern Sie den Binärstrom einfach direkt in die Tabellendatei. Warum müssen wir vor dem Speichern zwei Kodierungs- und Dekodierungsvorgänge durchführen?

• Der Grund für die Client-zu-Server-Kodierung und -Dekodierung besteht darin, dass MySQL eine Syntax- und lexikalische Analyse des eingehenden Binärstroms durchführen muss. Wenn wir keine Codierungsanalyse und -überprüfung durchführen, können wir nicht einmal wissen, ob der eingehende Binärstrom eine Einfügung oder ein Update ist.

• Die Codierung und Decodierung von File to Engine besteht darin, die Wortsegmentierungssituation im Binärstrom zu kennen. Um ein einfaches Beispiel zu geben: Wir möchten die ersten beiden Zeichen eines bestimmten Felds aus der Tabelle abrufen und eine Anweisung der Form „select left(col,2) from table“ ausführen. Die Speicher-Engine liest den Wert der Spalte aus Datei. E4B8ADE69687. Wenn wir diesen Wert zu diesem Zeitpunkt gemäß GBK in die drei Wörter E4B8, ADE6 und 9687 aufteilen, sollte der an den Client zurückgegebene Wert E4B8ADE6 sein zurückgegeben werden sollte. Es ist ersichtlich, dass, wenn die Daten aus der Datendatei gelesen werden und keine Kodierung oder Dekodierung durchgeführt wird, Operationen auf Zeichenebene nicht innerhalb der Speicher-Engine ausgeführt werden können.

---

关于错进错出

在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单个字符编码后，按照字节数的不同，取值范围如下表：

由于UTF-8编码能表示的字符数量远超GBK。那么我们很容易就能找到一个从UTF8到GBK的有损编码转换。我们用字符映射器（见下图）找出了一个明显就不在GBK编码表中的字符，尝试存入到GBK编码的表中。并再次取出查看有损转换的行为
字符信息具体是：ਅ GURMUKHI LETTER A Unicode: U+0A05, UTF-8: E0 A8 85

在MySQL中存储的具体情况如下：

master [localhost] {msandbox} (test) > create table Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_test_gbk (id int primary key auto_increment, char_col varchar(50)) Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen = 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 Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_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 Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_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 Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_test (id int primary key auto_increment, char_col varchar(50)) Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen = 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 Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_test (char_col) values ('中文');
Query OK, 1 row affected (0.01 sec)

master [localhost] {msandbox} (test) > select id,hex(char_col),char_col from Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_test;
+----+---------------+----------+
| id | hex(char_col) | char_col |
+----+---------------+----------+
|  2 | E4B8ADE69687  | 中文     |
+----+---------------+----------+
2 rows in set (0.00 sec)

具体流程图如下。可见在被MySQL Server接收到以后实际上已经发生了编码不一致的情况。但是由于Latin1字符集对于自己表述范围外的字符不会做任何处理，而是保留原值。这样的行为也使得错进错出成为了可能。

如何避免乱码

理解了上面的内容，要避免乱码就显得很容易了。只要做到“三位一体”，即客户端，MySQL character-set-client，table Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen三个字符集完全一致就可以保证一定不会有乱码出现了。而对于已经出现乱码，或者已经遭受有损转码的数据，如何修复相对来说就会有些困难。下一节我们详细介绍具体方法。

如何修复已经编码损坏的数据

在介绍正确方法前，我们先科普一下那些网上流传的所谓的“正确方法”可能会造成的严重后果。

错误方法一

无论从语法还是字面意思来看：ALTER TABLE ... CHARSET=xxx 无疑是最像包治乱码的良药了！而事实上，他对于你已经损坏的数据一点帮助也没有，甚至连已经该表已经创建列的默认字符集都无法改变。我们看下面这个例子

master [localhost] {msandbox} (test) > show create table Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_test;
+--------------+--------------------------------+
| Table        | Create Table                   |
+--------------+--------------------------------+
| Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_test | CREATE TABLE `Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_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 Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_test Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen=gbk;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

master [localhost] {msandbox} (test) > show create table Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_test;
+--------------+--------------------------------+
| Table        | Create Table                   |
+--------------+--------------------------------+
| Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_test | CREATE TABLE `Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_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 Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_name [COLLATE collation_name];

而实际上，这句语法只适用于当前并没有乱码，并且不是通过错进错出的方法保存的表。。而对于已经因为错进错出而产生编码错误的表，则会带来更糟的结果。我们用一个实际例子来解释下，这句SQL实际做了什么和他会造成的结果。假设我们有一张编码是latin1的表，且之前通过错进错出存入了UTF-8的数据，但是因为通过terminal仍然能够正常显示。即上文错进错出章节中举例的情况。一段时间使用后我们发现了这个错误，并打算把表的字符集编码改成UTF-8并且不影响原有数据的正常显示。这种情况下使用alter table convert to character set会有这样的后果：

master [localhost] {msandbox} (test) > create table Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_test_latin1 (id int primary key auto_increment, char_col varchar(50)) Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen = 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 Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_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 Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_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 Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_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 Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_test_latin1;
+----+--------------------------------------------------------+-----------------------------+-----------------------+
| id | hex(char_col)                                          | char_col                    | char_length(char_col) |
+----+--------------------------------------------------------+-----------------------------+-----------------------+
|  1 | C3A8C2BFE284A2C3A6CB9CC2AFC3A4C2B8C2ADC3A6E28093E280A1 | 这是中文                |                    12 |
+----+--------------------------------------------------------+-----------------------------+-----------------------+
1 row in set (0.00 sec)

从这个例子我们可以看出，对于已经错进错出的数据表，这个命令不但没有起到“拨乱反正”的效果，还会彻底将数据糟蹋，连数据的二进制编码都改变了。

正确的方法一 Dump & Reload

这个方法比较笨，但也比较好操作和理解。简单的说分为以下三步：

1. 通过错进错出的方法，导出到文件

2. 用正确的字符集修改新表

3. 将之前导出的文件导回到新表中

还是用上面那个例子举例，我们用UTF-8将数据“错进”到latin1编码的表中。现在需要将表编码修改为UTF-8可以使用以下命令

shell> mysqldump -u root -p -d --skip-set-Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen --default-character-set=utf8 test Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_test_latin1 > data.sql
#确保导出的文件用文本编辑器在UTF-8编码下查看没有乱码
shell> mysql -uroot -p -e 'create table Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_test_latin1 (id int primary key auto_increment, char_col varchar(50)) Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen = 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 Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_test_latin1 MODIFY COLUMN char_col VARBINARY(50);
mysql> ALTER TABLE Lernen Sie, MySQL-verstümmelte Probleme in 10 Minuten zu verstehen und zu lösen_test_latin1 MODIFY COLUMN char_col varchar(50) character set utf8;

 以上就是10分钟学会理解和解决MySQL乱码问题的内容，更多相关内容请关注PHP中文网（www.php.cn）！