MySQL でクエリー結果を CSV にエクスポートする方法【GUI/SQL両対応】

クエリーの実行結果を CSV ファイルとしてエクスポートしたいことがあるかもしれません。

この記事では、MySQL で CSV ファイルを生成する方法をご説明します。

MySQL Workbench で CSV ファイルにエクスポートする (GUI)
SELECT ... INTO OUTFILE を使って CSV ファイルにエクスポートする
1. サーバーシステム変数の secure_file_priv を確認・設定する
2. SELECT ... INTO OUTFILE を使って CSV ファイルを生成する

MySQL Workbench で CSV ファイルにエクスポートする (GUI)

MySQL Workbench の画面から、クエリーの実行結果を CSV ファイルにエクスポートすることができます。

例えば、school_db データベースの students テーブルに対する次のようなクエリーの実行結果を CSV ファイルにエクスポートしたいとします。

テストのために、student_id = 5 の first_name には Yuri, Amy というカンマ入りの値を、student_id = 6 の last_name には、Saito"s というダブルクォート入りの値を保存しています。

SELECT 	student_id, 
		first_name, 
        last_name, 
        gender
FROM 	students;

[実行結果]

このクエリーの実行結果を CSV ファイルにエクスポートするには Export/Import のラベルの横にある Export (Export recordset to an external file) ボタンをクリックします。

Export Resultset の画面が出てくるので、ファイル名と保存先を指定して、フォーマットに CSV を選択して Save します。

保存先のフォルダを確認すると、CSV ファイルが生成されています。

student_id,first_name,last_name,gender
1,Yuta,Tananka,M
2,Sakura,Hata,F
3,Aya,Tanaka,F
4,Hiroki,Suzuki,M
5,"Yuri, Amy",Sasaki,F
6,Ryo,"Saito"s",M

テキストエディタで開いてみると、クエリーの実行結果が CSV のデータとして保存されていることが確認できます。

カンマ区切りの文字は "" で囲まれていますが、テキスト内の " はエスケープされないことに注意してください。

値に " が入ってくる可能性がある場合は、次に説明する SELECT ... INTO OUTFILE を使うことをご検討ください。

また、MySQL Workbench からこの方法で CSV ファイルを生成する場合は、ヘッダーは含まれてしまい、今のところ含めないようにするオプションはありません。

SELECT ... INTO OUTFILE を使って CSV ファイルにエクスポートする

次は、MySQL の SELECT ... INTO OUTFILE を使って、クエリーの実行結果を CSV ファイルにエクスポートしてみます。

SELECT ... INTO OUTFILE では、ファイルは、MySQL サーバー上のファイルシステムに書き出されます。クライアント PC には出力されません。

そして、書き込み先ディレクトリには MySQLユーザー (mysql) が書き込み権限を持っている必要があります。

また、事前に、MySQL のサーバーシステム変数の secure_file_priv にファイルを書き出すためのディレクトリを指定しておく必要があります。

サーバーシステム変数の secure_file_priv を確認・設定する

サーバーシステム変数の secure_file_priv の設定は次のクエリーで確認できます。

SHOW VARIABLES LIKE 'secure_file_priv';

[実行結果]

secure_file_priv が NULL の時は、ファイル読み書き操作がすべて無効化されている状態です。

この状態で文を実行すると次のようなエラーになります。

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

サーバーシステム変数secure_file_priv に有効なパスを設定するには次のようにします。

1. my.cnf ファイルを探す

my.cnf は MySQL サーバーの設定ファイルです。MySQL の起動時に読み込まれ、サーバーの動作やデフォルトの挙動を制御する設定が書かれています。

Windows の場合は拡張子が .ini になり、my.ini のこともあります。

my.cnf ファイルのロケーションは OS やインストール方法によって違いますが /etc/my.cnf になることが多いです。

私の場合は、macOS に MySQL 公式パッケージからインストールした状態で、Mac のシステム設定で MySQL 画面で MySQLのディレクトリが確認できます。

ディレクトリには etc ディレクトリや my.cnf ファイルがなかったので、etc ディレクトリを作成して、my.cnf ファイルを生成しまいた。

