MySQLリモートDBの結果をローカルCSVファイルに出力する方法

MySQLのデータをCSVファイルに出力したい場合、
SELECT … INTO OUTFILE 構文を使うのがよくあるパターンなのですが、
DBサーバーがリモートホスト上にある場合はこの方法が使えません。

CSVファイルに出力するコマンド

このコマンドで、リモートDBの結果をローカルCSVファイルに出力できます。

構文

mysql -u [ユーザー名] -p -h [DBサーバーホスト名] [DB名] -e "`cat [実行SQLファイル名]`" | sed -e 's/\t/,/g' > [出力CSVファイルパス]

実行例

$ mysql -u test -p -h dbhost testdb -e "`cat outcsv.sql`" | sed -e 's/\t/,/g' > /tmp/test.csv

いろいろ調べた結果たどり着いたのが以上の方法です。
理由とか気にならない人は、ここ以下の内容を読む必要はないですｗ

SELECT … INTO OUTFILE ではリモートDBの結果をローカルCSVに出力できない

MySQLデータベースから、SELECT文の実行結果をCSVファイルとして取得する場合、
大体は SELECT … INTO OUTFILE を使うのが定石。
SQL実行ユーザーにFILE権限があれば、指定先にCSVファイルが出力されます。

構文

mysql> SELECT * FROM [table] INTO OUTFILE "[file]" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '[クォート文字]';

ただし、この方法で指定するCSVファイルの出力パスは、
DBサーバー側のファイルパスを指しています。
そのため、サーバーとクライアントのホストが異なる場合は、
クライアントホストのファイルパスを探しても、CSVファイルは出力されていません。
当たり前ですね。

そのため、クライアント側にCSVファイルを出力する方法は無いかと探してみたのですが、マニュアルにこういう記述を見つけました。

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

つまり、SELECT … INTO OUTFILE を使ってクライアントホスト上にCSVファイルを出力する方法は無いようです。

mysql -e “SELECT …” > [file_name] 形式で実行

それでは、SQLファイルを作成して、マニュアルに書かれている通りに、-e オプションを使って実行してみます。

出力対象のテスト用データ

今回のCSV出力対象となるテストデータはこんな感じです。

mysql> SELECT id, name FROM test;
+------+----------+
| id   | name     |
+------+----------+
| 1    | AAAAAAA  |
| 2    | BBBBBBBB |
| 3    | CCCCCCCC |
+------+----------+
3 rows in set (0.01 sec)

CSV出力用のSQLファイル作成

CSV出力データを取得するためのSQLファイル、outcsv.sqlを作成します。（コマンドラインに直接SQL文書いてもいいですが）
SELECT対象カラムは、concat関数などで両サイドをダブルクォートしてあげるといいでしょう。

こういう内容で作成しました。

SELECT
  CONCAT('"', id, '"') AS id,
  CONCAT('"', name, '"') AS name
FROM test;

コマンド実行

上記testテーブルから、outcsv.sqlを使って結果をファイルに出力します。

$ mysql -u test -p -h dbhost testdb -e "`cat outcsv.sql`" > result1

実行結果ファイル(result1)の内容

出力されたファイルの中身はこうなりました。（各値の間にはtabが存在します）

id name
"1" "AAAAAAA"
"2" "BBBBBBBB"
"3" "CCCCCCCC"

この実行結果はタブ区切りで、特にタブ以外のセパレータを指定する方法もありません。
MySQL の結果はタブ区切りでしか標準出力できないということが、
こちらの記事で大変詳しく解説されています。
MySQL の結果を csv 形式で標準出力させたい – BOOLEANLABEL

タブ区切りファイルをCSVに変換

結局、取得できたタブ区切りファイルを置換することで対応します。
例えばsedでやるならこうなります。

sedによる実行例

$ sed -e 's/\t/,/g' result1 > result2

変換結果ファイル(result2)の内容

id,name
"1","AAAAAAA"
"2","BBBBBBBB"
"3","CCCCCCCC"

こんな感じですね。

１行のコマンドでCSV出力を実行する

というわけで１行で書いてしまうなら、こうなります。

$ mysql -u test -p -h dbhost testdb -e "`cat outcsv.sql`" | sed -e 's/\t/,/g' > test.csv

CSV出力結果ファイル(test.csv)の内容

id,name
"1","AAAAAAA"
"2","BBBBBBBB"
"3","CCCCCCCC"

スマートな方法は見つからなかったのですが、
リモートDBの結果をどうしてもクライアントホストにCSV出力したい場合は参考にしてみてください。