MySQL inserts Chinese characters with garbled characters

Character Set error Solution
Problems:
Mysql> update users
-> Set username = 'guan Yu'
-> Where userid = 2;
ERROR 1366 (HY000): Incorrect string value: '/xB9/xD8/xD3/xF0' 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)
Chinese characters in the table are garbled.
Solution:
Run the following command:
Mysql> status;
--------------
Mysql Ver 14.12 Distrib 5.0.45, for Win32 (ia32)

Connection id: 8
Current database: test
Current user: root @ localhost
SSL: not in use
Using delimiter :;
Server version: 5.0.45-Community-nt MySQL Community edition (GPL)
Protocol Version: 10
Connection: localhost via TCP/IP
Server characterset: Latin1
DB characterset: Latin1
Client characterset: GBK
Conn. characterset: GBK
TCP port: 3306
Uptime: 7 hours 39 min 19 Sec
Threads: 2 questions: 174 slow queries: 0 opens: 57 flush tables: 1 open Ta
Bles: 1 queries per second AVG: 0.006
--------------
Check MySQL and find that the character set of server characterset and DB characterset is set to Latin1, so Chinese garbled characters are displayed.

Mysql> show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| Users |
+ ---------------- +
1 row in SET (0.00 Sec)

Modify the character set of a table.
Mysql> alter table users character set GBK;
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0

View the table structure:
Mysql> show create users;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
Corresponds to your MySQL server version for the right syntax to use near 'users
'At line 1
Mysql> show create table users;
+ ------- + -----------------------------------------------------------------------
------------------------------------------------------------------------------ +
| Table | Create Table
|
+ ------- + -----------------------------------------------------------------------
------------------------------------------------------------------------------ +
| Users | create table 'users '(
'Userid' int (11) default NULL,
'Username' char (20) character set latin1 default NULL
) ENGINE = InnoDB default charset = gbk |
+ ------- + -----------------------------------------------------------------------
------------------------------------------------------------------------------ +
1 row in set (0.00 sec)

Mysql> desc users;
+ ---------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------ + ----- + --------- + ------- +
| Userid | int (11) | YES | NULL |
| Username | char (20) | YES | NULL |
+ ---------- + ------ + ----- + --------- + ------- +
2 rows in set (0.02 sec)

Insert Chinese characters into the table and an error occurs.
Mysql> insert into users values (88, 'Chinese ');
ERROR 1366 (HY000): Incorrect string value: '/xD6/xD0/xCE/xC4' for column 'usern
Ame' at row 1
Mysql> insert into users values (88, 'Chinese ');
ERROR 1366 (HY000): Incorrect string value: '/xD6/xD0/xCE/xC4' for column 'usern
Ame' at row 1

You also need to change the username Character Set of the users table.
Mysql> alter table users modify username char (20) character set gbk;
ERROR 1366 (HY000): Incorrect string value: '/xC0/xEE/xCB/xC4' for column 'usern
Ame' at row 1
Mysql> alter table users modify username char (20) character set gbk;
ERROR 1366 (HY000): Incorrect string value: '/xC0/xEE/xCB/xC4' for column 'usern
Ame' at row 1

Because data already exists in the table, changing the username character set is not ***
Clear Data in the users table
Mysql> truncate table users;
Query OK, 3 rows affected (0.01 Sec)

Change the username character set in the User table
Mysql> alter table users modify username char (20) Character Set GBK;
Query OK, 0 rows affected (0.06 Sec)
Records: 0 duplicates: 0 Warnings: 0

Then, insert a Chinese character ***.
Mysql> insert into users values (88, 'Chinese ');
Query OK, 1 row affected (0.01 Sec)

Mysql> select * from users;
+ -------- + ---------- +
| Userid | username |
+ -------- + ---------- +
| 88 | Chinese |
+ -------- + ---------- +

 

 

 

Supplement

 

Modify the character encoding of a column:

Alter table tbl_name modify col_name varchar (256) Character Set gb2312;

 

Specify the encoding when creating a table:

Create Table T1
(
C1 CHAR (10) character set latin1 COLLATE latin?german=ci
) Default character set latin2 COLLATE latin2_bin;