MySQLでSQLを適用した際に日本語が文字化けする

はじめに

MySQLでcharacter-setが日本語に対応していない状態での文字の扱いが気になったため少し調べてみた。

環境

1
2
3
4
Windows 10 Pro (操作PC)
Docker Desktop 4.17.0 (99724)
MySQL 5.5.62
MySQL 8.0.32 (※MySQL8.0.32では character_set_serverのデフォルト値がutf8mb4のため発生しない/しなかった)

準備

今回検証するにあたって、Docker を使用し、手で確かめられるようにした。
下記リポジトリをクローンし、 Docker のビルドを行うことで一緒に確かめられるので是非試してほしい。
https://github.com/katsuobushiFPGA/docker-mysql-binary-practice ※前回の記事で使用した Dockerfile を使用する。

MySQL8.0.32で進めたところcharacter_set_serverのデフォルト値がutf8mb4のため発生しないことがわかり、MySQL5.5.62のDockerfileを作成した。
下記の手順でまずは、MySQL5.5の環境を作成する。

mysql5.5 フォルダを作成し、 conf.d, data, init フォルダを作成する。
init の中身は、そのままコピーしてきて良い。
Dockerfileを mysql5.5 フォルダに作成する。

構造は以下のような感じ

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
├─mysql
│  ├─conf.d
│  ├─data
│  ├─init
│  └─Dockerfile
├─mysql5.5
│  ├─conf.d
│  ├─data
│  ├─init
│  └─Dockerfile
└ compose.yml

1
FROM mysql:5.5.62

下記を付け足す。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
  mysql55:
    build:
      context: ./mysql5.5
    environment:
      - MYSQL_ROOT_PASSWORD=root
      - MYSQL_USER=sample
      - MYSQL_PASSWORD=sample
    volumes:
      - ./mysql5.5/init:/docker-entrypoint-initdb.d
      - ./mysql5.5/data:/var/lib/mysql
    # - ./mysql5.5/conf.d:/etc/mysql/conf.d
    ports:
      - 3307:3306
    restart:
      always

問題となったケース

下記のようなケースで日本語が文字化けした状態になった。

1
mysql -u root -proot [DB名] < hogehoge.sql

hogehoge.sql の中には、UPDATE文が入っており、カラムに日本語を入れるようなSQLになっている。

現象の確認

現象を確認するために、下記のSQLを用意した。

1
2
3
4
5
6
CREATE TABLE test_character_set (
    `test_varchar` varchar(100) DEFAULT NULL,
    `test_char` char(100) DEFAULT NULL,
    `test_text` text  DEFAULT NULL
);
INSERT INTO `test_character_set` VALUES("テストの文字列です。", "これはテストです。", "テストになります。");

character-setの確認

Dockerのコンテナ内に入り、 character-set を確認する。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
$ docker compose exec mysql55 bash

(コンテナ内)
# mysql -u root -proot test

