仙石浩明の日記

MySQL 5 からテーブルごとに文字列のエンコーディングを指定できるようになった (「そんなことは知ってるYO!」という人も多いと思うので、 そういう人は「これからが本題」 の部分まで読み飛ばして欲しい)。 例えばテーブルを作るときに、

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.05 sec)

mysql> USE test
Database changed
mysql> CREATE TABLE user ( name VARCHAR(255) ) CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)

などと 「CHARSET=utf8」 を指定すれば、 文字列を UTF-8 エンコーディングで格納する。 「CHARSET」 すなわち 「文字集合」 と、 エンコーディング (文字符号化) は本来別の概念であるが、 MySQL の場合は両者をまとめて CHARSET ないし character_set と呼んでいるので、 ここではそれを踏襲してキャラクタセットと呼ぶことにする。 MySQL のシステム変数のうちキャラクタセットに関連するものは、 以下のように沢山ある。

mysql> SHOW 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)

たくさんあってややこしいが、 重要なのは 「character_set_client」 と 「character_set_connection」 「character_set_results」 で、 この3変数はクライアントがクエリを送信し、 クエリ結果を受信するときのキャラクタセットを設定する。 charset コマンド (あるいは SET NAMES) を使うと、 クライアント側のキャラクタセットに関係するこの3変数を一度に変更できるので、 特に必要がなければこの3変数は常に同じキャラクタセット、 すなわちクライアント側で送受信するキャラクタセットに一致させておくとよい (PHP スクリプトから MySQL をアクセスするときは、 mysql_set_charset() を使ってクライアント側のキャラクタセットを設定する)。

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

mysql> INSERT INTO user VALUES ('仙石　浩明');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT name, HEX(name) FROM user;
+-----------------+--------------------------------+
| name            | HEX(name)                      |
+-----------------+--------------------------------+
| 仙石　浩明      | E4BB99E79FB3E38080E6B5A9E6988E |
+-----------------+--------------------------------+
1 row in set (0.00 sec)

このように 「select HEX()」 で確認すると、 文字列が正しく UTF-8 エンコーディングで格納されていることが確認できる。

蛇足だが、mysql クライアントが GNU readline を使っている場合は、 ~/inputrc などで 「set convert-meta Off」 を設定しておく。 デフォルトの readline では convert-meta が On なので、 キャラクタの最上位ビット (MSB) を 0 にしてしまう。 つまり UTF-8 (および EUC-JP や Shift_JIS) などの 8bit キャラクタセットだと、 MSB が 1 である文字が正しく送信されない。 例えば 「あ」 (UTF-8 で E3 81 82) を入力しようとしても、 MSB が落ちた 「c^A^B」 (63 01 02) が送られてしまう。

「character_set_server」 が latin1 になっていて気持ち悪いかも知れないが、 このシステム変数は新しく database を作るときのデフォルトを設定するものなので、 (latin1 な database は金輪際作らないというのでも無い限り) 変更する必要はない。

latin1 になっているもう片方の変数 「character_set_database」 は、 デフォルト database に合わせて (つまり USE コマンドを発行するごとに) サーバがこの変数を変更するので、 これもユーザが変更する必要はない。

前置きが長くなったが、これからが本題。

UTF-8 なテーブルを読み書きする際は、 「charset utf8」 コマンドを送信してクライアント側のキャラクタセットを UTF-8 に設定すればよいのであるが、 デフォルトが latin1 であるクライアントも多い。 PHP などから MySQL サーバにアクセスする場合なども、 (PHP のビルド方法にも依存するが) デフォルトは latin1 になっている (PHP の場合 mysql_client_encoding() で確認できる)。 このようなクライアントをデフォルトの latin1 のままで使うとどうなるだろうか？

クライアント側のキャラクタセットが latin1 のままで、 UTF-8 なデータをテーブルに挿入してみる:

mysql> DELETE FROM user;
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO user VALUES ('仙石　浩明');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT name, HEX(name) FROM user;
+-----------------+--------------------------------------------------------------------+
| name            | HEX(name)                                                          |
+-----------------+--------------------------------------------------------------------+
| 仙石　浩明 | C3A4C2BBE284A2C3A7C5B8C2B3C3A3E282ACE282ACC3A6C2B5C2A9C3A6CB9CC5BD |
+-----------------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

