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

最初に試したのは、直接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してね、と。

ただ、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」を実行してあげました。とりあえず文字化け解消。

• 関連：ExcelファイルをUTF-8のCSV形式で保存する方法（Excel 2016最新機能）

最終的に、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がないんですね。

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.9.1 SELECT ... INTO 構文
• MySQLのgroup_concatの結果が短い気がするのは環境変数で制限されているから。 | 三度の飯とエレクトロン
• Linux上で文字コードを変換できるコマンドnkfのオプション一覧
• SQLで複数行をまとめて取りたいとき（ GROUP_CONCAT ） | アライドアーキテクツ エンジニアブログ
• unix - How to convert a tab separated file into comma separated file - Stack Overflow
• fastest way convert tab-delimited file to csv in linux - Stack Overflow
• Post Status « WordPress Codex