MySQL character encoding problem, incorrect string value

On MySQL insert the kanji times error for example below. See the following analysis in detail.

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:server Use this variable to get the character set of the client in the link


Character_set_connection:server converts the client's query from Character_set_client to the character set specified by the variable.
Character_set_results:server should be converted to the character set specified by the variable before sending the result set or returning an error message to the client




There are two statements that enable the connection character set. For example, the following:
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 perform any conversions for the returned result set
mysql> SET character_set_results = NULL;


The error caused by character set encoding in PG is: invalid byte sequence for encoding "UTF8". See detailed references

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

Problem reported by character set encoding on MySQL: Incorrect string value, see the following experiment in detail:

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

9

If you change the default character set or collation for a database,
Stored routines that use the database defaults must be dropped and
Recreated so, they use the new defaults. (In a stored routine,
Variables with character data types use the database defaults if the
Character set or collation is not specified explicitly. See [Help
CREATE PROCEDURE].)

References:

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

Http://dev.mysql.com/doc/refman/5.5/en/alter-database.html

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

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

MySQL character encoding problem, incorrect string value