How to Fix MySQL Character Encoding Issues (Moji-bake): Causes, Solutions, and Troubleshooting Guide

1. Introduction

When building a database using MySQL, character encoding issues (moji-bake) are one of the most common problems users encounter. When character corruption occurs, data cannot be displayed or entered correctly, which may significantly disrupt business operations and system management. This article explains the main causes of character encoding issues in MySQL, outlines effective solutions, and provides concrete troubleshooting steps.

2. Main Causes of Character Encoding Issues

Character encoding problems in MySQL can generally be classified into the following three categories:

Mismatch in Character Encoding Settings

• MySQL supports multiple character sets. If the character encoding between the client and the server does not match, character corruption will occur.

Configuration Differences Between Client and Server

• If the string sent from the client (for example, phpMyAdmin or a command-line tool) does not match the server’s character set configuration, problems can occur.

Incorrect Character Set Settings for Databases or Tables

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

3. Understanding MySQL Character Set Configuration

Correctly understanding MySQL character set configuration is the first step toward preventing character corruption. Let’s review the following items.

Main Character Set Configuration Variables

• 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 Check the Current Settings

• Run the following command to check the current character set configuration.

  SHOW VARIABLES LIKE 'character_set%';

• Based on the output results, identify any configuration mismatches.

4. Preventing Character Encoding Issues

To prevent character corruption in advance, proper configuration and environment setup are essential.

Adjusting the MySQL Configuration File (my.cnf/my.ini)

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

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

Setting Character Encoding for Databases and Tables

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

  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 a command-line tool, specify the character set at the time of connection.

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

5. What to Do If Character Corruption Occurs

If character encoding issues occur, follow the steps below to resolve the problem.

Check the Configuration

• Use the SHOW VARIABLES command introduced earlier to verify the current configuration values.

Backup and Restore Data

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

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

• Specify the same character set when restoring.

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

Troubleshooting Steps

• Check the configuration using the SHOW VARIABLES command, update it to the correct settings if necessary, and then test again. Review log files and error messages to identify the root cause.

6. FAQ (Frequently Asked Questions)

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

• The character set configuration of the client or server may be set to latin1 or another incompatible encoding. Change the setting to utf8mb4.

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

• You can change it using the following command.

  ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4;

Q3: How can I prevent character corruption in the Windows command prompt?

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

Q4: How can I prevent character corruption in a Docker environment?

• Create a my.cnf file on the host side and mount it to the appropriate location inside the container to apply the configuration.

Q5: Where is the MySQL configuration file located?

• On Linux, it is typically located at /etc/my.cnf or /etc/mysql/my.cnf. On Windows, it is located within the MySQL installation directory.

7. Summary

Character encoding issues in MySQL can be resolved through proper configuration and systematic troubleshooting. Use the steps outlined in this article to verify and adjust your settings as needed. By reviewing your configuration regularly, you can minimize the risk of character corruption and maintain a stable database environment.