MySQL character set and character encoding notes

More Elementary,

For further information, please refer to Sir Lou's blog:

http://cenalulu.github.io/linux/character-encoding/

http://cenalulu.github.io/mysql/mysql-mojibake/

GBK and UTF8 's actual storage method inside the system:

1 , GBK:

> SELECT Hex (CONVERT (' Hello ' using GBK);

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

| Hex (CONVERT (' Hello ' using GBK) |

|----------------------------------|

| c4e3bac3 |

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

The GBK character set is divided by 4 lengths, so the corresponding relationship is drawn:

You--c4e3

Well-BAC3

2 , UTF-8:

> SELECT Hex (CONVERT (' Hello ' using UTF8);

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

| Hex (CONVERT (' Hello ' using UTF8) |

|-----------------------------------|

| E4BDA0E5A5BD |

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

The GBK character set is divided by 4 lengths, so the corresponding relationship is drawn:

You--e4bda0

Well-E5A5BD

So we can find a different place. If we use UTF-8 "Hello" (corresponding to the underlying storage as:e4bda0e5a5bd), but use GBK way to read, GBK will be the E4BDA0E5A5BD according to every 4 bit length, Eventually cut into E4BD a0e5 a5bd this way.

Following

> SELECT CONVERT (Unhex (' e4bda0e5a5bd ') USING GBK);

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

| CONVERT (Unhex (' e4bda0e5a5bd ') USING GBK) |

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

|   Raccoon à ソ| ---> read it in a GBK way is garbled.

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

> SELECT Hex (CONVERT (' Wan ' using GBK);

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

| Hex (CONVERT (using GBK)) |

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

| E4BD |

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

> SELECT Hex (CONVERT (' Ã ' using GBK);

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

| Hex (CONVERT (' Ã ' using GBK) |

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

| a0e5 |

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

> SELECT Hex (CONVERT (' ソ ' using GBK);

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

| Hex (CONVERT (' ソ ' using GBK) |

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

| A5BD |

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

Here's an example:

Create 2 sheets t1, T2

> CREATE TABLE T1 (a varchar ()) CHARSET=GBK;

> Set names GBK;

> INSERT INTO T1 select ' Hello ';

> SELECT Hex (CONVERT (' Hello ' using GBK); In the---> GBK format, the underlying storage is actually C4E3BAC3

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

| Hex (CONVERT (' Hello ' using GBK) |

|----------------------------------|

| c4e3bac3 |

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

> CREATE TABLE t2 (a varchar ()) Charset=utf8;

> Set names UTF8;

> INSERT INTO t2 select ' Hello ';

> SELECT Hex (CONVERT (' Hello ' using UTF8); In the---> UTF8 format, the underlying storage is actually E4BDA0E5A5BD

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

| Hex (CONVERT (' Hello ' using UTF8) |

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

| e4bda0e5a5bd |

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

Added, the 16 binary encoding is reversed into the UTF8 encoded kanji:

> SELECT CONVERT (Unhex (' e4bda0e5a5bd ') USING UTF8);

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

| CONVERT (Unhex (' e4bda0e5a5bd ') USING UTF8) |

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

| Hello |

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

> SELECT CONVERT (Unhex (' e4bda0e5a5bd ') USING GBK);

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

| CONVERT (Unhex (' e4bda0e5a5bd ') USING GBK) |

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

|   Raccoon à ソ| ---> read it in a GBK way is garbled.

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

This article is from the "Vegetable Chicken" blog, please be sure to keep this source http://lee90.blog.51cto.com/10414478/1928024

MySQL character set and character encoding notes