前回記事：MySQLでテーブルを作成・削除・変更する方法では、MySQLにおけるテーブルの追加・変更・削除についてご紹介しました。今回は、MySQLを便利にしている機能のうちの一つである、CSVファイル形式でデータを入出力する方法についてご紹介したいと思います。
目次
１．１ CSVファイルに出力する「INTO OUTFILE」
１．２ FIELDS オプションの詳細
１．３ LINES オプションの詳細
１．４ コマンド実行例と実行結果
１．５ エラーの発生原因と対処法
２．１ CSVファイルから入力する「LOAD DATA INFILE」
２．２ FIELDS オプションの詳細
２．３ LINES オプションの詳細
２．４ IGNOREオプションの詳細
２．５ コマンド実行例と実行結果
１．SELECT結果をCSVファイルにエクスポートする
１．１ CSVファイルに出力する「INTO OUTFILE」
SELECT文の実行結果をCSVファイルに出力するには、SELECT文の末尾に「INTO OUTFILE」と書き、ファイル名を指定します。
INTO OUTFILE文の基本の構文は、次の通りです。
SELECT * FROM [テーブル名] INTO OUTFILE '[出力先ファイルパス]';
通常のSELECT文と同じく、「*」は全カラムを出力対象とするときに指定します。出力対象を絞りたい場合は、その部分に対象のカラムをカンマ区切りで列挙します。
SELECT [カラム1],[カラム2],... FROM [テーブル名] INTO OUTFILE '[出力先ファイルパス]';
さらにオプションを付けることで、区切り文字や改行コードを指定することができます。 その場合の構文は、次のようになります。
SELECT * FROM [テーブル名] INTO OUTFILE '[出力先ファイルパス]'
FIELDS
TERMINATED BY '[区切り文字]'
ENCLOSED BY '[フィールドを囲む文字]'
LINES
TERMINATED BY '[改行コード]';
１．２ FIELDS オプションの詳細
FIELDSオプションでは、２つの項目が指定可能です。
変更が不要な方については、記述を省略します。
■TERMINATED BY
区切り文字を指定します。
デフォルトではタブ区切りになっています。
■ENCLOSED BY
フィールドを囲む文字を指定します。
デフォルトでは、囲み文字はありません。
１．３ LINES オプションの詳細
LINESオプションには、行区切り（改行）が指定可能です。
変更が不要な場合は、記述を省略します。
■TERMINATED BY
改行コードを指定します。
デフォルトではLF（\n）になっています。
１．４ コマンド実行例と実行結果
まず、FIELDSやLINESを指定せずに実行してみます。
SELECT * FROM db_01.USER_DATA INTO OUTFILE 'D:test.csv';
CSVファイルには、次のようにタブ区切りのLFで出力されました。
次に、FIELDSやLINESを指定して実行します。
SELECT * FROM db_01.USER_DATA INTO OUTFILE 'D:test2.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
CSVファイルには、設定した通りカンマ区切りのCRLFで出力されました。
１．５ エラーの発生原因と対処法
上の例では任意のフォルダを出力先に指定していますが、デフォルトのままでは次のようなエラーになります。
これを回避するためには、「my.ini」を管理者権限のエディタで開き、「secure-file-priv」の行を次のように書き換えます。
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"
の先頭に「#」を付けてコメントアウトし、パスを空欄に書き換える。
書き換えが終わったら、サービスを再起動することで設定が反映されます。
なお「""」への書き換えは、必須ではありません。任意のフォルダを指定してもOKですし、デフォルトで問題なければそのまま使用して下さい。
「my.ini」の場所については、MySQL5.7のWindows版の場合、特に変更しなければ次の場所にあります。
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
２．CSVファイルからデータをインポートする
２．１ CSVファイルから入力する「LOAD DATA INFILE」
CSVファイルからデータを入力（インポート）したい場合、「LOAD DATA INFILE文」を使用します。
基本の構文は、次の通りです。
LOAD DATA INFILE '[入力元ファイル名]' INTO TABLE [テーブル名]
FIELDS
TERMINATED BY '[区切り文字]'
ENCLOSED BY '[フィールドを囲む文字]'
ESCAPED BY '[エスケープシーケンス]'
LINES
TERMINATED BY '[改行コード]'
IGNORE [整数] LINES;
２．２ FIELDS オプションの詳細
入力の場合のFIELDSオプションには、３つの項目が指定可能です。
変更が不要な項目については、記述を省略します。
■TERMINATED BY
出力と同じく、区切り文字を指定します。
デフォルトではタブ区切りになっています。
■ENCLOSED BY
出力と同じく、フィールドを囲む文字を指定します。
デフォルトでは、囲み文字はありません。
■ESCAPED BY
エスケープシーケンスを指定します。
２．３ LINES オプションの詳細
出力と同じく、LINESオプションには行区切り（改行）が指定可能です。
変更が不要な場合は記述を省略します。
■TERMINATED BY
出力と同じく、改行コードを指定します。
デフォルトではLF（\n）になっています。
例えばこのようなCSVファイルの取り込みを行いたい場合、次のように指定します。
LOAD DATA INFILE 'test_in.csv'
INTO TABLE db_01.USER_DATA
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
実行した結果、このようにCSVファイルのうち2行目のみ取り込みが行われ、レコードが1行追加されました。
なお出力の場合と異なり入力元のファイルを置く場所は固定のため、パスの指定は不要です。
デフォルトから変更していない場合、次の通りです。
C:\ProgramData\MySQL\MySQL Server 5.7\Data\[データベース名]
参考まで、この「Data」フォルダの設定は「my.ini」の「datadir」の行に記述されていますが、データベースのデータ本体が保存されているフォルダのため、不用意に書き換えないようご注意下さい。
３．まとめ
CSVファイルを用いての入出力は、管理者権限が必要だったりリモートホストでは難しいという難点がありますが、使用できる環境であればとても便利なので、ぜひ活用してみて下さい。
次回はsqldumpを用いてデータベースをエクスポートする方法を、ご紹介したいと思います。
よければ合わせてご覧下さい。
関連記事：
無料でも使える高機能データベース「MySQL」とは
関連記事：
MySQLのダウンロード＆インストールと初期設定方法
関連記事：
MySQLのデータベースに接続する３つの方法と接続手順
関連記事：
便利な公式ツールMySQL Workbenchの使い方と日本語化方法
関連記事：
MySQLでユーザを作成し、権限を設定する方法
関連記事：
MySQLで新規にデータベースを作成・削除する方法
関連記事：
MySQLテーブル設計のための、よく使うデータ型まとめ
