Wierd problems when insert Chines characters into MySQL via Windows cmd

Question

My Envirionment

• System: Windows 7 64-bit
• Database: MySQL 5.7.20 64-bit
• Locale: Chinese
• cmd code page: CP936

MySQL setting

• system variables: enter image description here

• table info: enter image description here

My Problem

Under the environment and db setting described above, I tried to insert an record into the created table cs_2, however, it failed.

The SQL statement is as follow:

insert into cs_2 (direction) values('出');

The result is:

ERROR 1366 (HY000): Incorrect string value: '\xBA' for column 'direction' at row 1

Personal analysis

After doing some researching on mysql character conversion and Windows cmd input conversion, I have got the following guidances:

• First, it is the character conversion when I input the string on the cmd:

Keyborad typing -> inputmethod conversion -> system inner encoding

in my case, the system inner encoding is the UTF-16 LE formated byte stream of ‘出’

• Second, it is the conversion in the MySQL communication according to the system variables

system inner encoding -> character_set_client -> character_set_connection -> character set of table

in my case, this is

UTF-16 LE -> gbk -> gbk ->gbk

only at the beginning between the system inner encoding and the character_set_client the conversion happened. And I thought that conversion was performed by the system itself.

Thus, under this assumption, the insert SQL should have run successfully, but it failed! I don't know why, I have struggled for this problems for several days but I can't find the solution.

If anyone happened to have encountered this problem, please give me some clues. Great thanks in advance!

Answer 1

When creating a database or a table in mysql you have to specify that you want to create this table in the utf-8 format (which is needed if you want to perform operations with the chinese character set)
Create the table (or maybe better the whole database) that has such character sets in the utf8 format:

to convert a table to utf8:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;

to convert your whole database to utf8 look at this answer:
How to convert an entire MySQL database characterset and collation to UTF-8?

to create an utf8 database from the beginning use the following command:

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 

(source https://dba.stackexchange.com/a/76789)

This should hopefully solve your problem.