MySQL Chinese insert error Incorrect string value: & #39; \ xCC \ xEC \ xB2 \ xC5 & #39;, xccxec

MySQL Chinese insert error Incorrect string value: '\ xCC \ xEC \ xB2 \ xC5', xccxec


Preface:Failed to input Chinese characters, error: Incorrect string value: '\ xCC \ xEC \ xB2 \ xC5', as shown below:

Mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)


Mysql> insert into t select 2 as a, 'genius 'as B;
ERROR 1366 (HY000): Incorrect string value: '\ xCC \ xEC \ xB2 \ xC5' for column 'B' at row 1
Mysql>
Mysql>


1. Check the character set of the database. It is utf8 and displayed normally.
Mysql> show variables like '% char % ';
+ -------------------------- + ------------------------------------ +
| 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/local/mysql/share/charsets/|
+ -------------------------- + ------------------------------------ +
8 rows in set (0.00 sec)


Mysql>


2. view the character set of the connected client,
Vim/etc/my. cnf
# ----------------- UTF-8 -----------------#
Skip-character-set-client-handshake
Init-connect = 'set NAMES utf8'
Character-set-server = utf8
# ----------------- UTF-8 -----------------#
You can see that the connect connection has the utf8 setting, which is also ensured. You can see utf8 encoding when logging on through the mysql command line.


3. Run the following command in the Query window through the sqlyog remote tool:
Set names utf8;
Insert into t SELECT 3 AS a, 'second dream' AS B;
Yes. The execution interface is shown as follows:
2 queries executed, 2 success, 0 errors, 0 warnings


Query: set names utf8


0 row (s) affected


Execution Time: 0 sec
Transfer Time: 0 sec
Total Time: 0.001 sec
--------------------------------------------------


Query: insert into t select 3 as a, 'second dream' as B


1 row (s) affected


Execution Time: 0.002 sec
Transfer Time: 0 sec
Total Time: 0.002 sec


Query the t table in the sqlyog window. Chinese characters are also displayed, as shown in:

 

However, after linux, you can use the mysql window command to log on, as shown in the following code:

Mysql> SELECT * FROM t;
+ --- + ----------- +
| A | B |
+ --- + ----------- +
| 1 | bb |
| 1 | ?? |
| 2 | min Cun. |
| 3 | response .? Sister? |
+ --- + ----------- +
4 rows in set (0.00 sec)


Mysql>


In this case, we can infer where the problem is. The sqlyog client can normally enter Chinese characters to display Chinese characters, but the mysql window in linux cannot. Check the character set settings of the linux operating system on the OS layer.



4. Check the character set of the linux OS where the mysql server is located.
[Root @ data01 ~] # Cat/etc/sysconfig/i18n
LANG = "zh_CN.GB18030"
LANGUAGE = "zh_CN.GB18030: zh_CN.GB2312: zh_CN"
SUPPORTED = "zh_CN.UTF-8: zh_CN: zh: en_US.UTF-8: en_US: en"
SYSFONT = "lat0-sun16"
Modified by timman on 2015/03/03


We can see that this is not GB18030, not utf8, so we need to reset it and copy the content of/etc/sysconfig/i18n from the production environment, and change it to the following:
[Root @ data01 ~] # Cat/etc/sysconfig/i18n
# LANG = "zh_CN.GB18030"
# LANGUAGE = "zh_CN.GB18030: zh_CN.GB2312: zh_CN"
# SUPPORTED = "zh_CN.UTF-8: zh_CN: zh: en_US.UTF-8: en_US: en"
# SYSFONT = "lat0-sun16"
# Modified by timman on 2015/03/03
LANG = "zh_CN.UTF-8"




Then restart linux, restart mysql, or export LANG = "zh_CN.UTF-8 ";
[Root @ data01 ~] # Service mysqld5612 restart
Shutting down MySQL... SUCCESS!
Starting MySQL ...................................... ............................... SUCCESS!
[Root @ data01 ~] #


5. Test: For data unification, linux re-sets the character set and then re-logs in to execute the Chinese input operation. Therefore, after the table is drop, create still reports an error. If the table cannot be entered in Chinese, encoding problems may occur.
Mysql> insert into t SELECT 4 AS a, 'Fourth Dream 'AS B;
ERROR 1366 (HY000): Incorrect string value: '\ xB5 \ xDA \ xCB \ xC4 \ xC3 \ xCE' for column 'B' at row 1
Mysql>
However, sqlyog can be input, but the query in the linux mysql operation window is garbled as follows:
Mysql> SELECT * FROM t;
+ ------ + ----------- +
| A | B |
+ ------ + ----------- +
| 3 | response .? Sister? |
+ ------ + ----------- +
1 row in set (0.00 sec)


Mysql>




Analysis: The sqlyog tool can be used. The linux system is also utf8, and the database is also utf8 encoding. Only the shell Terminal code of the remote tool that connects to the linux server where mysql is located, check my shell connection tool xshell now


6. Check the connection of the xshell tool. In the Terminal-> Encoding: drop-down box next to it, it is found that it is default and is not set to utf8 Encoding, as shown in:



Then modify it to utf8, as shown below:



Then, you can enter Chinese as follows:
Mysql> insert into t SELECT 4 AS a, 'Fourth Dream 'AS B;
Query OK, 1 row affected (0.11 sec)
Records: 1 Duplicates: 0 Warnings: 0

Mysql>



7. set names utf8; invalid reason
Please refer to: http://blog.csdn.net/zsmj_2011/article/details/7943734



PS: In summary, mysql has two concepts: server-side encoding and client-side encoding. My server-side encoding is utf8, my MYSQL client (not the mysql window interface, but the client tool xshell that remotely connects to the mysql server) is obviously a GBK or ansi environment, so it must be set to utf8 before conversion is successful.


For Chinese garbled characters, check the following three places:
1. character encoding of the mysql window (set the character set of the remote tool connected by xshell );
2. database character encoding (show variables like '% char % ');
3. linux operating system character encoding (echo $ LANG );

Bytes ----------------------------------------------------------------------------------------------------------------
<All Rights Reserved. This document can be reprinted, but the source address must be indicated by link. Otherwise, we will be held legally responsible.>
Original blog address: http://blog.itpub.net/26230597/viewspace-1447291/
Original Author: Huang Shan (mchdba)
Bytes ----------------------------------------------------------------------------------------------------------------