本稿では MySQL で SELECT した結果を CSV ファイルとして出力する方法について解説します。 また、よく陥りがちなエラーの回避方法についても解説します。
構文
SELECT した結果を CSV ファイルとして出力するには、次のような SELECT 文を実行します。
SELECT column1, column2, column3
FROM table_name
INTO OUTFILE '/tmp/data.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';
CSVファイルの出力する際に決めるべきことに、区切り文字、囲い文字、エスケープ文字、改行コードがあります。 上の SQL では、それぞれを指定するようにしています。
CSVの区切り文字は FIELDS TERMINATED BY で指定します。 上の例ではカンマ(,)を区切り文字に指定していますが、もしタブ区切りのTSVとして出力したい場合は\t を指定すれば良いです。
データを囲う文字は ENCLOSED BY で指定します。 また、データを囲った場合にデータをエスケープするための文字を ESCAPED BY で指定します。 もしデータを囲う必要がなければ必要はありません。
CSV ファイルの各行の改行コードは LINES TERMINATED BY で指定します。
よく発生するエラー
上で説明した SELECT 文を実行した際にエラーが発生する場合があります。 ここでは、よく陥りがちなエラーについて解説します。
エラー「ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement」
このエラーは、MySQL サーバーがファイルの書込みを制限するオプションを付けて起動されている場合に発生します。 secure-file-priv というオプションです。 このオプションが有効な場合、ファイルの出力先が制限されます。
現在どこにファイルを出力することが許されているのかは、SELECT @@global.secure_file_priv; という SQL で確かめることができます。
mysql> SELECT @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /tmp |
+---------------------------+
上の結果の例のような場合、/tmp フォルダにファイルを出力することができます。 もし値が NULL の場合は、どこへの出力も許可していません。 また、この値を空文字(“”)に変更すると、どこにでもファイルを出力できるようにできます。
もしこのフォルダを変更したい場合は、my.ini もしくは my.cnf に次のような設定を行い、MySQL サーバーを再起動することで変更できます。
...
[mysqld]
secure-file-priv="/tmp"
...
おわりに
本稿では MySQL で SELECT した結果を CSV ファイルとして出力する方法について解説しました。 繰り返しになりますが、どのような形式の CSV ファイルを出力するのかを確かめた上で、正確にオプションを指定することが大切です。