mysql> SHOW SESSION VARIABLES LIKE 'character\_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | latin1 |
| character_set_connection | latin1 |
| character_set_database   | latin1 |
| character_set_filesystem | binary |
| character_set_results    | latin1 |
| character_set_server     | latin1 |
| character_set_system     | utf8   |
+--------------------------+--------+
7 rows in set (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'collation\_%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

こんな感じになってる。

当然だが、 latin1 なので、そのまま日本語を入れようとすると文字化けする。

※各変数の意味は下記を参考に

第65回 MySQLと文字コード
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0065

原因

問題となる文字コードのセットは、 character_set_server の latin1 の部分である。
latin1 はラテンアルファベットの文字コード標準であり、日本語に対応していない。

解決方法

解決方法を挙げる。

my.cnfを修正する

※恒久的な対処方法
→修正後に、mysqldを再起動する必要がある。

サーバ文字コード、クライアント文字コードを utf8 に変更する。

1
2
3
4
5
[mysqld]
character-set-server=utf8

[client]
default-character-set=utf8

実際にDockerの構成を変更して試してみる。直下に conf.d フォルダを作成し、その中に my.cnf ファイルを作成する。内容は下記

1
2
3
4
5
[mysqld]
character-set-server=utf8

[client]
default-character-set=utf8

compose.yml の volumes に下記を追記

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
services:
  mysql55:
    build:
      context: ./mysql
    environment:
      - MYSQL_ROOT_PASSWORD=root
      - MYSQL_USER=sample
      - MYSQL_PASSWORD=sample
    volumes:
      - ./mysql/init:/docker-entrypoint-initdb.d
      - ./mysql/data:/var/lib/mysql
+     - ./conf.d:/etc/mysql/conf.d
    ports:
      - 3306:3306
    restart:
      always

コンテナを破棄し、起動させる。

1
docker compose down && docker compose up -d

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
docker compose exec mysql bash
mysql -u root -proot
mysql> SHOW SESSION VARIABLES LIKE 'character\_set\_%';
+--------------------------+--------+
| 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   |
+--------------------------+--------+
7 rows in set (0.00 sec)

※作成済みのDBに関しては、 latin1 のままなので…

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
mysql> show create database `test`;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

MySQL全体: utf8
データベース: latin1
のような状態になっている。

下記を実行して、DBをutf8にする。

1
2
3
4
5
6
7
8
9
ALTER DATABASE `test` default character set utf8;

mysql> show create database `test`;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

テーブルも変えておく必要がある。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
mysql> show create table `test_character_set`;
+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table              | Create Table
|
+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_character_set | CREATE TABLE `test_character_set` (
  `test_varchar` varchar(100) DEFAULT NULL,
  `test_char` char(100) DEFAULT NULL,
  `test_text` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

下記を実行してテーブルをutf8にする。

1
2
3
4
ALTER TABLE `test_character_set` CONVERT TO CHARACTER SET utf8;

Query OK, 2 rows affected (0.23 sec)
Records: 2  Duplicates: 0  Warnings: 0

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
mysql> show create table test_character_set;
+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table              | Create Table
    |
+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_character_set | CREATE TABLE `test_character_set` (
  `test_varchar` varchar(100) DEFAULT NULL,
  `test_char` char(100) DEFAULT NULL,
  `test_text` mediumtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

この状態でデータを入れてみる。

1
2
3
4
5
6
7
8
9
mysql> select * from test_character_set;
+--------------------------------+-----------------------------+-----------------------------+
| test_varchar                   | test_char                   | test_text                   |
+--------------------------------+-----------------------------+-----------------------------+
| ??????????                     | ?????????                   | ?????????                   |
| ??????????                     | ?????????                   | ?????????                   |
| テストの文字列です。           | これはテストです。          | テストになります。          |
+--------------------------------+-----------------------------+-----------------------------+
3 rows in set (0.00 sec)

??? は DB/テーブルが latin1 のまま入れたデータになる。

※ Dockerの場合 my.cnf は読み取り専用でないと下記のようなエラーが出る。

1
mysql: [Warning] World-writable config file '/etc/mysql/conf.d/my.cnf' is ignored.

※参考 [Docker+Windows]mysqlのdockerイメージがmy.cnfのマウントのエラーで起動しない時の対処法
https://qiita.com/rabbitbeef/items/14433a2c0a6f85c3b476

番外編: 特殊なケース

DB/テーブルは utf8だけどMySQL全体では latin1で設定されているケース

というわけで、再現するために my.cnf を適用前に戻す。
※具体的には、 volumes の conf.d をマウントしないようにする。

↓ 準備した後の状態

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
root@93065bc166c4:/# mysql -u root -proot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.62 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW SESSION VARIABLES LIKE 'character\_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | latin1 |
| character_set_connection | latin1 |
| character_set_database   | latin1 |
| character_set_filesystem | binary |
| character_set_results    | latin1 |
| character_set_server     | latin1 |
| character_set_system     | utf8   |
+--------------------------+--------+
7 rows in set (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW SESSION VARIABLES LIKE 'character\_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | latin1 |
| character_set_connection | latin1 |
| character_set_database   | utf8   |
| character_set_filesystem | binary |
| character_set_results    | latin1 |
| character_set_server     | latin1 |
| character_set_system     | utf8   |
+--------------------------+--------+
7 rows in set (0.00 sec)

mysql>

さてこの状態でデータを入れてみると・・・

1
2
3
4
5
6
7
mysql> select * from test_character_set;
+--------------------------------+-----------------------------+-----------------------------+
| test_varchar                   | test_char                   | test_text                   |
+--------------------------------+-----------------------------+-----------------------------+
| テストの文字列です。 | これはテストです。 | テストになります。 |
+--------------------------------+-----------------------------+-----------------------------+
1 row in set (0.00 sec)

特に問題はなし！既に作成したテーブル/DBの文字コードがあっていればサーバの設定は特に関係ないみたい。

mysqlコマンドでSQLを流した際に文字化けしてしまった際の対処方法

どのようなケースか再現できなかったが、実務で起きた出来事である。
mysqlを実行した後に、SQLを流した後DBを確認した際に文字化けが発生した。
その際に取った対処方法を記載しておく。

↓こういう感じ
https://moodle.org/mod/forum/discuss.php?d=46967

mysqlオプションを使用する。

※一時的な対処方法
→サーバの設定を変えてはいけないときに使用する。

--default-character-set オプションを使用する。

1
mysql -u root -proot test --default-character-set=utf8 < hogehoge.sql

※番外編: mysqlで入る前に --default-character-set オプションを付ける。これでもいける。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
mysql -u root -proot test --default-character-set=utf8

mysql> SHOW SESSION VARIABLES LIKE 'character\_set\_%';
+--------------------------+--------+
| 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     | latin1 |
| character_set_system     | utf8   |
+--------------------------+--------+
7 rows in set (0.00 sec)

mysql>

SETを使用する

※一時的な対処方法
→現在の接続の操作のみに適用されるため、他に影響はでない。

1
SET CHARSET utf8;

SQLの実行前に設定しておく。上記を実行した後にSQLを流す。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> SHOW SESSION VARIABLES LIKE 'character\_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | latin1 |
| character_set_connection | latin1 |
| character_set_database   | utf8   |
| character_set_filesystem | binary |
| character_set_results    | latin1 |
| character_set_server     | latin1 |
| character_set_system     | utf8   |
+--------------------------+--------+
7 rows in set (0.00 sec)

mysql> SET CHARSET utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'character\_set\_%';
+--------------------------+--------+
| 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     | latin1 |
| character_set_system     | utf8   |
+--------------------------+--------+
7 rows in set (0.00 sec)

おまけ: クライアントから文字列が送信できない

SQLを保存して、下記のコマンドでコンテナ内に入りSQLを流してみる。

1
2
docker compose exec mysql bash
mysql -u root -proot test

(コンテナ内)

1
INSERT INTO `test_character_set` VALUES("テストの文字列です。", "これはテストです。", "テストになります。");

実際にやってみたGif

MySQLクライアント内で文字コードが対応しておらず、文字列が送信できず空白になってしまうので、テーブル内は下記のようになってしまう。

1
2
3
4
5
6
7
mysql> select * from test_character_set;
+--------------+-----------+-----------+
| test_varchar | test_char | test_text |
+--------------+-----------+-----------+
|              |           |           |
+--------------+-----------+-----------+
1 row in set (0.00 sec)

下記で解決できるとのこと、結構手間がかかる…
参考: https://qiita.com/tomo__x_/items/bd5d86d5f423f8f89b22

参考

10.4 接続文字セットおよび照合順序
https://dev.mysql.com/doc/refman/8.0/ja/charset-connection.html
第65回 MySQLと文字コード
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0065
4.5.1.1 mysql クライアントオプション
https://dev.mysql.com/doc/refman/8.0/ja/mysql-command-options.html
基礎MySQL ~その２~ my.cnf (設定ファイル)
https://qiita.com/yoheiW@github/items/bcbcd11e89bfc7d7f3ff
SET文でシステム変数を変更(セッション変数, グロバール変数)
https://www.wakuwakubank.com/posts/413-mysql-set/#index_id1
[Docker+Windows]mysqlのdockerイメージがmy.cnfのマウントのエラーで起動しない時の対処法
https://qiita.com/rabbitbeef/items/14433a2c0a6f85c3b476
Docker mysqlコンテナで日本語入力できない
https://qiita.com/tomo__x_/items/bd5d86d5f423f8f89b22
MySQLのテーブル作成後に、文字コードをutf8mb4に変更する https://www.karakaram.com/changing-the-character-set-to-utf8mb4-after-creating-mysql-table/

おわりに

実は実務でこういう事が起きていて、サーバ文字コードとクライアント文字コードがどっちも latin1 だったかどうか思い出しながら書いていた。
最初は、クライアント文字コードだけ latin1 で起きていた気がするんだけどと思ったけど現象が発生しないことからサーバも違かったなぁと思い出した。
アプリケーションでは普通に utf8使ってるのにDBの文字コードが latin1 なのはよくわからんですなあ。
ちなみに、サーバ文字コードとクライアント文字コードが違うことで起きることは調査できなかった。
時間あれば調査してみたいと思う。