MySQL character encoding problem, Incorrect string value, mysqlincorrect

MySQL character encoding problem, Incorrect string value, mysqlincorrect
An error is reported when inserting Chinese Characters in MySQL. For details, refer to the analysis below.
Incorrect string value: '\ xD0 \ xC2 \ xC8A \ xbew' for column 'ctnr' at row 1


MySQL character set parameters:
Character_set_server: Server Character Set
Collation_server: Server verification rules


Character_set_database: Character Set of the default database
Collation_database: the default database proofreading rules


Character_set_client: the server uses this variable to obtain the character set of the client in the link.


Character_set_connection: the server converts the query of the client from character_set_client to the character set specified by this variable.
Character_set_results: the character set specified by the variable before the server sends the result set or returns an error message to the client.




You can set the concatenation character set in two statements:
A
Set names 'charset _ name' is equivalent to the following three sentences:
Mysql> SET character_set_client = x;
Mysql> SET character_set_results = x;
Mysql> SET character_set_connection = x; # This also sets the default value of collation_connection x


B
Set character set charset_name is equivalent 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 the returned result set.
Mysql> SET character_set_results = NULL;


The error reported by pg due to character set encoding is: invalid byte sequence for encoding "UTF8". for details, refer

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

Problem caused by character set encoding on MySQL: Incorrect string value. For details, 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 (11) default null,
'Ctnr 'varchar (60) 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 extranet ');
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 extranet ');
ERROR 1366 (HY000): Incorrect string value: '\ xD0 \ xC2 \ xC8A \ xbew' for column 'ctnr' at row 1




Refer:

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

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

Please refer to the following source for reprinting:
Blog.csdn.net/beiigang

Encoding of inserted data in mysql, Incorrect string value: '\ xA9O \ xB5 \ xC4 \ xC1 \ xC1

The method is to escape and find a special character or string that is not used to mark this character.
However, you need to transcode and decode the data transferred between the presentation layer (web or client) and persistence layer (database.

The incorrect string value problem occurs when importing data in mysql.

Use show variables like 'character % 'to view the database encoding;
View the encoding of file C:/data/201109/all.txt,
The two are the same.
You can use the alter table name convert to character set encoding format. It is best to back up data in advance to avoid exceptions.
Save the file directly and select encoding. UTF-8 whenever possible