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

Pocket

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

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

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

構文

実行例

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

 

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

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

構文

ただし、この方法で指定する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出力対象となるテストデータはこんな感じです。

 

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

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

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

 

コマンド実行

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

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

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

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

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

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

sedによる実行例

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

こんな感じですね。

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

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

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

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

 

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

  1. Pingback: mysqlデータのCSV出力

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

Post Navigation