Learn to understand and solve MySQL garbled problems in 10 minutes

This article will introduce in detail the causes of MySQL garbled characters and specific solutions

The reasons for MySQL garbled characters

To understand why garbled characters appear, we must first understand: from when the client initiates a request, to when MySQL stores the data, and then when the client is retrieved from the table next time, which links will have encoding/decoding behavior. In order to better explain this process, the blogger produced two flow charts, corresponding to the two stages of deposit and withdrawal.

Encoding conversion process when stored in MySQL

There are three encoding/decoding processes (red arrows) in the above figure. The three red arrows respectively correspond to: client encoding, MySQL Server decoding, and conversion from Client encoding to table encoding. The Terminal can be a Bash, a web page or an APP. In this article, we assume that Bash is our Terminal, which is the input and display interface of the user side. The behavior corresponding to each box in the figure is as follows:

• Use the input method in the terminal to input

• The terminal converts it into a binary stream according to the character encoding

• The binary stream is transmitted to MySQL Server through the MySQL client

• Server decodes through character-set-client

• Determine whether the character-set-client is consistent with the Learn to understand and solve MySQL garbled problems in 10 minutes of the target table

• If they are inconsistent, perform a character encoding conversion from client-Learn to understand and solve MySQL garbled problems in 10 minutes to table-Learn to understand and solve MySQL garbled problems in 10 minutes

• Save the converted character encoding binary stream into a file

The encoding conversion process of retrieving data from the MySQL table

The above figure has three encoding/decoding processes (red arrows). The three red arrows in the above figure respectively correspond to: client decoding display, MySQL Server encoding according to character-set-client, and table encoding to character-set-client encoding conversion.

• Read binary data stream from file

• Use table character set encoding to decode

• Convert data to character-set-client encoding

• Use character-set-client encoding to binary stream

• Server is transmitted to the network through the network Remote client

• The client displays the query results through the character encoding configured in bash

Cause of MySQL garbled characters

1. The coding of the corresponding links when depositing and withdrawing is inconsistent
It is obvious that this will cause garbled codes. We number the character sets used in the three encoding and decoding stages of the storage stage as C1, C2, and C3 (from left to right in Figure 1); the three character sets used in the retrieval stage are numbered as C1', C2', C3' (from left to right). to the right). Then when saving, bash C1 uses UTF-8 encoding, but when taking it out, we use the windows terminal (default is GBK encoding), so the result will almost certainly be garbled. Or when saving to MySQL, set names utf8(C2) is used, but when taking out, set names gbk(C2') is used, then the result must be garbled

2. Three steps in a single process The coding of steps is inconsistent
That is, among the three steps in the same direction in any of the above pictures, as long as the coding of two or more steps is inconsistent, coding and decoding errors may occur. If lossless encoding conversion cannot be performed between the two different character sets (described in detail below), then garbled characters will definitely appear. For example: our shell is UTF8 encoded, MySQL's character-set-client is configured as GBK, and the table structure is Learn to understand and solve MySQL garbled problems in 10 minutes=utf8, then there will undoubtedly be garbled characters.
Here we will simply demonstrate this situation

master [localhost] {msandbox} (test) > create table Learn to understand and solve MySQL garbled problems in 10 minutes_test_utf8 (id int primary key auto_increment, char_col varchar(50)) Learn to understand and solve MySQL garbled problems in 10 minutes = 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 Learn to understand and solve MySQL garbled problems in 10 minutes_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 Learn to understand and solve MySQL garbled problems in 10 minutes_test_utf8;
+----+----------------+----------+
| id | hex(char_col)  | char_col |
+----+----------------+----------+
|  1 | E6B6933FE69E83 | �?��        |
+----+----------------+----------+
1 row in set (0.01 sec)

About MySQL encoding/decoding

Since systems are transmitted according to binary streams, then directly convert this string of binary streams Just save it directly to the table file. Why do we need to perform two encoding and decoding operations before storing?

• The reason for the encoding and decoding of Client to Server is that MySQL needs to perform syntax and lexical analysis on the incoming binary stream. Without encoding analysis and verification, we can't even know whether the incoming binary stream is an insert or an update.

