情報科学屋さんを目指す人のメモ

方法・手順・解説を書き残すブログ。私と同じことを繰り返さずに済むように。

【MySQL】SELECTの結果をCSVファイルに出力してExcelで編集したい

Excel (20) Linux (24) MySQL (1)

日ごとの投稿数を、WordPressのデータベースからCSVファイルに出力しようとしたときのメモ。今回の用途的に、DBが巨大な場合や、TSV→CSV変換のエスケープはあまり考えていないので注意。

INTO OUTFILEを使う方法

最初に試したのは、直接SQLでファイル出力をする方法でした(SELECT ... INTO 構文)。

ひとまず、次のコマンドで、MySQLにログインします。

mysql -u DB_USER -p -D DB_NAME -h DB_HOST
Password: (DB_PASSWORD)

※ログインに必要なパスワードなどの情報は、wp-config.phpに書いてあります。

ログインしたら、MySQLに対して、次のようなクエリを投げました。

mysql> SELECT DATE(post_date) AS dt, COUNT(*) FROM posts WHERE '2016-01-01' <= post_date AND post_date < '2017-01-01' AND post_status = 'publish' GROUP BY DATE(post_date) ORDER BY dt ASC LIMIT 366 INTO OUTFILE '~/posts-2016.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

ポイントは、終盤部分です。「INTO OUTFILE '(ファイル指定)' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'」を加えて、出力しようとしています。

ただ、これは次のエラーになってしまいました。

ERROR 1045 (28000): Access denied for user 'username'@'localhost' (using password: YES)

このエラーは、このテーブルについてFILE権限がないことが原因のようでした。

ELECT の SELECT ... INTO OUTFILE 'file_name' 形式は、選択された行をファイルに書き込みます。このファイルはサーバーホスト上で作成されるため、この構文を使用するには FILE 権限が必要です。 引用元

GRANTしてね、と。

mysql -e オプションを使う

ただ、DB設定には変更を加えたくなかったのと、あくまでMySQLのあるホストのローカルにしか出力できないのが面倒だったので、mysqlコマンドの「-e」オプションを使ってみることにしました。

ただ、そのままだと、タブ区切りで出力されてしまうので、次の通り、sedで簡単にタブ文字をカンマに置き換える処理を挟んでみました。

mysql -u DB_USER -p -D DB_NAME -h DB_HOST -e "SELECT DATE(post_date) AS post_date, COUNT(*) FROM posts WHERE '2016-01-01' <= post_date AND post_date < '2017-01-01' AND post_status = 'publish' GROUP BY DATE(post_date) ORDER BY post_date ASC LIMIT 366" | sed 's/\t/,/g' > ~/posts-2016.csv

あとはそのCSVをWindows環境に持ってきてExcelで開いてみると、こうなりました(Excelで開くだけならTSVでもよかったかも)。

文字化け対策をする

この後、記事タイトルを出力しようとしたところ、Excel上で思いっきり文字化けをしてしまいました。

BOM付きUTF-8のCSVにすればいいのだろう、ということで、nkfコマンドを使って、「nkf --overwrite --oc=UTF-8-BOM ~/posts-2016.csv」を実行してあげました。とりあえず文字化け解消。

最終的なコマンド

最終的に、CSVのエスケープ関連処理も少しして、以下のようなSQLとなりました。

mysql -u DB_USER -p -D DB_NAME -h DB_HOST -e "(SELECT文)" | sed -r -e 's/"/""/g' -e 's/\t/","/g' -e 's/(^|$)/"/g' | nkf --oc=UTF-8-BOM > ~/result.csv

-eオプションで、指定のSQLを実行して、その結果のTSVをCSVに変換しつつ、BOM付きUTF-8に変換して、result.csvとして保存(出力)しています。

おまけ

いろいろ試してみたところ、CSVファイルが途中で切れてしまっていました。何がおかしいのかと思ったのですが、どうやら、GROUP_CONCATの結果の文字数制限に引っ掛かっていたようで、その切れ方が悪いのか、それ以降の結果がまるごと切れてしまっていたようでした。group_concat_max_lenの設定を書き換えてあげることで回避しました。

mysql -u DB_USER -p -D DB_NAME -h DB_HOST -e "set group_concat_max_len = 100000; SELECT DATE(post_date) AS dt, GROUP_CONCAT(post_title SEPARATOR ',') FROM posts WHERE '2016-01-01' <= post_date AND post_date < '2017-01-01' AND post_status = 'publish' GROUP BY DATE(post_date) ORDER BY dt ASC LIMIT 366" | sed -r -e 's/"/""/g' -e 's/\t/","/g' -e 's/(^|$)/"/g' | nkf --overwrite --oc=UTF-8-BOM > ~/posts-2016.csv

MySQLには、array_aggがないんですね。

参考

コメント(0)

新しいコメントを投稿




  • カテゴリ ナビ
  • 著者紹介

    ブログが趣味で、 月間1,000万PV を達成しました。

    自分が困ったことをブログに書けば、次に困る人の参考になって、みんながみんな同じ苦労をせずに済む、というのが原点です。

    最近の関心は、スマホやパソコンに詳しくない人の行動や思考、 そしてそんな人を手助けする方法や枠組み。 また、それに関連するような、"身近な"セキュリティ。

    ※SNS(特にTwitter)でシェアされた記事は、内容の追加・更新を行っています。 必ず、ではありませんが、気に入った記事は積極的にシェアしてみてください。

    RSS | Facebook | Twitter | About