Linuxサーバ

MySQL5.7内のデータをCSVファイルに出力する

MySQLとCSVデータのエクスポートの基本手順

MySQLに格納されたデータをCSVファイルに出力する方法です。

# mysql -u ユーザ名 -p
パスワードを入力
Enter password: パスワードを入力

mysql> use データベース名

Database changed

mysql> select * from テーブル名 into outfile "/out.csv" fields terminated by ',' optionally enclosed by '"' ;

上記が基本的な構文となりますが、自分の場合スムーズには進みませんでしたので以下に問題となった点を書き残して置きます。

 

ちなみに、CSVファイルのインポートについては下記をご参照ください。


MySQLに郵便番号CSVファイルを取り込むPartt2　スクリプトを使用する

 

発生したエラー内容

自分の場合、MySQLのユーザとしていきなりrootを使用したのですが、まず発生したのは以下の内容。

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

調べるとどうやらファイルのインポート・エクスポートに関してMySQL5.7系より制限が入っており（セキュリティ的なものでしょう）、my.cnf内の記述で制御可能というおはなし。まずは現状を調べてみます。

mysql> select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+
1 row in set (0.00 sec)

どうやらディレクトリが指定されている様子。ということでエクスポート先を上記のディレクトリ内に指定してリトライ。

mysql> select * from テーブル名 into outfile "/var/lib/mysql-files/out.csv" fields terminated by ',' optionally enclosed by '"' ;
Query OK, 896 rows affected (0.14 sec)

あっさり成功しました（汗

 

ちなみにこの件でGoogle先生に色々聞いてみたときの反応だと、多くの場合では今回「var/lib/mysql-files」と指定されている値が「null」になっており、my.cnfに「secure-file-priv=""」を追記してMySQLを再起動することで任意のディレクトリを使用したインポート・エクスポートが可能になるという回答でした。

 

ただ、本家の目的（であろうと思われる）セキュリティ的なことを鑑みるに空白を指定するのもどうかと思いますし、Ubuntuでのデフォルトのディレクトリがしていされているのならそこを使用すればよかろう、という結論で今回は落ち着きました。

 

出力したCSVをエクセルで開いて眺めてみると…

なんと！列名が出力されてないじゃないですか！！

 

どうも列名を追加するオプションはなさそうで、どうにか列名も一緒に出力する術はないものか？と調べてみましたが

select '列名1','列名2',・・・・・・

union

select * from　～

というように

  1. まず１行目に列名を出力する
  2. UNIONを用いて2行目以降に実際のデータを繋げる

という人力というかアナログというか困るような手法を使用するようです。。。

 

MySQL5.7系でCSVへ出力する際の流れをまとめる

久しぶりにMySQLを触ったのもありますが、結構手間取った印象だったので内容をまとめておきます。

  1. 基本的な構文:select * from テーブル名 into outfile "/var/lib/mysql-files/out.csv" fields terminated by ',' optionally enclosed by '"' ;
  2. secure-file-privオプションが効いている場合は所定のディレクトリへ出力する。またはmy.cnfを編集してsecure-file-priv=""を追記してMySQLを再起動する
  3. 列名も一緒に出力する場合ユニオンクエリを活用して1行目に列名、2行目以降に実際のデータを出力するかたちをとる

といった流れになります。

 

ところで、なぜいきなりこんなことを思い立ったのかと言いますと…

  1. 従来、SEO対策として「All In One SEO Pack」を使用してきた
  2. 現在使用しているテーマはAffinger系
  3. 両者が干渉するおそれがある、という噂を耳にした
  4. 実際、DescriptionやTitle等でAll In ～の機能を利用して書いた記事とAffinger内の機能を利用して書いた記事が混在している
  5. できればAll～を外してしまってシンプルな構成にしたい

と考えたとき、TitleやDescription内の文言がどのようにデータベースへ格納されているのかを見たかったんです。以前ならAccessが入ったPCがあったのでODBCでつないでそのまま中身を見ていたんですが今となってはそれもできず…

 

と、ここまで書いてて思ったんですがもしかしたらphpMyAdminでもインストール・設定したらみれるんじゃないですかね？だとしたら結構無駄な試みだった気がするんですが…

 

まあ、たまにはいいか。こんなことでもないとなかなか更新する機会もなく放置ぎみになってしまってたブログだし、せっかくなので投稿しておきます。

オススメの記事

-Linuxサーバ
-,