はじめに
そんなに頻繁に発生する作業ではないのですが、MySQLのSELECT実行結果をCSVファイルで手元に保存しておく方法について、いざやろうとすると以前はどうやっていたのか忘れがちなのでやり方を残しておくことにしました。今日はそんな話です。
手順
{******}の部分と、出力先パスはお使いの環境等に合わせて書き換えてください。
1. tsv 出力
サーバー側プロンプト
user@server:~$ echo "{SQL文}" | mysql -u{user} -p{pass} {db} > /path/to/filename.tsv
2. csv 変換
前項で出力した tsv ファイルに対して下記コマンドを実行してください。
サーバー側プロンプト
user@server:~$ cat filename.tsv | tr "\\t" "," > filename.csv
3. 自分の端末にダウンロード
FTPソフト等でサーバーに接続できる方はそれ使ってもらえればOKです。
今回は scp コマンドで実行したので、ついでにそのサンプルも残しておきます。
ローカル側プロンプト
$ scp {user}@{host}:/serverside/path/to/filename.csv /local/path/to/filename.csv
SQL注意点
SQLで取得したデータの中に改行、タブ、カンマが含まれていると、tsv や csv に変換した際に必要以上に分割が行われてしまいます。その結果、列数が増えてしまいヘッダの項目と合わなくなってしまうことが多々ありました。(ヘッダでは5列しかないのに、中身は6列以上ある、みたいな状態になります。)
csvに変換する前に改行、タブを取り除いておくとこういった事故をある程度防ぐことができます。
ここではREPLACE関数で改行とタブを半角スペースに置換しておく方法を使いました。
なお、面倒ですがこの REPLACE 処理は問題のデータが入ってきそうなカラム全てに適用が必要です。(id など、特定の文字列しか入ってこないカラム等は必要ないです。主に自由入力系のフォームから送信された値を保存するカラムでこの問題が起こりえます)
SELECT
--- char(13) => CR, char(10) => LF, char(9) => tab
REPLACE(
REPLACE(
REPLACE(
REPLACE(columun_name, char(13), ' '),
char(10), ' '),
char(9), ' '),
',', ' ') AS 'column_name'
FROM table_name