MySQL latin1 Character Set Chinese garbled solution

Look at my example first.

Error resolution for character set

The problems that arise:
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 |? í?ù|
+--------+----------+
3 Rows in Set (0.00 sec)

Operations on the database

The following are commands for viewing character sets and sorting, and you can use phpMyAdmin to run the commands after myql> directly in sql:

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)

Using PHP to read out data, you need to join

mysql_query ("SET NAMES ' LATIN1 '")

At this point will find latin1 output of Chinese garbled in the page display can be normal (the page encoding is GBK, then the normal description of the read out of the text is GBK encoded), and then insert the UTF8 stored database because the data format is not normal, unable to insert normally, In this case, you need to use PHP for data transcoding.

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

Note here that if you want to import into db in the UTF8 character set the original encoding format that was originally inserted into Latin1 db (which is determined to be GBK code after the test in this article) becomes UTF8, the output is constantly debugged with the encoding of the Web page until the page renders a normal Chinese character. This method can reverse the introduction of the db when inserting the Chinese character encoding format.

MySQL 5.5 modified character set code for UTF8 (solve Chinese garbled problem completely)

The simplest and most perfect way to modify the character set key values in the MySQL my.cnf file (note the details of the configured fields):

1. Add Default-character-set=utf8 to the [client] field as follows:

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

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

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

3, in the [MySQL] field to add Default-character-set=utf8, as follows:

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

After the modification is complete, the service MySQL restart restart MySQL services will take effect. Note: The [mysqld] field differs from the [MySQL] field.