• File to Engine encoding and decoding is to know the word segmentation situation in the binary stream. To give a simple example: we want to retrieve the first two characters of a field from the table, and execute a statement of the form select left(col,2) from table. The storage engine reads the value of the column from the file. E4B8ADE69687. So at this time, if we divide this value into three words E4B8, ADE6, and 9687 according to GBK, the value returned to the client should be E4B8ADE6; if we divide it into E4B8AD and E69687 according to UTF8, then the two words E4B8ADE69687 should be returned. It can be seen that if the data is read from the data file, character-level operations cannot be performed inside the storage engine without encoding and decoding.

---

关于错进错出

在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 Learn to understand and solve MySQL garbled problems in 10 minutes_test_gbk (id int primary key auto_increment, char_col varchar(50)) Learn to understand and solve MySQL garbled problems in 10 minutes = 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 Learn to understand and solve MySQL garbled problems in 10 minutes_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 Learn to understand and solve MySQL garbled problems in 10 minutes_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 Learn to understand and solve MySQL garbled problems in 10 minutes_test (id int primary key auto_increment, char_col varchar(50)) Learn to understand and solve MySQL garbled problems in 10 minutes = 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 Learn to understand and solve MySQL garbled problems in 10 minutes_test (char_col) values ('中文');
Query OK, 1 row affected (0.01 sec)

master [localhost] {msandbox} (test) > select id,hex(char_col),char_col from Learn to understand and solve MySQL garbled problems in 10 minutes_test;
+----+---------------+----------+
| id | hex(char_col) | char_col |
+----+---------------+----------+
|  2 | E4B8ADE69687  | 中文     |
+----+---------------+----------+
2 rows in set (0.00 sec)

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

如何避免乱码

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

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

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

错误方法一

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

master [localhost] {msandbox} (test) > show create table Learn to understand and solve MySQL garbled problems in 10 minutes_test;
+--------------+--------------------------------+
| Table        | Create Table                   |
+--------------+--------------------------------+
| Learn to understand and solve MySQL garbled problems in 10 minutes_test | CREATE TABLE `Learn to understand and solve MySQL garbled problems in 10 minutes_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 Learn to understand and solve MySQL garbled problems in 10 minutes_test Learn to understand and solve MySQL garbled problems in 10 minutes=gbk;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

master [localhost] {msandbox} (test) > show create table Learn to understand and solve MySQL garbled problems in 10 minutes_test;
+--------------+--------------------------------+
| Table        | Create Table                   |
+--------------+--------------------------------+
| Learn to understand and solve MySQL garbled problems in 10 minutes_test | CREATE TABLE `Learn to understand and solve MySQL garbled problems in 10 minutes_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 Learn to understand and solve MySQL garbled problems in 10 minutes_name [COLLATE collation_name];

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

master [localhost] {msandbox} (test) > create table Learn to understand and solve MySQL garbled problems in 10 minutes_test_latin1 (id int primary key auto_increment, char_col varchar(50)) Learn to understand and solve MySQL garbled problems in 10 minutes = 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 Learn to understand and solve MySQL garbled problems in 10 minutes_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 Learn to understand and solve MySQL garbled problems in 10 minutes_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 Learn to understand and solve MySQL garbled problems in 10 minutes_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 Learn to understand and solve MySQL garbled problems in 10 minutes_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-Learn to understand and solve MySQL garbled problems in 10 minutes --default-character-set=utf8 test Learn to understand and solve MySQL garbled problems in 10 minutes_test_latin1 > data.sql
#确保导出的文件用文本编辑器在UTF-8编码下查看没有乱码
shell> mysql -uroot -p -e 'create table Learn to understand and solve MySQL garbled problems in 10 minutes_test_latin1 (id int primary key auto_increment, char_col varchar(50)) Learn to understand and solve MySQL garbled problems in 10 minutes = 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 Learn to understand and solve MySQL garbled problems in 10 minutes_test_latin1 MODIFY COLUMN char_col VARBINARY(50);
mysql> ALTER TABLE Learn to understand and solve MySQL garbled problems in 10 minutes_test_latin1 MODIFY COLUMN char_col varchar(50) character set utf8;

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