◆ クライアントとサーバーのキャラクターセットの確認 †
まず、自分の状態を知るための SQL 文
SHOW VARIABLES LIKE 'char%';
を覚えてください。
クライアントのキャラクターセットが binary
サーバーのキャラクターセットが ujis
の場合、以下のように表示される。
mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | binary |
| character_set_connection | binary |
| character_set_database | ujis |
| character_set_results | binary |
| character_set_server | ujis |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
7 rows in set (0.00 sec)
サーバーとクライアントのキャラクターセットが同じ場合は、
文字の自動変換は起きず、
文字化けは発生しない。
| character_set_client | ujis |
| character_set_connection | ujis |
| character_set_database | ujis |
| character_set_results | ujis |
| character_set_server | ujis |
クライアントが binary の場合は、文字の自動変換は起きない。
文字化けは発生しない。
しかし、クライアントの SQL 文のエスケープに注意すること。
| character_set_client | binary |
| character_set_connection | binary |
| character_set_database | ujis |
| character_set_results | binary |
| character_set_server | ujis |
クライアントが latin1 で、
サーバーが日本語の場合は、
確実に文字が壊れる。
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | ujis |
| character_set_results | latin1 |
| character_set_server | ujis |
クライアントとサーバーがマルチバイトキャラクターセットだが、
違うキャラクターセットの場合、
幾つかの文字は破壊されるかもしれない。
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | ujis |
| character_set_results | utf8 |
| character_set_server | ujis |
↑
◆ 4.1 では何が変わったの？ †
CHAR(10) は 10 バイトから 10 文字に変わった。
char() に、全てのバイト列が入らなくなった。例えば、ujis のフィールドには、EUC-JP に定義されるコード空間のバイトしか INSERT できない。全てを入れるパッチは http://www.mysql.gr.jp/frame/modules/bwiki/?Contrib
パスワードの保存形式が変わった。(長くなった)
文字コードの自動変換機能が追加された。
mysqldump の標準キャラクターセットがどうコンパイルしても utf8 になる
データベース、テーブル、フィールド、それぞれ個別にキャラクターセットを指定できるようになった。
データベース名、テーブル名は、OS 上では unicode に直されて保存されるように変わった(ディレクトリ名、ファイル名が、utf8 キャラクターセットで書かれるようになった)。4.0 まではバイナリ列がそのままデータベース名(=ディレクトリ名)、テーブル名(=ファイル名)になっていた。
timestamp 型の表示フォーマットが変わった。4.0 までは 「20050116214504」 であったが、4.1 では 「2005-01-16 21:45:50」。アプリの作り方に注意。
HEAP(MEMORY) テーブルのインデックスに、BTREE が加わった。4.0 までの HEAP テーブルは HASH のみ。(i int, INDEX USING BTREE (i)) TYPE=MEMORY;
sub query の追加
NDB クラスターの追加
GIS追加
utf8 , ucs2 キャラクターセットの追加
↑
◆ CHAR の定義の違いによる弊害(桁数が変わる) †
これは 3.X, 4.0 から 4.1 に upgrade したときに遭遇する問題です。
3.X, 4.0 で char(10) と定義していたフィールドが、 4.1 に uprade した瞬間に、char(3) もしくは char(5) 扱いになってしまいます。
なぜか?
ujis は、最大で 3バイトを使用して 1文字を表現する。sjis は最大で 2バイトを使用して sjis 1文字を表現する。
4.0 で CHAR(10) と定義した。これは 10 バイト。
4.1 に upgrade したら、10バイトを、X文字にしなければならない。
そこで、ujis の場合は、10バイト -> 3(ujis)文字となる。
↑
◆ 文字コードの自動変換機能による弊害(文字化け) †
4.0 までは、キャラクターセットはサーバーだけが設定するものであり、クライアントがサーバーのキャラクターセットに自動的に合せて動作していました。
ところが
4.1 になってから、サーバー、クライアントがそれぞれのキャラクターセットで動作するようになりました。
例えば、
クライアントが sjis で動作
サーバーが ujis で動作
している場合、サーバーは、クライアントに対しては常に sjis のデータを送るようになります。
サーバーは ujis のデータを sjis に変換して、クライアントに送るのです。
これは一見便利です。
が、落とし穴があります。
変換できないキャラクターセットの組み合わせの場合
変換しきれない文字があった場合
こういう場合は、文字が ? になったり、期待外れの文字になったりします。
例えば、
クライアントが latin1 で動作
サーバーが sjis で動作
の場合、どうやって sjis を latin1 の文字コードに変換できるというのでしょう？
出来るわけがないので、sjis 文字はあえなく全て ? になってしまいます。
(え、「?」にしなけりゃいいのにって？
私もそう思います。変換できなければ「?」にせず、そのままのバイトのままにしておけば クライアントが latin1 でも問題が少なかったかもしれません。が、仕様が「?」に変換なので、なんとも。)
例えば、
Unicode を使用してアプリを作っている方は経験されているでしょうが、 Unicode の変換マップというのは統一が取れていません。
同じことが MySQL にもおきています。
MySQL が変換するコードと、あなたが使用している環境が考えるコードと文字の形の組み合わせが一致するとは限りません。
では、クライアントが latin1 で、サーバーが ujis,sjis の状況というのは、起きうるものなのでしょうか？
困ったことに、現状では簡単にこの状態が引き起こってしまいます。
あなたが使用している PHP は、自分でコンパイルしましたか？
あなたが使用している MySQL は、ご自身の手でコンパイルしましたか?
ほとんどの方が、他の誰かがコンパイルしたものを使用していると思います。
では、
あなたが使用している PHP, MySQL のライブラリ(libmysql.dll, libmysqlclient)の標準キャラクターセットは何かご存じですか？
そう、ここが問題なのです。
日本人以外のデベロッパーが作ったバイナリは、ujis,sjis を標準のキャラクターセットにしているわけがありません。
事実 MySQL AB 配布のバイナリは、latin1 が標準です。
それらのバイナリを使って PHP の MySQL モジュールを動かせば(作成すれば)、クライアント(PHP)は latin1 で動作し、サーバーは ujis,sjis で動くことになるのです。こうして日本語文字は破壊されます。
これらの問題を避けるには、方法は3つ。
5.0.13-rc 以上の場合は、mysqld --skip-character-set-client-handshake オプションを必ず指定する
PHP(Ruby,Perl,C,...)のアプリの変更。サーバーに接続した後にすぐ、"SET NAMES キャラクターセット名" という SQL 文を実行する
PHP(Ruby,Perl,C,...)の MySQL モジュールの標準キャラクターセットを、自分が使うキャラクターセットにする。これはアプリの変更はない。しかし、libmysql.dll, libmysqlclient のコンパイルし直しが発生する。
ただし、サーバーもクライアントライブラリーも5.0.13-rc 以上の場合、skip-character-set-client-handshake オプションが mysqld に指定されていれば、この作業は不要。
↑
◆ mysqldump の仕様変更の弊害(文字化け) †
mysqldump は、3.X, 4.0 までは、コンパイルするときに指定されたキャラクターセットを標準としていました。
4.1 からは、mysqldump はコンパイル時のキャラクターセットを無視して、utf8 を標準とします。(ひどい。普通、mysqldump のキャラクターセットは、mysqlコマンドとかのキャラクターセットと同じだと思って使うよなぁ...)
これは、4.1 の文字コード自動変換機能と組合わさって、最低な状況を起こしてしまいます。
サーバーが ujis, sjis で動いていたとしても、utf8 に変換して dump します。
utf8 はご存じの通り、到底整理されているコードとは言い難く、そのため、dump した内容が壊れる可能性が高いです。
これを避けるためには、mysqldump を実行するとき、必ず default-character-set オプションを指定すべきです。
個人的には以下が好みです。
[mysqldump]
default-character-set=binary
skip-opt
create-options
set-charset
hex-blob
single-transaction
master-data
なお、コンパイル時のキャラクターセットを mysqldump が無視しなくなる、 mysqlコマンドと同じキャラクターセットと同じにするパッチは以下にあります。
http://www.mysql.gr.jp/frame/modules/bwiki/?Contrib
↑
◆ パスワード保存形式の変更(認証エラー、接続失敗) †
4.1 からは、パスワードの長さが長くなりました。
しかし、3.X, 4.0 までのパスワードはそのまま使えます。 3.X, 4.0 までの mysql データベースの内容は、そのまま引き続き使用できます。
