mysqlで複数行にまたがるカラムをCSVフォーマットで出力する方法

地味なネタですが、今日はまってしまったのでメモっておきます。

とあるとmysqlのデータをselect * from xxx into outfileで外部ファイルに出力したいとします。
テーブルのスキーマは「URL、タイトル、 HTML本体」という構成だとしましょう。HTML本体の部分は改行がたくさん入っています。

このデータをあとからcsvとして正しく読み込めるようなフォーマットで出力したいわけです。

まずは普通に出力してみます。

select url, title, html from table into outfile "output.tsv";

そうするとこのような結果になります。

http://hoge.com タイトルですよ \
\
\

〜途中省略〜\

mysql のinto outfileはデフォルトだとTAB区切りで、複数にまたがるデータについてはバックスラッシュでエスケープするようです。
区切り文字はなんでもいいんですが、改行している部分がこれだとCSVとしてはあつかえませんね。

例えばPerlでText::CSVで読み込むためには、複数行にまたがるカラムの場合、ダブルクオートで囲ってやる必要があります。

つまり上のサンプルだとこんな感じ

select url, title, html from table into outfile "output.csv" fields terminated by "," enclosed by '"';

"http://hoge.com","タイトルですよ",""
"
"
〜途中省略〜""
"

区切り文字をカンマにしてダブルクオートで囲むようにしました。

これならPerlのText::CSVとかで簡単にパースできそう！・・・と思ったらNG！！

ダブルクオートで囲った内部でダブルクオートがあるケースではmysqlが気を利かせてさらにダブルクオートでエスケープしてくれてるあたり、とてもいい感じなんですが、mysqlではメタ文字のエスケープとして改行の部分にも"が入ってしまいます。

これがよろしくない。

"" を１つのカラムだと認識してしまって、２行目以降でエラーになります。

なので囲み文字はダブルクオート、エスケープの文字をバックスラッシュにしてみます。

select url, title, html from table into outfile "output.csv" fields terminated by "," enclosed by '"' escaped by '\\';

そうするとこのような出力結果になります。

"http://hoge.com","タイトルですよ","\
\
\
〜途中省略〜\\
"

全てのカラムがダブルクオートで囲まれてますね。それと同時にエスケープはバックスラッシュになってます。

さてさて、Text::CSVでパースしてみます。こっち側でもちょっとした工夫があります。

use strict;
use warnings;
use Text::CSV_XS;
use Data::Dumper;

my $csv = Text::CSV_XS->new(
    {
        sep_char            => ',',
        escape_char         => "\\",
        allow_loose_escapes => 1,
        binary              => 1
    }
);

open my $fh, "<", 'output.csv';
while ( my $row = $csv->getline($fh) ) {
    for (@$row) {
	print $_,"\n";
	print "-" x 100,"\n";
    }
}

Text::CSV_XSのコンストラクタでsep_charやescape_charを指定してるのはまぁ普通なんですが、allow_loose_escapesをセットしてるところがミソです。これがないとエラーになります。

Text::CSV_XSのPODにはこんなことが書いてありました。

allow_loose_escapes
By default, parsing fields that have escape_char characters 

that escape characters that do not need to be escaped, like:
my $csv = Text::CSV_XS->new ({ escape_char => "\\" });

$csv->parse (qq{1,"my bar\'s",baz,42});
would result in a parse error. 

Though it is still bad practice to allow this format, 

this option enables you to treat all escape character sequences equal.

ふーむ。
なんだか何言ってんだかいまいちよくわからんけど、結果としてはうまくできたので、まぁいいや。

それにしても地味というかレアケースというか、微妙なハウツーだね、これは。

だれか一人でも参考にする人がいれば、ミラクル！