【MySQL】How to fix garbled text in command prompt (1)

xampp for Windows...7.4.30
mysql Ver 15.1
OS...Windows10

When you need to use special characters (such as Japanese) other than English, the strings might be garbled.

What causes text garbling?

There possibly are 2 reasons.
1.Despite that your DB is sending you the data with UTF8, your command prompt is displaying it with encoding cp932.
2.Despite that your DB does not accept two byte characters, you insert(or update) data with two byte character language.

Let's check the settings related encoding of your DB.

You can check the encoding for your DB from your command prompt.
1.Log into MySQL.
2.Hit "show variables like 'char%'."
Then you will get the following.

MariaDB [(none)]> show variables like 'chara%';
+--------------------------+-------------------------------------+
| Variable_name            | Value                               |
+--------------------------+-------------------------------------+
| character_set_client     | cp932                               |
| character_set_connection | cp932                               |
| character_set_database   | utf8mb4                             |
| character_set_filesystem | binary                              |
| character_set_results    | cp932                               |
| character_set_server     | utf8mb4                             |
| character_set_system     | utf8                                |
| character_sets_dir       | C:\xampp_mama\mysql\share\charsets\ |
+--------------------------+-------------------------------------+

• character_set_client ...The character set for statements that arrive from the client.
• character_set_connection...The server convert the data in the request from the client to this encoding.
• character_set_results...The server sends the results or error messages with this encoding.

*For the details of these variables, see https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_character_set_server.

Let's check the encoding for your command prompt.

1.Open the command prompt.
2.Hit "chcp."
Then you will get the encoding of your command prompt.

Active code page: 932 //932 is cp932(Japanese).

For "code page," refer https://en.wikipedia.org/wiki/Code_page.

Or, hit "show character set" to find the encoding that is available for setting.

show character set;

SOLUTION

1 Change the encoding settings of your command prompt

If the encoding of your command prompt is cp932 and your DB is sending the results in UTF8, you will see garbled results in your command prompt.
In that case, you need to set the encoding of your command prompt to the same as the one of the DB.
1.Exit MySQL if you are logged in.
2.Hit "chcp 65001". *65001 is utf8.

2 Change the encoding settings of your DB from your command prompt

The encoding of my command prompt is cp932. So, I need the DB to send the data in the same encoding (cp932).
1.Log into MySQL.
2.Hit "set names cp932" to change the encoding for
character_set_client,character_set_connection and character_set_results.
Then, you will get following.

+--------------------------+-------------------------------------+
| Variable_name            | Value                               |
+--------------------------+-------------------------------------+
| character_set_client     | cp932                               |
| character_set_connection | cp932                               |
| character_set_database   | utf8mb4                             |
| character_set_filesystem | binary                              |
| character_set_results    | cp932                               |
| character_set_server     | utf8mb4                             |
| character_set_system     | utf8                                |
| character_sets_dir       | C:\xampp_mama\mysql\share\charsets\ |
+--------------------------+-------------------------------------+

3 Change "my.ini"

You can also set the encoding for client/server using "my.ini" in MySQL.
To be continued to "【MySQL】How to fix garbled text in command prompt(2) "!!

Thank you!