MySQL latin1 character set Chinese garbled Solution

The most garbled mysql Chinese is the database encoding problem. If our database encoding is the latin1 character set, but we save the gbk directly, Chinese garbled characters may occur, below I have summarized some solutions.

View my instance first

Character Set error Solution

Problems:
Mysql> update users
-> Set username = 'guan Yu'
-> Where userid = 2;
ERROR 1366 (HY000): Incorrect string value: 'xb9xd8xd3xf0 'for column 'usern
Ame' at row 1

An error occurred while inserting Chinese characters into the table.
　　
Mysql> select * from users;
+ -------- + ---------- +
| Userid | username |
+ -------- + ---------- +
| 2 | ???? |
| 3 | ???? |
| 4 |? Í? Optional |
+ -------- + ---------- +
3 rows in set (0.00 sec)

Database Operations

The command for viewing character sets and sorting methods is as follows. You can run myql> directly using phpmyadmin:

Mysql> show variables like 'character _ set _ % ';
+ --------- + ---------- +
| Variable_name | Value |
+ --------- + ---------- +
| Character_set_client | latin1 |
| Character_set_connection | latin1 |
| Character_set_database | latin1 |
| Character_set_results | latin1 |
| Character_set_server | latin1 |
| Character_set_system | latin1 |
| Character_sets_dir |/usr/share/mysql/charsets/|
+ --------- + ---------- +
7 rows in set (0.00 sec)

Mysql> show variables like 'collation _ % ';
+ -------- + ------- +
| Variable_name | Value |
+ -------- + ------- +
| Collation_connection | latin1_swedish_ci |
| Collation_database | latin1_swedish_ci |
| Collation_server | latin1_swedish_ci |
+ -------- + ------- +
3 rows in set (0.00 sec)

To read data using php, you must add

Mysql_query ("set names 'latin1 '")

At this time, we will find that the Chinese garbled characters output by latin1 are properly displayed on the page (the Page code is gbk, and the normal Code indicates that the read text is gbk encoded ), when you insert a database stored in utf8, the data format is abnormal and cannot be inserted normally. In this case, you need to use php for data transcoding.

Iconv ('gbk', 'utf-8', XXXXX );

Note that if you want to import the data to the database of the utf8 character set, the original encoding format originally inserted into the latin1 DB (determined as gbk encoding after testing in this article) is converted to utf8, therefore, the webpage encoding method is used to continuously debug the output until the page displays normal Chinese characters. This method can reverse introduce the Chinese character encoding format when the db is inserted.

MySQL 5.5 modifies the character set encoding to UTF8 (completely solves Chinese garbled characters)

The simplest and perfect modification method is to modify the character set key value in mysql's my. cnf file (pay attention to the configuration field details ):

1. Add default-character-set = utf8 to the [client] field, as shown below:

[Client]
Port = 3306
Socket =/var/lib/mysql. sock
Default-character-set = utf8

2. Add character-set-server = utf8 to the [mysqld] field as follows:

[Mysqld]
Port = 3306
Socket =/var/lib/mysql. sock
Character-set-server = utf8

3. Add default-character-set = utf8 to the [mysql] field, as shown below:

[Mysql]
No-auto-rehash
Default-character-set = utf8

After the modification, the service mysql restart restarts the mysql service to take effect. Note: The [mysqld] field is different from the [mysql] field.