Garbled Analysis of MySQL imported data and Solutions

There are many possible causes of garbled characters, but they are usually caused by inconsistent encoding. The Garbled text problem today is caused by inconsistent file encoding and database connection encoding, to add initialization data for batch execution, use the source command,

Root @ (none) 03:14:49> use test
Database changed
Root @ test 03:14:53> show create table rule \ G;
* *************************** 1. row ***************************
Table: Rule
Create Table: Create Table 'rule '(
'Id' bigint (20) not null auto_increment comment 'auto-incrementing primary key ',
'Name' varchar (64) not null comment' rule name, for example, sharding ',
'Status' tinyint (4) not null comment' status ',
Primary Key ('id ')
) Engine = InnoDB auto_increment = 2 default charset = GBK;
Root @ test 03:15:06> show variables like "% character % ";
+ -------------------------- + ------------------------------------ +
| Variable_name | value |
+ -------------------------- + ------------------------------------ +
| Character_set_client | GBK |
| Character_set_connection | GBK |
| Character_set_database | GBK |
| Character_set_filesystem | binary |
| Character_set_results | GBK |
| Character_set_server | GBK |
| Character_set_system | utf8 |
+ -------------------------- + ------------------------------------ +
7Rows in SET (0.00 Sec)

Root @ test 03:17:14>

We can see that the encoding method connecting the client is GBK. When I directly use

root@(none) 03:19:02>source a.sql;

Root @ test 03:20:45> select * from rule;
+ ---- + -------------------- + ---------------------------------- +

| ID | Name | status |

+ ---- + -------------------- + ---------------------------------- +
| 1 | | 0 |

| 2 | insects in the upper left corner | 0 |

+ ---- + -------------------- + ---------------------------------- +
2 rows in SET (0.00 Sec)

Root @ test 03:20:46>

Garbled, why? My client connection, the table encoding format is the same !!

Let's look at the encoding method of A. SQL:

$file a.sql
a.sql: UTF-8 Unicode text, with CRLF line terminators

The file is UTF-8 encoded. Run the following code:

root@test 02:35:36>set names utf8;
Query OK, 0 rows affected (0.00 sec)

Root @ test 02:35:57> truncate table rule;
Query OK, 0 rows affected (0.00 Sec)
Root @ test 02:36:04> source A. SQL;
Query OK, 1 row affected (0.00 Sec)

Query OK, 1 row affected (0.00 Sec)
Root @ test 02:36:08> select * from rule;
+ ---- + -------------------- + ---------------------------------- +
| ID | Name | status |
+ ---- + -------------------- + ---------------------------------- +
| 1 | | 0 |
| 2 | insects in the upper left corner | 0 |
+ ---- + -------------------- + ---------------------------------- +
2 rows in SET (0.00 Sec)

Root @ test 02:36:10> set names GBK;
Query OK, 0 rows affected (0.00 Sec)

Root @ test 02:36:23> select * from rule;
+ ---- + -------------- + -------------------------------- +
| ID | Name | status |
+ ---- + -------------- + -------------------------------- +
| 1 | irrigation | 0 |
| 2 | soy sauce | 0 |
+ ---- + -------------- + -------------------------------- +
2 rows in SET (0.00 Sec)

Root @ test 02:36:25>