MySQL character encoding problem, Incorrect string value, mysqlincorrect

Source: Internet
Author: User

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

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.