MySQL character encoding problem, incorrect string value

The insertion of the Chinese characters on MySQL is wrong, see the following analysis.
Incorrect string value: ' \xd0\xc2\xc8a\xbew ' for column ' Ctnr ' at row 1


MySQL character set related parameters:
Character_set_server: Server Character Set
Collation_server: Server Proofing Rules


Character_set_database: The character set of the default database
Collation_database: Proofing rules for the default database


Character_set_client: The server uses this variable to get the character set of the client in the link


Character_set_connection: The server translates the client's query from Character_set_client to the character set specified by the variable.
Character_set_results: The server sends a result set or returns an error message to the client before it should be converted to the character set specified by the variable




There are two statements to set up the connection character set, as follows:
A
SET NAMES ' charset_name ' corresponds to the following three sentences:
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET character_set_connection = x; #这个也设置了collation_connection的默认值x


B
The set CHARACTER set Charset_name corresponds to the following three sentences:
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET collation_connection = @ @collation_database;


When character_set_results is null, the server does not convert any of the returned result sets
mysql> SET character_set_results = NULL;


The error that is caused by character set encoding in PG is: invalid byte sequence for encoding "UTF8", see reference

http://blog.csdn.net/beiigang/article/details/39582583

MySQL character set encoding caused by the problem reported: incorrect string value, see the following experiment:

1
Mysql> Show variables like '%character_set% ';
+--------------------------+----------------------------+
| variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | GBK |
| character_set_connection | GBK |
| Character_set_database | UTF8 |
| Character_set_filesystem | binary |
| Character_set_results | GBK |
| Character_set_server | UTF8 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/share/mysql/charsets/|
+--------------------------+----------------------------+
8 rows in Set (0.00 sec)


2
Mysql> CREATE TABLE Tb_tt (id int, CTNR varchar (60));
Query OK, 0 rows affected (0.06 sec)


3
Mysql> Show CREATE TABLE Tb_tt;
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
| Tb_tt | CREATE TABLE ' Tb_tt ' (
' id ' int (one) DEFAULT NULL,
' CTNR ' varchar DEFAULT NULL
) Engine=innodb DEFAULT Charset=utf8 |
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
1 row in Set (0.00 sec)


4
mysql> INSERT INTO TB_TT (ID,CTNR) VALUES (1, ' new internet ');
Query OK, 1 row affected (0.02 sec)


5
Mysql> select * from Tb_tt;
+------+--------+
| ID | Ctnr |
+------+--------+
| 1 | New Website |
+------+--------+
1 row in Set (0.02 sec)


6
mysql> set names ' UTF8 ';
Query OK, 0 rows Affected (0.00 sec)


7
Mysql> Show variables like '%character_set% ';
+--------------------------+----------------------------+
| variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | UTF8 |
| character_set_connection | UTF8 |
| Character_set_database | UTF8 |
| Character_set_filesystem | binary |
| Character_set_results | UTF8 |
| Character_set_server | UTF8 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/share/mysql/charsets/|
+--------------------------+----------------------------+
8 rows in Set (0.00 sec)


8
mysql> INSERT INTO TB_TT (ID,CTNR) VALUES (2, ' new Internet ');
ERROR 1366 (HY000): Incorrect string value: ' \xd0\xc2\xc8a\xbew ' for column ' Ctnr ' at row 1




Reference:

Http://dev.mysql.com/doc/refman/5.5/en/globalization.html

-----------------

Reprint Please specify the Source:
Blog.csdn.net/beiigang

MySQL character encoding problem, incorrect string value