Thorough Guide to Causes and Fixes for Garbled Text in MySQL

1. Introduction

2. Main Causes of Garbled Text

Mismatched Character Set Settings

• MySQL supports multiple character sets, but if the client and server character sets do not match, garbled text occurs.

Configuration Differences Between Client and Server

• If the strings sent by the client (e.g., phpMyAdmin or command-line tools) do not match the server’s character set configuration, this can also cause the issue.

Incorrect Character Set Settings for Databases or Tables

• If you do not specify the appropriate CHARACTER SET when creating a database or table, inconsistencies can arise when manipulating data later.

3. Understanding MySQL Character Set Settings

Key Character Set Configuration Items

• character_set_server: The default character set for the entire server
• character_set_client: The character set of strings sent from the client
• character_set_database: The default character set for the database

How to Verify the Settings

• Run the following command to view the current character set settings.

  SHOW VARIABLES LIKE '%';

• Based on the output, identify any settings that are inconsistent.

4. Preventing Garbled Text

Adjusting MySQL Configuration Files (my.cnf/my.ini)

• To change the server-side settings, edit my.cnf or my.ini as shown below.

  [mysqld]
  character-set-server = utf8mb4
  collation-server = utf8mb4_general_ci

Setting Character Sets for Databases and Tables

• When creating a database, use the following command to explicitly specify the character set.

  CREATE DATABASE sample_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

• To modify an existing table:

  ALTER TABLE table_name CONVERT TO ... CHARACTER SET utf8mb4;

Adjusting the Client Environment

• When using command-line tools, specify the character set at connection time.

  mysql --default-character-set=utf8mb4 -u root -p

5. How to Address Garbled Text Issues

Check Settings

• Use the SHOW VARIABLES command introduced above to check the current settings.

Backing Up and Restoring Data

• When backing up data, explicitly specify the character set.

  mysqldump --default-character-set=utf8mb4 -u root -p database_name > backup.sql

• Do the same specification when restoring.

  mysql --default-character-set=utf8mb4 -u root -p database_name < backup.sql

Troubleshooting Steps

• Use the SHOW VARIABLES command to check the settings, adjust them as needed, and then retest. Review log files and error messages to pinpoint the cause.

6. FAQ (Frequently Asked Questions)

Q1: Why does Japanese text appear as “???” in MySQL?

• The client or server character set may be set to latin1 or similar. Change the setting to utf8mb4.

Q2: How do I change the character set of an existing table?

• You can change it with the following command.

  ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4;

Q3: How can I prevent garbled characters in the command prompt?

• Use the chcp 65001 command to change the code page to UTF-8.

Q4: Are there any measures to prevent garbled characters in a Docker environment?

• You can create a my.cnf on the host and mount it to the appropriate location inside the container to apply the settings.

Q5: Where is the MySQL configuration file located?

• On Linux it is located at /etc/my.cnf or /etc/mysql/my.cnf, and on Windows it resides in the MySQL installation directory.

7. Summary