utf8 mysql garbled code

UTF-8 encoding is currently the most widely used character encoding format, and MySQL can also support using UTF-8 encoding to store data. However, when using MySQL to store UTF-8 encoded data, garbled characters may occur. This article will introduce the reasons and solutions for encountering UTF-8 encoding garbled characters in MySQL.

Due to historical reasons, MySQL uses Latin1 (ISO-8859-1) encoding format by default. This encoding format is fine for most Western languages. But for languages ​​such as Asian languages ​​and Middle Eastern languages ​​that use complex character sets, it seems insufficient.

With the development of Internet technology, more and more countries and regions use non-Latin alphabet character sets for text communication. In order to solve this problem, MySQL introduced the UTF-8 encoding format, allowing MySQL to store richer language text. UTF-8 (8-bit Unicode Transformation Format) encoding is a variable-length character encoding that supports all characters in the Unicode standard. However, when UTF-8 encoding is stored in MySQL, garbled characters may occur.

Causes and solutions for garbled characters:

Problem 1: The CHARSET of the MySQL table is not in UTF-8 format.

When creating a table, if CHARSET is not specified, the CHARSET of the database is used by default. Therefore, when the database CHARSET is not UTF-8, data stored in UTF-8 encoding will be garbled.

1.1 Check the CHARSET of the database

First you need to check the CHARSET of the database. You can check the current database CHARSET through the following command:

mysql> show create database databaseName;

1.2 Modify the CHARSET of the database

If the CHARSET of the database is not UTF-8, you need to modify the CHARSET of the database. You can modify the CHARSET of the database through the following steps:

1.2.1 Back up the database

Because we need to modify the CHARSET of the database, we need to back up the database first.

1.2.2 Modify the default encoding of the database

In MySQL, you can specify the default encoding when creating the database. The default encoding is generally set to UTF-8. Here is a demonstration of how to modify the default encoding.

Modify the MySQL configuration file /etc/mysql/mysql.conf.d/mysqld.conf, find "[mysqld]", and add the following content:

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

After the modification is completed, save and quit.

1.2.3 Modify database and table encoding

In MySQL, you can view the encoding of the database, table, and column through the following SQL statement:

/*查看数据库使用的编码*/
SHOW VARIABLES LIKE 'character_set_database';

/*查看数据表使用的编码*/
SHOW FULL COLUMNS FROM 表名;

/*查看列使用的编码*/
SELECT column_name, character_set_name FROM information_schema.columns WHERE table_name='表名';

If the current encoding does not exist If you use UTF-8, you need to modify the encoding of the database, table, and column to UTF-8. You can modify it through the following SQL statement:

/*修改数据库编码为 utf8mb4 */
ALTER DATABASE databaseName CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

/*修改表编码为 utf8mb4*/
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

/*修改列编码为 utf8mb4*/
ALTER TABLE 表名 MODIFY COLUMN 列名 varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

After the modification is completed, execute the above encoding method again to confirm CHARSET Encoded as UTF-8.

Problem 2: UTF-8 encoding is not set for the database connection

When the application connects to the MySQL database, you need to specify the connection character set. If UTF-8 encoding is not set, UTF will be read and written. Garbled characters will appear when encoding data with -8. When connecting to the MySQL database, you can set UTF-8 encoding through the following statement:

SET NAMES utf8mb4;

Question 3: MySQL client displays encoding issues

When using the command line to execute SQL statements, it may Garbled characters occur mainly because the client displays garbled characters. This problem can be solved by setting the encoding format of the MySQL client.

3.1 Linux/MacOSX client solves garbled characters

In Linux and MacOSX systems, you can set the MySQL client encoding format through the following command:

export LANG=en_US.UTF-8
export LC_ALL=en_US.UTF-8

After the setting is completed, you can Run the MySQL client, execute the SQL statement, and display Chinese characters normally.

3.2 Windows client solves garbled characters

In Windows systems, you can set the MySQL client encoding format through the following steps:

Open Control Panel-> System and Security- > System-> Advanced system settings-> Environment variables

In the environment variables, create a new system variable:

变量名：LANG
变量值：zh_CN.UTF-8

After the settings are completed, you can run the MySQL client and execute SQL statements, Display Chinese characters normally.

Summary:

When storing UTF-8 encoded data in MySQL, garbled characters may occur. The main reasons for the garbled code problem are that the CHARSET of the table is not in UTF-8 encoding format, the database connection is not set to UTF-8 encoding, and the MySQL client displays encoding issues. By correctly configuring CHARSET, connection character set, and client encoding format in MySQL, the problem of UTF-8 encoding garbled characters can be solved.

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