Resolve Japanese character corruption in MySQL CLI with SET character_set_results=utf8mb4;

Tadashi Shigeoka ·  Thu, September 5, 2019

Here’s how to resolve Japanese character corruption in MySQL CLI (CUI, REPL).

MySQL

Prerequisites

MySQL character code list

mysql> show variables like "chara%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Steps to resolve character corruption in MySQL CLI

Japanese character corruption

mysql> select name_ja from product where id = 1;
+---------+
| name_ja |
+---------+
| ??    |
+---------+
1 row in set (0.01 sec)

Set character encoding character_set_results=utf8mb4

When you can’t change MySQL’s character encoding settings itself, you can temporarily change the character encoding of CLI query execution results to utf8mb4 as follows:

mysql> SET character_set_results=utf8mb4;
Query OK, 0 rows affected (0.00 sec)

Japanese character corruption resolved

mysql> select name_ja from product where id = 1;
+----------+
| name_ja  |
+----------+
| 商品 |
+----------+
1 row in set (0.01 sec)

That’s all from the Gemba.