HOME > blog > MySQL > MySQL のテーブルの文字コードを utf8 に変更する

MySQL のテーブルの文字コードを utf8 に変更する

今回は MySQL についての超小ネタです……。

とあるサーバで動いてるシステムで「日本語を入力すると Internal Server Error が発生する」との相談を受けた際のお話です。

そのシステムは MySQL を利用してるシステムなので「多分文字コード設定回りだろうなぁ……」と察しはしたんですが、普段その手の設定の確認や変更は phpMyAdmin を利用することが多くて「そいえばコマンドでどう実行するんだっけ?」と調べた備忘録です。

まずは現状確認

まずは my.cnf の確認から。Linux でもディストリビューション(あるいはインストールの仕方)によって my.cnf の場所は変わるんですが、今回のサーバは Redhat系ディストリビューションで、その場合は大体 /etc/my.cnf にあります。内容は初期設定のままだったので「なるほど、ね」と。

次に、mysql に入ったあと、

SHOW VARIABLES LIKE "chara%";

を実行すると現在稼働中の MySQL の文字コード設定が表示されます。以下の感じのレスポンスが返ってきます。

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
mysqlで文字コードをutf8にセットする - Qiita
mysqlの文字コードはチェックする場所が多いので原因を突き止めるのに毎回苦労します。大きく二種類に分けられて、1. クライアント側、サーバー側(mysqlサーバー)、及びそれらの接続の文字コード2. データベース/テーブル/...

によると、

  • character_set_client : クライアント側で発行したsql文はこの文字コードになる
  • character_set_connection : クライアントから受け取った文字をこの文字コードへ変換する
  • character_set_database : 現在参照しているDBの文字コード
  • character_set_results : クライアントへ送信する検索結果はこの文字コードになる
  • character_set_server : DB作成時のデフォルトの文字コード
  • character_set_system : システムの使用する文字セットで常にutf8が使用されている

基本的にはこれを全部(character_set_filesystem, character_sets_dir以外)utf-8にすればok。

とのこと。

my.cnf を更新

my.cnf を開いて [mysqld][client] の文字コード指定をした上で、mysqld を再起動させます。MySQL5.5以降、mysqld の方の文字コードセットは character-set-server に変わっている点に注意ください。

# 本来の my.cnf は初期設定のままでももう少し色々設定が記述されてますが、
# 本題に関係ない部分は今回省略してます
[mysqld]
character-set-server=utf8
[client]
default-character-set=utf8

再起動は

sudo service mysqld restart

最近の Redhat系ディストリビューションだと systemctl を使うみたいですけど、今回触ったサーバでは service で再起動がかかってくれました。

再起動後、もう一度

SHOW VARIABLES LIKE "chara%";

で、

+--------------------------+----------------------------+
| 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                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

となっていることを確認。

latin1 で作られたテーブルを特定する

このままだと既に latin1 で作られたテーブルはそのまま latin1 のままなので、まずはそのテーブルを特定します。

【MySQL】テーブルの文字コードを調べる at softelメモ
MySQLでは、クライアント、現在の接続の文字コード、データベースの文字コード、テーブルの文字コードなど事細か ...
USE database;

で、対象となるデータベースに切換えます。次にテーブルの状態を確認。

SHOW TABLE STATUS FROM database;

するとこんな感じで結果が返ってきます。

+---------------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+-----------------+---------+
| Name                                  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options  | Comment |
+---------------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+-----------------+---------+
| CHARACTER_SETS                        | MEMORY |      10 | Fixed      | NULL |            384 |           0 |        16434816 |            0 |         0 |           NULL | 2017-03-03 22:35:56 | NULL                | NULL       | utf8_general_ci |     NULL | max_rows=43690  |         |
| COLLATIONS                            | MEMORY |      10 | Fixed      | NULL |            231 |           0 |        16704765 |            0 |         0 |           NULL | 2017-03-03 22:35:56 | NULL                | NULL       | utf8_general_ci |     NULL | max_rows=72628  |         |

返ってくる情報が多いので分かりにくいですが、Collation の値が、そのテーブルに設定されたいわゆる文字コードに相当します。phpMyAdmin でテーブル構造を確認する際の欄では「参照順序」と表示される項目ですね。

対象テーブルとカラムの文字コードを変更する

今回は問題を起こしてたテーブルにいくつかレコードが入っている状態だったので、DROP TABLE で一旦テーブルを削除してから作り直し、という手順を踏まずに既存テーブルに対して文字コードを変更するアプローチを取ります。

ただし、テーブルを消しても問題ない状態だったりレコードがない、とかなら対象テーブルを削除して作り直した方が確実だと思います。

ALTER TABLE targettable CONVERT TO CHARACTER SET utf8;

で、完了。

なお、上述の参照ページでは既存テーブルとカラムの各文字コードを一括変換する方法のほか、テーブルのカラムの文字コードをひとつずつ変更する方法にも触れられています。

雑感

MySQL も default-character-set=utf8character-set-server=utf8 の設定なしでも utf8 でデータベース(やテーブル)を作ってくれたら良いのですけどねぇ……。