読者です 読者をやめる 読者になる 読者になる

長生村本郷Engineers'Blog

千葉県長生村本郷育ちのエンジニアが書いているブログ

MySQL SQL結果をINTO OUTFILEを使用せずCSV取得

f:id:kenzo0107:20160408142308p:plain

概要

MySQL SELECT文の結果をcsvで取得する際
以下のようにCLI上で実行することで取得できます。

$ mysql -u <user> -p<password> <db_name>
mysql> SELECT * FROM [table] WHERE hoge=hoge INTO OUTFILE "/tmp/output.csv" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

ですが、mysql ログインユーザの権限にFILEがない場合
以下のようなエラーで出力できません。

RROR 1 (HY000): Can't create/write to file '/tmp/output.csv' (Errcode: 13)

FILE のREAD/WRITE権限を付与すれば問題ないですが
権限周りをいじりたくないときなどあるかと思います。

本番環境のDBで権限周りがブラックボックス化していて
FLUSH PRIVILEGES すると何か障害が出るんじゃないかとか汗

その際に実施したことを以下まとめました。

考え方

TXT として取得して sedcsv ファイルに加工する、
という方法で実行しています。

例) 以下のようなSQL実行結果を取得したとすると

$ less output.txt

商品1    2    1,000    2,000
商品2    3    1,500    3,000

Excel で表示されるように加工すると

$ less output.txt

"商品1", "2","1,000","2,000"
"商品1", "3","1,500","3,000"

手順

query 結果を ouput.txt に出力

$ mysql -u <user> -p<password> <db_name> -e"<query>"  > output.txt

Excel用に加工

  • 力技
// 各行の一番前(「^」)に「"」を付加
$ cat output.txt | sed -e 's/^/"/g' > output2.txt

"商品1    2    1,000    2,000
"商品2    3    1,500    3,000

// 各行の一番後(「$」)に「"」を付加
$ cat output2.txt | sed -e 's/$/"/g' > output3.txt


"商品1    2    1,000    2,000"
"商品2    3    1,500    3,000"

// タブ「/t」を「","」に変更
$ cat output3.txt | sed -e 's/\t/","/g' > ouptut4.txt

"商品1","2","1,000","2,000"
"商品2","3","1,500","3,000"

文字コード変更

このCSVを利用する方(提出先)の方の文字コードに合わせます。 提出先では Shift JIS がデフォルトとのことで そこに変更します。

$ nkf -g output4.txt
UTF-8
$ nkf -sLw output4.txt > output.csv

$ nkf -g output.csv
Shift_JIS

これで INTO OUTFILE を利用せず CSVファイルを取得できました。 ouput.csv

おまけ

ファイル名に日付をつける。

$ mv output.csv output_`date '+%Y%m%d'`.csv
$ ls 

output.csv
output_20160314.csv