Analyze and solve the problem of mysql garbled code in docker

Docker is a popular containerization platform that makes application deployment and management easier and more efficient. The MySQL database is an integral part of many applications. However, when using MySQL in Docker, you sometimes encounter garbled characters, which not only affects the correctness of the data, but also brings unnecessary trouble to developers.

This article will introduce the garbled problem of MySQL in Docker and possible solutions.

1. The garbled code problem of MySQL in Docker

Garbled code means that the data queried on the client does not meet expectations, and there are some unrecognizable characters or behaviors.

This problem is very common when running MySQL in Docker. Especially when the encoding method of the MySQL database is set to UTF-8, the following situations may occur:

1. When inserting data, the normal Chinese or other language characters of the data become some random characters.

2. When the client queries data, the returned data contains a large number of garbled characters.

Actually, these problems are not caused by Docker or MySQL itself, but due to some incorrect settings and configurations.

2. Solution to MySQL in Docker

1. Check the data source character set

The root cause of garbled characters is that the character set of the database is inconsistent with the character set of the data source. We need to confirm whether the character set of the data source is UTF-8 to ensure the correctness of data transmission between different systems.

Check the MySQL default character set through the following command in MySQL:

show variables like 'character%';

The character_set_client, character_set_connection and character_set_results in the displayed results should all be utf8mb4. If not, it may cause garbled characters.

To run MySQL in Docker, you need to add the following parameters to the Dockerfile or Docker-compose configuration file:

...
environment:
  MYSQL_ROOT_PASSWORD: root
  MYSQL_DATABASE: test
  MYSQL_USER: test
  MYSQL_PASSWORD: test
  MYSQL_CHARSET: utf8mb4
  MYSQL_COLLATION: utf8mb4_unicode_ci
...

Among them, MYSQL_CHARSET and MYSQL_COLLATION are the character set and sorting method respectively.

2. Modify the MySQL configuration file

When running MySQL in Docker, you can also modify the MySQL configuration file to achieve the correct character set and sorting method.

Enter the MySQL configuration file my.cnf and add the following configuration at the bottom:

[mysql]
default-character-set=utf8mb4

[mysqld]
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
skip-character-set-client-handshake

To run MySQL in Docker, you need to copy my.cnf to the container first:

...
volumes:
  - ./my.cnf:/etc/mysql/conf.d/my.cnf
...

3. Check the MySQL client character set

When running MySQL in Docker, you need to ensure that the character set of the client (such as the operating system and MySQL client tools, etc.) is also UTF-8.

In Linux, you can check the character set of the current system through the following command:

$ echo $LANG

If LANG returns other character sets, you need to add the LANG environment variable to your shell configuration file:

$ echo "export LANG='en_US.utf8'" >> ~/.bashrc

In Windows systems, you need to ensure that the character set of the client tool (such as Navicat, etc.) is also UTF-8.

4. Set MySQL column attributes

In MySQL, the character set and collation rules in the column attributes will also affect the correctness of the query results. By default, the column properties of a new MySQL table adopt the default settings of the MySQL server. If the character set and collation of the MySQL server do not match, garbled characters may result.

In order to solve this problem, you can manually set the column attributes to:

CREATE TABLE test (
  name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
...
);

The above are some solutions to the garbled problem of MySQL in Docker. Developers can choose a method that suits them according to their own needs and actual situation. In a production environment, in order to ensure system stability and reliability, application deployment and configuration need to be carefully tested and verified.

The above is the detailed content of Analyze and solve the problem of mysql garbled code in docker. For more information, please follow other related articles on the PHP Chinese website!