とある MySQL のダンプファイルをインポートしようとしたところ、次のような警告が表示されました。
Warning (Code 1300): Invalid utf8 character string: 'FFFFFF'
インポートに失敗しているわけではないですがやや気持ち悪いです。
原因
blob
とかのバイナリを含むテーブルを mysqldump
して mysql
でインポートしようとしたためです。
create table t (x blob, z text); insert into t values (x'FFFF0123456789ABCDEF', 'あいうえお'); select hex(x), z from t; +----------------------+-----------------+ | hex(x) | z | +----------------------+-----------------+ | FFFF0123456789ABCDEF | あいうえお | +----------------------+-----------------+
mysqldump test t > dump.sql mysql test < dump.sql #=> Warning (Code 1300): Invalid utf8 character string: 'FFFF01'
バイナリを含むテーブルを普通に mysqldump
すると文字列リテラルにバイナリのバイトシーケンスがそのまま出力されます(エスケープとかされないという意味)。
そのようなダンプファイルを mysql
に流すと、リテラルの中身を utf8
として読もうとして↑のような警告になります(エラーになることもあったっけ?)。
もちろん SQL 的にエスケープが必要なものはエスケープされます(シングルクオートとか)。
insert into t values (x'302739', 'あいうえお');
mysqldump test t | less #=> INSERT INTO `t` VALUES ('0\'9','あいうえお');
解決方法(hex-blob)
mysqldump
に --hex-blob
オプションを付けるのがスタンダードな解決方法です。
$ mysqldump --help | grep -A1 -- --hex-blob --hex-blob Dump binary strings (BINARY, VARBINARY, BLOB) in hexadecimal format.
このオプションを付けてダンプするとバイナリ部分は16進の形式でダンプされます。
INSERT INTO `t` VALUES (0xFFFF0123456789ABCDEF,'あいうえお');
解決方法(default-character-set)
リテラルの部分を utf8
として解釈しようとするから警告が出ているわけなので、インポート時に --default-character-set=binary
などと指定する方法も考えられます。が・・・これはうまくいきません。
mysql test --default-character-set=binary < dump.sql #=> Warning (Code 1300): Invalid utf8 character string: 'FFFF01'
なぜなら普通に mysqldump
するとダンプファイルの先頭のあたりに SET NAMES utf8
が出力されるためです。
無理やり削ってやれば大丈夫です。
sed '/SET NAMES/d' dump.sql | mysql test --default-character-set=binary
ただし、この方法はダンプファイルに出力されている文字のエンコーディングと、データベースの(テーブルの(カラムの))エンコーディングが一致している前提が必要です。もっとも、今日日はどちらも utf8
とか utf8mb4
などで揃っているだろうのでまず問題は無いと思いますが。
あるいはダンプ時に --default-character-set=binary
を指定しても良いです。
mysqldump --default-character-set=binary test t > dump.sql mysql test < dump.sql
あるいはダンプ時に SET NAMES
を出力しないオプションもあります。これなら mysql
の --default-character-set=binary
を活かせられます。
mysqldump --skip-set-charset test t > dump.sql mysql --default-character-set=binary test < dump.sql
mysqldump の default-character-set とは
そもそも mysqldump
に指定する --default-character-set
とはいったいなんなのか・・これはダンプファイルに出力される文字エンコーディングです。
例えば次のように複数の文字エンコーディングが混在しているようなテーブルで、
create table t ( s text charset sjis, e text charset ujis, u text charset utf8 ); insert into t values ('あ', 'あ', 'あ'); select * from t;
元の文字エンコーディングがなんであったとしても mysqldump
の --default-character-set
で指定したエンコーディングに変換されて出力されます。
mysqldump --default-character-set=sjis test t | grep INSERT | nkf -Sw #=> INSERT INTO `t` VALUES ('あ','あ','あ'); mysqldump --default-character-set=ujis test t | grep INSERT | nkf -Ew #=> INSERT INTO `t` VALUES ('あ','あ','あ'); mysqldump --default-character-set=utf8 test t | grep INSERT #=> INSERT INTO `t` VALUES ('あ','あ','あ');
--default-character-set=binary
なら素のまま出力されます(ので化けてる、空文字に見えてる部分には印字不可能なバイトシーケンスがあります)。
mysqldump --default-character-set=binary test t | grep INSERT | nkf -Sw #=> INSERT INTO `t` VALUES ('あ','、「','縺); mysqldump --default-character-set=binary test t | grep INSERT | nkf -Ew #=> INSERT INTO `t` VALUES ('','あ','); mysqldump --default-character-set=binary test t | grep INSERT #=> INSERT INTO `t` VALUES ('','','あ');
さいごに
バイナリを含むテーブルのダンプには --hex-blob
を付けましょう。~/.my.cnf
に書いても可。
--default-character-set=binary
は個人的にオススメできません。本来テキストファイルであるはずのファンプファイルにバイナリのバイトシーケンスが含まれるのは違和感がありすぎます。--default-character-set=binary
を使うことで無駄な変換が行われず、複数のエンコーディングを同時に使っている状況なら意義があることもあるかもしれませんが、今日日はデータベースも mysqldump
も utf8
で統一されているでしょう。もしどうしてもおかしなエンコーディングを使う必要があるなら(sjis
とか)それこそ BLOB
とかでバイナリで放り込めばいいんじゃないでしょうか。