MySQL Chinese insert error Incorrect string value:

MySQL Chinese insert error Incorrect string value:

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 ~] #

For more details, please continue to read the highlights on the next page:

1 2 Next Page