CODEWITHSUNDEEP
mysqlutf-8character-encoding
zhuguowei
zhuguowei

Reputation: 8487

why in remote server cannot input Chinese character in mysql command line but local is ok

In my local machine(Mac OS) iTerm2 terminal, I could login remote mysql server and input Chinese character successfully

➜  ~ mysql -h remote_ip  -u username -p foo --safe-updates

mysql> select '你好';
+--------+
| 你好   |
+--------+
| 你好   |
+--------+
1 row in set (0.01 sec)

then I login a remote server by ssh

➜  ~ ssh root@remote_ip

and login the same mysql server

root@qa-web:~# mysql -h remote_ip  -u username -p foo --safe-updates

mysql> select '

but this time I cannot input Chinese character in command line, after inputting they are disappeared immediately.

Why is so?

Additional information

In above second case

mysql> status
...
Server characterset:    utf8mb4
Db     characterset:    utf8
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4

My machine Mac OS

➜  ~ locale
LANG="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_CTYPE="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_ALL="en_US.UTF-8"

Remote Server

root@hg:~# locale
LANG=en_US.UTF-8
LANGUAGE=
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=en_US.UTF-8

root@hg:~# locale -a
C
C.UTF-8
en_US.utf8
POSIX

Copy this sql(select 'hello','你好';) from Atom to Mysql command line, and the effect is

mysql> select 'hello','';
+-------+--+
| hello |  |
+-------+--+
| hello |  |
+-------+--+
1 row in set (0.00 sec)

Locale

➜  ~ 你好
zsh: command not found: 你好

Remote

root@hg:~# 你好
-bash: $'\344\275\240\345\245\275': command not found

Upvotes: 2

Views: 2554

Answers (3)

Alastair McCormack
Alastair McCormack

Reputation: 27724

On Unix systems you need to make sure your locale matches your terminal emulation. Programs, like the MySQL shell, uses the locale value to determine what encoding to write to the terminal in.

On Mac, the default locale is usually UTF-8 based, which matches the default iTerm profile.

When you remote shell, you also need to make sure the remote locale matches your terminal. If the remote server is non-mac then it's more likely that the locale is not UTF8 based.

When you remote shell, get the locale in play

locale

To work with the Mac defaults, it needs to be *.utf-8. For example:

en_gb.utf-8

If it's not then you must change it, at least temporarily. See what locales are available:

locale -a

Find an appropriate locale then set the LANG environment variable:

export LANG=en_gb.utf-8

Now you can run mysql and get the correct results.

Upvotes: 0

Rick James
Rick James

Reputation: 142366

If you are using Windows with cmd, then...

The command "chcp" controls the "code page". chcp 65001 provides utf8, but it needs a special charset installed, too. some code pages

To set the font in the console window: Right-click on the title of the window → Properties → Font → pick Lucida Console

Upvotes: 0

sietse85
sietse85

Reputation: 1506

try starting mysql with mysql --default-character-set=utf8 This forces the character_set_client, character_set_connection and character_set_results variables to be UTF8.

Source: MySQL command line formatting with UTF8

Of course if you are using a chinese specific charset, start it with that. Like GB (gb18030) charsets for example.

Upvotes: 2

Related Questions