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/ | +--------------------------+----------------------------+
によると、
- 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 のままなので、まずはそのテーブルを特定します。
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=utf8 や character-set-server=utf8 の設定なしでも utf8 でデータベース(やテーブル)を作ってくれたら良いのですけどねぇ……。