Why MySQL Data Gets Garbled: A Step‑by‑Step Encoding Journey

Encoding flow when inserting data into MySQL :

Input typed in a terminal or client tool (e.g., Navicat) is encoded according to the terminal's character set.

The encoded binary stream is sent to the MySQL server, which decodes it using the character_set_client setting.

The server then checks the target table's charset. If it differs from character_set_client, MySQL converts the data to the table's charset before writing the binary stream to the data file.

Summary: client → server (engine) → file – three encoding steps, two conversions.

Decoding flow when retrieving data from MySQL :

The binary data is read from the data file and decoded using the table's charset.

The server re‑encodes the result according to character_set_client and sends it back to the client.

The client terminal finally decodes the stream using its own charset for display.

Summary: file → server (engine) → client – three encoding steps, two conversions.

Why encoding conversion is needed:

Between client and server to allow the server to parse SQL statements (determine INSERT vs. UPDATE, etc.).

Between server and file so the storage engine can perform character‑level operations on the data.

Common causes of garbled characters :

Inconsistent charsets between insertion and retrieval (e.g., storing as UTF‑8 but reading as GBK).

Lossy conversion when the client charset, character_set_client, and table charset differ, causing double conversion (e.g., UTF‑8 → GBK → UTF‑8) where GBK→UTF‑8 is lossy.

Lossy conversion occurs when a character exists in the source charset but not in the target charset, or when the target charset cannot preserve unsupported characters.

To avoid garbled text, ensure that the client charset, MySQL server's character_set_client, and the table charset are all identical.