とある 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 とかでバイナリで放り込めばいいんじゃないでしょうか。
