Let’s talk about the solution to garbled data imported into MySQL

MySQL is an open source relational database management system that is widely used in various Internet applications. The data stored in the database may sometimes involve Chinese, so garbled characters are often encountered when importing data. This article will introduce the solution to garbled MySQL data import.

1. Cause Analysis

In MySQL, if the character set of the database and the character set of the data table are not consistent, the imported data will be garbled. Therefore, before importing data, you need to carefully check whether the character sets of the database and data table are consistent. If it is inconsistent, it needs to be modified.

2. Modify the character set

1. Modify the database character set

First, you need to log in to the MySQL database, enter the database and execute the following command:

SHOW CREATE DATABASE `database_name`;

Then you can see the character set setting statement, as shown below:

CREATE DATABASE `database_name` /*!40100 DEFAULT CHARACTER SET utf8 */

The utf8 here is the character set of the MySQL database. If you need to modify the character set, you can execute the following statement:

ALTER DATABASE `database_name` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

The utf8mb4 here is the same as utf8, but it supports more character sets. It is recommended to use utf8mb4.

2. Modify the data table character set

If the database character set has been modified, but garbled characters still appear when importing data, it is likely that the character set of the data table and Caused by database inconsistency. You can query the character set of the data table through the following command:

SHOW CREATE TABLE `table_name`;

Then you can see the settings of the character set and collation rules, as shown below:

CREATE TABLE `table_name` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CHARSET and COLLATE here are character sets respectively. and proofreading rules, which need to be consistent with the database character set. If it is inconsistent, you can execute the following statement to modify it:

ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Note: Modifying the character set may affect the performance and storage space of the database and needs to be handled with caution.

3. Convert data encoding

If garbled characters appear when importing data, you can try to convert the data encoding. Common conversion tools include iconv and recode.

1. Use iconv

You can use iconv to convert the data encoding to the target encoding, for example, convert GBK encoded data to UTF-8 encoding:

iconv -f GBK -t UTF-8 file.txt > file_utf-8.txt

-f represents the original encoding, -t represents the target encoding, file.txt is the original data file name, and file_utf-8.txt is the converted file name.

2. Use recode

recode is similar to iconv, and can also convert the data encoding to the target encoding, such as converting GB2312 encoded data to UTF-8 encoding:

recode gb2312..utf8 file.txt

gb2312 represents the original encoding, utf8 represents the target encoding, and file.txt is the original data file name.

When using the conversion tool, you need to pay attention to whether the encoding format of the source file and the target encoding format are correct, otherwise the converted data will still be garbled.

4. Summary

The solution to the garbled MySQL data import includes modifying the character set and converting the data encoding. When modifying the character set, you need to ensure that the database character set and the data table character set are consistent; when converting data encoding, you need to pay attention to the correctness of the source file encoding and the target encoding. If none of the above methods can solve the problem of garbled characters, there may be a problem with the data source itself, and you need to check whether the data source is correct.

The above is the detailed content of Let’s talk about the solution to garbled data imported into MySQL. For more information, please follow other related articles on the PHP Chinese website!