普段はほとんど PostgreSQL を使っていますが、先日 MySQL に触れる機会がありました。
その時のバージョンが MySQL5.1 だったのですが、4byteなutf8(絵文字や中国語など)を扱うためにやったことのメモです。
utf8 と utf8mb4 という文字コード指定
MySQLの世界では
- utf8 は 3byte までの utf8 をサポート
- utf8mb4 は 4byte までの utf8 をサポート
となっているそうです。
これはよく知られた話のようで、「MySQL 絵文字 insert できない」などでネット検索をすると情報がたくさん得られます。
そして絵文字を扱うなら DB は utf8mb4 を選択する、という解決策にたどり着くことができるのですが、utf8mb4 は MySQL5.5.3 以降からのサポートなため、今回の MySQL5.1 な環境では使えません。
じゃあどうすればいいのか、という情報がネットではなかなか見つけられませんでした。
困り果てていたところに、会社の先輩から VARBINARY 型を使うというヒントをもらいました。
このヒントがなかったら、いまごろどうなっていたことか……。
マルチバイト文字列が入りそうなカラムは VARBINARY 型にする
DB の文字コードは utf8 で作成しつつ、マルチバイト文字列が入りそうなカラムは VARBINARY 型でスキーマ定義をすることで、うまく扱うことができました。
3byteも4byteも関係なく、バイナリーデータとして扱ってしまうという方法です。
バイナリーデータだからといって、深刻に困るようなことはありませんでしたが、2点ほどバイナリーにしたデメリットがありました。
- 格納データの上限サイズを決める必要がある
- mysql_enable_utf8 => 1 の対象にならない
格納データの上限サイズを決める必要がある
TEXT型を使うなら、サイズ上限を自分で考えなくても DB がよしなにしてくれると思うのですが、VARBINARY型を使う場合は、スキーマ定義で VARBINARY(byte) と、上限サイズを明示しないといけなくなります。
予想しやすいデータならいいのですが、外部のデータを収集してきて保存しておく場合だと文字数に上限がないケースもあるので、別な問題として調整する必要がでてきます。
mysql_enable_utf8 => 1 の対象にならない
こちらは perl ユーザー特有の問題だと思います。
DBD::mysql のレイヤーで、DB から抽出したデータを自動的に flaged utf8 にしてくれる便利なオプション mysql_enable_utf8 => 1 を使っている人も多いとおもいます。
VARBINARY 型にすると、文字列型ではないので flaged の対象外になるのだと思います。
perl の内部コード flaged utf8 で扱う必要がある場合には、都度、
use Encode qw(decode_utf8);
sub flaged_utf8 {
my $str = @_;
return Encode::is_utf8($str) ? $str : decode_utf8($str);
}
のような処理を通してあげる必要がありました。
このメモを書いたときの環境
参考までに。
- OS: CentOS 6.5 final
- MySQL: 5.1
- DB CharSet: utf8
- Perl: 5.16.3