MySQL garbled problem Ultimate Guide

Mysql has a large number of character sets, from the client to the result set, from the server to the database to the table to the column, you can set character sets, flexible and powerful, but it is very prone to problems, if you do not understand the mechanism, garbled characters may easily occur.
In order to make everyone feel less affected or free from gibberish harassment and troubles at work, I am here to combine the posts posted by other students on the forum with my own understanding and practices, A detailed analysis is summarized to show the audience.

The basic knowledge about character sets and garbled characters is not described in detail here (please search by yourself), but there is a problem that needs to be particularly emphasized:How is garbled code produced??
I believe many of you are confused about this problem, or you have never thought about it. The problem is that "character encoding" is incorrect, leading to garbled characters, but you have never really thought about why "character encoding" causes garbled characters.
The answer is actually very simple:"Conversion causes garbled characters"!
Based on this principle, the various situations are simple:
1) No garbled characters will occur during data transmission.
2) data storage will not cause garbled characters
3) data input and output (including display) may cause garbled characters
4) garbled characters may occur when receiving and sending data.

More detailed explanation: conversion causes garbled characters referData of Character Set A is parsed as Character Set B.Instead of converting the correct A character set to B.
For example, in the following mysql Character Processing flowchart, the mysql client actually sends two gbk characters (4 bytes), but character_set_connection
After utf8 is set, the mysql server parses the 4-byte gbk data received by utf8 to obtain 1 Chinese Character + 1 byte, which generates garbled characters;

If character_set_connection is set to gbk, the mysql server parses the data according to gbk to get two correct Chinese characters and then converts them to the utf8 encoding corresponding to these two Chinese characters, which will not produce garbled characters .)

[Character Processing Mechanism of mysql]
Detailed processing mechanisms include:
 

We can simulate a processing process from data insertion to Data Reading to see how character sets are moved and moved throughout the process.
[Insert Process]
1. The client sets its own encoding (character_set_client) to receive user input;
2. The client converts the user input to the connection encoding (character_set_connection) ===> the first conversion
3. The client sends the converted data to the server.
4. When the server receives data from the client, it judges the character set of the data column and performs character conversion ====> second conversion.
5. The server stores data (such as a disk) ====> the storage will not result in encoding conversion.

1 2 3 Next Page