Why MySQL Shows Garbled Text and How to Fix It

Root Causes of MySQL Garbled Text

Garbling occurs when the character set used at the client, the MySQL server (character-set-client), and the table charset are inconsistent during either the write or read phase. The article visualizes three encoding/decoding steps for both storing and fetching data, highlighting where mismatches happen.

Encoding Process When Storing Data

Input characters are typed in the terminal using an input method.

The terminal converts characters to a binary stream according to its charset.

The binary stream is sent to the MySQL server via the client.

The server decodes the stream using character-set-client.

If the client charset differs from the table charset, a conversion from client‑charset to table‑charset is performed.

The converted binary data is written to the table.

Decoding Process When Retrieving Data

The binary data is read from the table file.

It is decoded using the table’s charset.

The result is re‑encoded to character-set-client.

The server sends the binary stream back to the client.

The client displays the result using its own terminal charset.

Key Reasons for Garbling

Inconsistent charsets between the write and read phases (e.g., UTF‑8 client vs. GBK terminal).

Mismatch among the three conversion steps within a single flow.

The article demonstrates a concrete example where UTF‑8 characters are stored in a GBK‑encoded table, resulting in replacement characters (e.g., ‘?’ or �) during conversion.

Lossless vs. Lossy Conversion

A conversion is lossless only if every character exists in both source and target charsets and the target charset can preserve unsupported characters (e.g., by retaining original bytes). UTF‑8 can represent many more characters than GBK, so converting UTF‑8 to GBK often loses data.

Avoiding Garbled Text

Ensure the three charsets—client, character-set-client, and table charset—are identical. When they match, data remains intact.

Repairing Corrupted Data

Two common but incorrect approaches are highlighted: ALTER TABLE ... CHARSET=xxx only changes the default charset for new columns, leaving existing data untouched. ALTER TABLE ... CONVERT TO CHARACTER SET ... rewrites data assuming it is already correct; it can further corrupt data that was stored via “wrong‑in‑wrong‑out”.

Correct Methods

Method 1: Dump & Reload

Export the table using the original (incorrect) charset.

Create a new table with the desired charset.

Import the dump back into the new table.

mysqldump -u root -p -d --skip-set-charset --default-character-set=utf8 test charset_test_latin1 > data.sql

mysql -uroot -p -e 'create table charset_test_latin1 (id int primary key auto_increment, char_col varchar(50)) charset = utf8' test

mysql -uroot -p --default-character-set=utf8 test < data.sql

Method 2: Convert to Binary & Convert Back

Change the column to a binary type, then back to the correct varchar charset, forcing MySQL to reinterpret the stored bytes.

ALTER TABLE charset_test_latin1 MODIFY COLUMN char_col VARBINARY(50);

ALTER TABLE charset_test_latin1 MODIFY COLUMN char_col varchar(50) character set utf8;

Both methods safely restore the original characters without data loss.