2. my.cnf ファイルの [mysqld] セクションに以下を追加する

[mysqld]
secure_file_priv = "/usr/local/mysql-exports"

MySQL がアクセスできる場所にファイル保存用のディレクトリ /usr/local/mysql-exports を作成し、ディレクトリの所有者を MySQL に変更し、MySQL がファイルにアクセス・書き込みできるように権限を与えました。

sudo mkdir -p /usr/local/mysql-exports
sudo chown -R _mysql:_mysql /usr/local/mysql-exports
sudo chmod 755 /usr/local/mysql-exports

作ったディレクトリへのパスを secure_file_priv に指定しました。

my.cnf ファイル変更時に記述ミスがあったり、secure_file_priv で指定したディレクトリに MySQL の権限が足りないと、MySQL サーバーが開始できないようなエラーになる可能性があるので、必ずバックアップを取って編集してください。

3. MySQL を再起動する

MySQL を再起動します。システム設定の MySQL の画面から一度 Stop MySQL Server でストップしてから、Start MySQL Server で起動しなおしました。

これで設定ができたはずなので、もう一度サーバーシステム変数の secure_file_priv の設定を確認するクエリーを実行してみます。

SHOW VARIABLES LIKE 'secure_file_priv';

[実行結果]

my.cnf ファイルに設定したディレクトリが表示されています。

SELECT ... INTO OUTFILE を使って CSV ファイルを生成する

準備ができたので、SELECT ... INTO OUTFILE を使って、クエリーの実行結果を CSV ファイルにエクスポートしてみましょう。

先ほどと同様に、school_db データベースの students テーブルに対する次のようなクエリーの実行結果を CSV ファイルにエクスポートしたいとします。

SELECT 	student_id, 
		first_name, 
        last_name, 
        gender
FROM 	students;

[実行結果]

このクエリーの実行結果を SELECT ... INTO OUTFILE を使って、CSV ファイルにエクスポートするには、次のようにできます。

SELECT student_id, first_name, last_name, gender
	INTO OUTFILE '/usr/local/mysql-exports/students2.csv'
	FIELDS TERMINATED BY ',' 
	OPTIONALLY ENCLOSED BY '"' 
	ESCAPED BY '"'
	LINES TERMINATED BY '\n'
	FROM students;

[実行結果]

INTO OUTFILE で、クエリーの結果を結果を /usr/local/mysql-exports/students2.csv ファイルに出力するように指定しています。

FIELDS TERMINATED BY',' で、各列の区切りにカンマを使うように指定しています。

OPTIONALLY ENCLOSED BY '"' で、文字列型の列だけを " " で囲むようにしています。 OPTIONALLY で必要な時のみとなるはずですが、文字列はすべて "" で囲まれて出てきていました。

ESCAPED BY '"' では、文字列の中にある " を "" のようにエスケープするように指定しています。

LINES TERMINATED BY '\n' では、各行の区切りに改行 (LF) を使うように指定しています。

/usr/local/mysql-exports/ ディレクトリを確認すると、students2.csv ファイルができています。

テキストエディタで開いてみると、クエリーの実行結果が CSV のデータとして保存されていることが確認できます。

1,"Yuta","Tananka","M"
2,"Sakura","Hata","F"
3,"Aya","Tanaka","F"
4,"Hiroki","Suzuki","M"
5,"Yuri, Amy","Sasaki","F"
6,"Ryo","Saito""s","M"

文字列は "" で囲われ、文字列の中に " がある場合は "" にエスケープされています。

SELECT ... INTO OUTFILE では、ヘッダー（カラム名）は出力されないので、ヘッダーをつけたい場合は、UNION を使って手動でヘッダーをつけるか、シェルスクリプト等で、できあがったファイルにあとからヘッダー行を追加するなど、工夫が必要になります。

(
  SELECT 'student_id', 'first_name', 'last_name', 'gender'
  UNION ALL
  SELECT student_id, first_name, last_name, gender FROM students
)
INTO OUTFILE '/usr/local/mysql-exports/students_with_header.csv'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
ESCAPED BY '"'
LINES TERMINATED BY '\n';

[実行結果]