mysql data Chinese garbled code

1. Problem description

MySQL database is a commonly used relational database, which is widely used in various enterprise projects. When using the MySQL database, we will encounter various problems, among which the problem of Chinese garbled characters is a common one.

The problem of Chinese garbled characters is expressed as follows:

1. The Chinese characters inserted in the database become a bunch of garbled codes or other characters;
2. The Chinese characters queried in the database The characters have also become a bunch of garbled codes or other characters;
3. The Chinese characters displayed from the database query on the web interface have also become a bunch of garbled codes or other characters.

This kind of Chinese garbled code problem will greatly affect the readability and usability of information for users. If this problem cannot be solved, it will bring a very inconvenient experience to users.

2. Solution

Below we will introduce methods to solve the problem of Chinese garbled characters in MySQL from several aspects.

1. Change the MySQL database character set

MySQL supports multiple character sets. The default character set before MySQL 5.5 was latin1, which was changed to utf8mb4 after MySQL 5.5. Therefore, when creating databases and tables, it is best to explicitly specify the character set to use.

You can create a database with the utf8mb4 character set through the following SQL statement:

CREATE DATABASE mydatabase DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

The database created by the above SQL statement uses the utf8mb4 character set by default, and specifies utf8mb4_general_ci as the collation.

You can also specify the character set and collation in the same way when creating a table:

CREATE TABLE mytable (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

The mytable table created by the above SQL statement uses the utf8mb4 character set by default, and specifies utf8mb4_general_ci as the collation.

When specifying the character set and sorting rules, you need to pay attention to ensure that the character sets of the client and server are consistent, otherwise the Chinese garbled problem will still occur.

2. Modify the MySQL configuration file

The default configuration file for MySQL is my.cnf, which is generally located at /etc/mysql/my.cnf on Linux systems.

By modifying the MySQL configuration file, the default character set and collation of MySQL can be modified to utf8mb4.

In the my.cnf file, you can find the following two lines of code:

[mysqld]
character-set-server = latin1

Change the character-set-server to utf8mb4, save and exit, and restart the MySQL service.

3. Modify the MySQL connection character set

By modifying the connection character set between the MySQL client and server, the problem of Chinese garbled characters can also be solved.

You can modify the connection character set through the following statement:

SET NAMES utf8mb4;

After executing the above statement, the utf8mb4 character set will be used for communication between the client and the server.

4. Modify the application encoding format

If none of the above methods can solve the Chinese garbled problem, then you need to check whether the application encoding format is correct.

When developing applications using dynamic languages ​​such as PHP, you need to ensure that input and output data are properly encoded and decoded.

You can set the encoding through the following statement:

header('Content-Type:text/html; charset=utf-8');

This can ensure that the encoding method of the web page is UTF-8, thus solving the problem of Chinese garbled characters.

5. Notes on database backup and restoration

When performing database backup and restoration, you also need to pay attention to the issue of Chinese character sets.

If the character set of the backup file is inconsistent with the database, garbled Chinese characters may occur during restoration. Therefore, when backing up and restoring a MySQL database, it is best to set the character set of the backup file to be consistent with the database.

If the character set of the backup file is inconsistent with the database, you need to convert the backup file to the correct character set before restoring.

3. Summary

The MySQL Chinese garbled problem is a relatively common problem, and it is relatively simple to solve. Depending on the actual situation, there are many ways to solve this problem. When choosing a solution, you need to make a choice based on the specific situation to ensure that the problem of Chinese garbled characters can be effectively solved.

The above is the detailed content of mysql data Chinese garbled code. For more information, please follow other related articles on the PHP Chinese website!