What to do if mysql import file contains Chinese garbled characters

The solution to Chinese garbled characters in mysql import files: first create a database and specify the encoding; then before importing the database file, specify the encoding set names utf8.

Recommended: "mysql video tutorial"

Continuing yesterday's question, after the database is configured, the query table found The Chinese characters are garbled, and I tried several methods on the Internet but couldn't solve it.
It seems that it is a problem with the imported sql file, so think in reverse, use commands to create a database, add some data, and then export it to see what happens.
When inserting data into the table, an error was reported:
ERROR 1366 (HY000): Incorrect string value: '\xE6\xB5\x8B\xE8\xAF\x95' for column 'bookname' at row 1

This is strange, check the table structure:
show create table book;

#I saw an encoding format that I didn’t want to see. latin1, change it decisively, pass the command:
alter table book default character set utf8;After the change, you can see a refreshing result. The encoding of the table has been changed, but there is still a " What the hell?", the field has a garbled latin1:

##Change decisively and pass the command:

alter table book change bookname bookname varchar(32) character set utf8;
Complete modification I didn’t look at it anymore and tried to insert the data directly:

insert into book
(id,bookname,size,price)
values
(1,"测试",2,3)
;

The insertion was successful. I checked whether the data was still garbled in Chinese and found that it was not garbled:

Then proceed to the next step, export the sql file, enter the bin directory of mysql, and start exporting through the command. This process requires entering a password

E:\mysql-5.7.28-winx64\bin>mysqldump -u root -p test > test.sql
Enter password: *******

The exported file has a table encoding format of utf8. Comparing it with the previously imported file, no problem can be seen.

Then, it is the process of importing files before. In addition to the problem of creating the database, check the mydb database structure of mydb.sql imported before. It is indeed wrong:

mysql> use mydb;
Database changed
mysql> show variables like 'character_set_database';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set, 1 warning (0.00 sec)

So I modified it: alert database mydb character set utf8;

Then I checked the encoding of the table and found something strange:

mysql> show create table sp_user_cart;
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sp_user_cart | CREATE TABLE `sp_user_cart` (
  `cart_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '涓婚敭',
  `user_id` int(11) unsigned NOT NULL COMMENT '瀛﹀憳id',
  `cart_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '璐?墿杞﹁?鎯呬俊鎭?紝浜岀淮鏁扮粍搴忓垪鍖栦俊鎭',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `delete_time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`cart_id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8

utf8mb4_unicode_ci How did this thing come out...Then I changed the encoding, queried, and still No, I remembered that when I created the database before, I might have forgotten to set the encoding. The imported things were originally garbled, or my.ini configuration was incorrect. Try using the correct process from scratch.

First check the encoding settings of the database:

show variables like 'character%';

I found it very confusing:

mysql> show variables like 'character%';
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | gbk                                    |
| character_set_connection | gbk                                    |
| character_set_database   | latin1                                 |
| character_set_filesystem | binary                                 |
| character_set_results    | gbk                                    |
| character_set_server     | latin1                                 |
| character_set_system     | utf8                                   |
| character_sets_dir       | D:\mysql-5.7.29-winx64\share\charsets\ |
+--------------------------+----------------------------------------+

Started to change, through a series of set

set character_set_client = utf8;
Get the result

+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | utf8                                   |
| character_set_connection | utf8                                   |
| character_set_database   | utf8                                   |
| character_set_filesystem | utf8                                   |
| character_set_results    | utf8                                   |
| character_set_server     | utf8                                   |
| character_set_system     | utf8                                   |
| character_sets_dir       | D:\mysql-5.7.29-winx64\share\charsets\ |
+--------------------------+----------------------------------------+

创建数据库并制定编码：CREATE DATABASE test2 CHARACTER SET utf8 COLLATE utf8_general_ci;
使用：use test2
在导入数据库文件之前，制定编码set names utf8;
导入：source F:xxxx\xxxx\mydb.sql;
不算漫长的等待之后，查询，不乱码了

mysql> select * from sp_role;
+---------+---------------+---------------------------------------------------------------------------------------------
------------------------------------------------------+-----------------------------------------------------------------
------------------------+--------------------+
| role_id | role_name     | ps_ids
                                                      | ps_ca
                        | role_desc          |
+---------+---------------+---------------------------------------------------------------------------------------------
------------------------------------------------------+-----------------------------------------------------------------
------------------------+--------------------+
|      30 | 主管          | 101,0,104,116,115,142,143,144,121,122,123,149,102,107,109,103,111,129,130,134,135,138,139,14
0,141,112,147,125,110,131,132,133,136,137,145,146,148 | Goods-index,Goods-tianjia,Category-index,Order-showlist,Brand-in
dex                     | 技术负责人         |
|      31 | 测试角色      | 101,0,104,105,116,117,115,142,143,144,121,122,123,149,103,111,129,134,138,112,147
                                                      | Goods-showlist,Goods-tianjia,Category-showlist,Order-showlist,Or
der-dayin,Order-tianjia | 测试角色描述       |
|      34 | 测试角色2     | 0,105,116,142,143,122
                                                      | NULL
                        | 测试描述12         |
|      39 | 大发送到      | 101,0,104,105,116
                                                      | NULL
                        | 阿斯蒂芬           |
|      40 | test          | 102,0,107,109,154,155,145,146,148
                                                      | NULL

实战项目可以继续进行，遂生法喜。

The above is the detailed content of What to do if mysql import file contains Chinese garbled characters. For more information, please follow other related articles on the PHP Chinese website!