MySQL の結果を csv 形式で標準出力させたい

最近は DB の動作を理解しようと、気になったところは MySQL のソースを読んだりしているため、そこらへんで学んだことをちょっとずつ書くことにする。

ちなみに結論を先に述べておくと MySQL の結果を標準出力に出す場合は今のところはタブ区切りでしか出せないようにハードコーディングされているために

% mysql -e "SELECT ..." | sed -e 's/\t/,/g'

みたいに出力を加工するしかないようである。

そもそも MySQL の結果を csv 形式で「ファイルに」出力する方法はある。ひとつはコマンド mysqldump のオプションで指定する方法である。

--fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=...

これらのオプションは -T オプションと共に使用され、LOAD DATA INFILE に対応する節と同じ意味があります。

しかし、このオプションは同時に出力先のディレクトリを指定してやる必要があり、結果はそのディレクトリの配下のテーブル名に応じたファイルに出力されるため、その先を標準出力にすることはできない。

もうひとつは SELECT ... INTO OUTFILE 構文に FIELDS TERMINATED BY ',' を指定する方法である。

SELECT の SELECT ... INTO OUTFILE 'file_name' 型は選択された行をファイルに書き込みます。

ただし、この構文には制約がある。

SELECT ... INTO OUTFILE ステートメントはそもそも、サーバマシン上のテキストファイルにテーブルをすばやく捨てさせる事を意図しています。もしサーバホストではなく、クライアントホスト上に結果ファイルを作成したければ、SELECT ... INTO OUTFILE を利用する事はできません。その場合、クライアントホスト上にファイルを生成する為には、代わりに mysql -e "SELECT ..." > file_name のようなコマンドを利用しなければいけません。

つまり、リモートホストの DB に接続している場合はローカルホストにファイルを生成することはできないということである。もし、リモートホストにファイルを生成した場合はそれを取得するために別途 NFS マウントや SSH などの別の機能が必要となる。

ここまで調べてみたところ、MySQL は意図的に標準出力にはタブ区切り以外では渡さないような実装をしているのではないかと思えてきた。(真実はわからないが、標準出力に出す機能と csv 形式にする機能はあるのに、その組み合わせだけは禁止しているような仕様に見える。)

仕方ないため、普通に SQL 文で結果を取得することにしたが、どうも標準出力させた場合と (sed に渡す) パイプを通した場合でフォーマットが異なることに気づいた。

% mysql -uroot -pvertrigo --protocol=tcp -e "SELECT * FROM help_category limit 5" mysql
+------------------+--------------------+--------------------+-----+
| help_category_id | name               | parent_category_id | url |
+------------------+--------------------+--------------------+-----+
|                1 | Geographic         |                  0 |     |
|                2 | Polygon properties |                 30 |     |
|                3 | WKT                |                 30 |     |
|                4 | Numeric Functions  |                 34 |     |
|                5 | MBR                |                 30 |     |
+------------------+--------------------+--------------------+-----+

% mysql -uroot -pvertrigo --protocol=tcp -e "SELECT * FROM help_category limit 5" mysql | cat
help_category_id        name    parent_category_id      url
1       Geographic      0
2       Polygon properties      30
3       WKT     30
4       Numeric Functions       34
5       MBR     30

MySQL が出力先が標準出力かパイプかどうかを見分けてられるのか？シェルがつないでいるだけなんじゃないの？と思っていたが、調べてみると isatty(3) という関数で調べられるらしい。

#include <unistd.h>
if ( isatty(STDOUT_FILENO) ) {
  // コンソール
} else {
  // パイプ
}

実際に MySQL 内部でもこれで判定している。

if (!isatty(0) || !isatty(1))
{
  status.batch=1; opt_silent=1;
  ignore_errors=0;
}

フラグ変数 opt_silent がセットされると、出力の関数が変わる。print_tab_data がタブ区切りで出力するための関数で、この関数内で区切り文字がタブ文字でハードコーディングされているのがわかる。

else if (opt_silent && verbose <= 2 && !output_tables)
  print_tab_data(result);
else
  print_table_data(result);

for (uint off=1 ; off < mysql_num_fields(result); off++)
{
  (void) tee_fputs("\t", PAGER);
  safe_put_field(cur[off], lengths[off]);
}

以上より、MySQL の結果はタブ区切りでしか標準出力できないようである。

いけむランド

はてダからやってきました

MySQL の結果を csv 形式で標準出力させたい