Webサイト制作コースのお申し込みはこちら

実務をはじめとしてCSV形式でデータが欲しいと要求されるケースは頻繁に訪れます。

本記事では、MySQLのテーブルデータをCSVファイルに出力する方法についてご紹介していきたいと思います。

MySQLでのCSV出力は簡単


MySQLでテーブルデータをCSVファイルに出力するのは非常に簡単です。

SELECT文に「INTO OUTFILE」を記述することでCSVファイル出力が可能となります。

INTO OUTFILEの基本構文を確認しよう

まずINTO OUTFILEの基本構文をご紹介していきたいと思います。

SELECT カラム名, ... FROM テーブル名 INTO OUTFILE '出力ファイルパス';

基本となるSQLは通常のデータ抽出と全く同じで、SQLの末尾に「INTO OUTFILE」を追加することで指定したパスにCSVファイルを出力することが可能となります。

INTO OUTFILEのオプション設定を確認しよう

INTO OUTFILEには「FIELS」オプションと「LINES」オプションが存在します。

FIELSオプション

FIELSオプションでは、「TERMINATED BY」「ENCLOSED BY」「ESCAPED BY」の3種類の項目が指定可能です。

  • 「TERMINATED BY」では区切り文字を指定します。
  • 「ENCLOSED BY」ではフィールドを囲む文字を指定します。
  • 「ESCAPED BY」ではエスケープ文字を指定します。

LINESオプション

LINESオプションでは「TERMINATED BY」で改行コードを指定することが可能です。

実際にテーブルからデータを取得してCSVファイルを出力してみよう

ここからは実際にデータベースのテーブルからデータを取得してCSVファイル出力するサンプルSQLをご紹介します。

まず今回利用するテーブルとして「user」テーブルを下記のデータで作成しています。

+------+---------------+
| id   | user_name     |
+------+---------------+
|    1 | ポテパン1      |
|    2 | ポテパン2      |
|    3 | ポテパン3      |
+------+---------------+

実際にサンプルの「user」テーブルからCSVファイルを出力するサンプルSQLが下記となります。

select * from user into outfile '/Users/***/Desktop/sql/sample.csv' fields terminated by ',' enclosed by '"' escaped by '"' lines terminated by '\r\n';
ポテパンダの一言メモ

***の部分にはコンピューターのユーザー名を設定してください。

上記サンプルでは区切り文字「,」、フィールドの囲み文字「”」、エスケープ文字「”」、改行コードは「\r\n」で指定しています。

オプション項目に関しては省略可能ですので、デフォルト値以外の値を設定したい場合に記述するようにしましょう。

出力されたCSVの中身は下記のようになります。

"1","ポテパン1"
"2","ポテパン2"
"3","ポテパン3"

出力したCSVファイルにヘッダー項目を付ける方法

実際にCSVを出力してみると上記サンプルSQLの場合、テーブルのカラム名にあたるヘッダー項目がなく、抽出した値のみが記載されたCSVファイルが出力されています。

フィールド値を記載したCSVファイルを作成するには「UNION句」を利用します。

select 'id', 'name' union select * from user into outfile '/Users/***/Desktop/sql/sample.csv' fields terminated by ',' enclosed by '"' escaped by '"' lines terminated by '\r\n';

出力されたCSVファイルを確認すると「UNION句」の前で指定したフィールド名がヘッダー情報として記載されていることをご確認頂けます。

"id","name"
"1","ポテパン1"
"2","ポテパン2"
"3","ポテパン3"

MySQLでCSV出力を実施した際に起こるエラー例


INTO OUTFILEでCSV出力を試すと下記のようなエラーが起こる人が多いようです。

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

このエラーは公式サイトに記載されているようにファイルの「INPUT」「OUTPUT」が許可されていないことが原因となります。

エラーの原因を特定しよう

まずは下記のコマンドを実行してみてください。

select @@global.secure_file_priv;

実行した結果が下記のように「NULL」が設定されている場合、ファイルの入出力が許可されていませんので「my.cnf」を修正してあげる必要があります。

+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| NULL                      |
+---------------------------+

エラーとなっている原因を解決しよう

今回筆者の環境で変更した内容をコマンドと共にご紹介していきます。

まずデフォルトの「my.cnf」の場所を下記コマンドで確認します。

find /usr/local/Cellar/mysql -name "my*.cnf"

筆者の環境では下記のPATHに「my.cnf」ファイルがありました。

/usr/local/Cellar/mysql/8.0.19/.bottle/etc/my.cnf

次に上記で確認した「my.cnf」ファイルを「/etc」フォルダ配下にコピーします。

sudo cp /usr/local/Cellar/mysql/8.0.19/.bottle/etc/my.cnf /etc/my.cnf

vimコマンドで「my.cnf」ファイルを開きます。

sudo vim /etc/my.cnf 

[mysqlId]のすぐ下に下記の1行を追加しました。

secure-file-priv = ""
ポテパンダの一言メモ

既に「secure-file-priv = xxx」の指定が存在する場合、コメントアウトして設定を変更してみてください。

エラーの原因が解決しているかを確認しよう

再度MySQLにログインして下記のコマンドを実行してみましょう。

select @@global.secure_file_priv;

今回は前回と異なり結果が「Null」ではなく下記のようになっているはずです。

+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
|                           |
+---------------------------+

これでファイル入出力の許可設定は修正出来たことになりますので、再度MySQLでのCSV出力を試してみてください。

ポテパンダの一言メモ

CSV出力が出来ない原因はユーザーの環境により様々です。
今回ご紹介した内容はあくまでエラー原因の1つに過ぎないため、解決しない場合には別の方法を検討してみてください。

さいごに:MySQLでCSVを出力するのは意外と簡単

本記事では、MySQLでCSVファイルを出力する方法についてご紹介してきました。

CSV出力と聞くと何かツールなどと連携して複雑な手続きが必要なのではと考える方もいらっしゃいますが、MySQL標準機能だけで簡単に出力することが可能です。

CSVファイルでデータが必要になることは頻繁にありますので、ぜひ使い方を覚えて活用してみてください。

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。