文字化けせずに読み書きできているし、 同様に (latin1 な) PHP スクリプトからこのレコードを読んでも、 正しい UTF-8 な文字列として読み込むことができる。 ところが、 HEX() 演算子で文字列のコードをチェックしてみると、 全く違うコード (しかも正しい UTF-8 コードが 15バイトであるのに対し、 2倍以上長い 33バイト) になってしまっていることがわかる。

文字化けしていれば誰でも気付くし、 直そうとするので問題は少ないのであるが、 このように文字化けしない場合は、 問題が放置されたままになりかねないので注意を要する。

例えば google で検索してみると、 mysqldump で文字化けしないようにするための 「ノウハウ」 として、 「--default-character-set=latin1」 オプションを指定すればよい、 と書かれているページが沢山見つかる。 おそらく、 latin1 なクライアント (例えば PHP スクリプト) で、 非 latin1 文字列 (例えば漢字文字列) をデータベースに書込んでしまったために、 mysqldump でバックアップを取るときに latin1 を指定しないと文字化けしてしまう状況なのだろう。

もちろんこれは間違った対処法である。 UTF-8 なテーブルをバックアップするときは 「--default-character-set=utf8」 オプションを指定すべきだし、 もしこのオプションで文字化けするなら、 テーブルに格納されたデータが間違っているわけで、 修正すべきはテーブル内のデータであって、 指定するオプションではない。

上述した実行例のように、 HEX() 演算子で文字列のコードをチェックすれば、 テーブルに正しいエンコーディングで格納されているか確認できるが、 クエリ結果の罫線がずれていないかでも間違いに気付くことはできる。 上記の例ではクエリ結果に 「仙石　浩明」 という漢字文字列が正しく表示されているものの、 その次の罫線キャラクタ「|」の位置がずれている。 これは mysql クライアントが、 長さ 15 文字の latin1 文字列を出力したつもりになっていることを意味している。 その latin1 な 15 文字を、 端末側が勝手に UTF-8 な (全角) 5文字として解釈したため 「|」 の位置がずれてしまった、 というわけ。

では、なぜテーブル内のデータがこんな長大なコードになってしまうのか？

正: E4BB99E79FB3E38080E6B5A9E6988E
誤: C3A4C2BBE284A2C3A7C5B8C2B3C3A3E282ACE282ACC3A6C2B5C2A9C3A6CB9CC5BD

INSERT 文で 「仙石　浩明」 という値のレコードを挿入したとき、 送信したデータは正しい UTF-8 文字列なのであるが、 クライアント側のキャラクタセットの設定が latin1 なので、 MySQL サーバは受信したデータを latin1 文字列と見なす。 つまり、 「E4 BB 99 E7 9F B3 E3 80 80 E6 B5 A9 E6 98 8E」 というデータは 「ä » ™ ç Ÿ ³ ã € € æ µ © æ ˜ Ž」 (見易くするため文字と文字の間に空白を挟んだ) という latin1 文字列として扱われる。

「ä」 (小文字のａウムラウト) の UTF-8 コードは、 「C3 A4」 である。以下同様に、
「»」 (right-pointing double angle quotation mark) は 「C2 BB」
「™」 (trade mark sign) は 「E2 84 A2」
「ç」 (latin small letter c with cedilla) は 「C3 A7」
「Ÿ」 (latin capital letter Y with diaeresis) は 「C5 B8」
「³」 (superscript three) は 「C2 B3」
「ã」 (latin small letter a with tilde) は 「C3 A3」
「€」 (euro sign) は 「E2 82 AC」
「æ」 (latin small letter ae) は 「C3 A6」
「µ」 (micro sign) は 「C2 B5」
「©」 (copyright sign) は 「C2 A9」
「˜」 (small tilde) は 「CB 9C」
「Ž」 (latin capital letter Z with caron) は 「C5 BD」

したがって、テーブルには 「C3 A4 C2 BB E2 84 A2 C3 A7 C5 B8 C2 B3 C3 A3 E2 82 AC E2 82 AC C3 A6 C2 B5 C2 A9 C3 A6 CB 9C C5 BD」 という 33バイトの UTF-8 コードが格納される。