Edited at

MySQLで抽出結果をファイルに出力する方法

More than 3 years have passed since last update.

MySQLでのファイル出力方法について。

バージョン5.7.11でmysqlクライアントを使用して確認しています。


SELECT INTO OUTFILE を使用する

サーバ上のファイルに出力します。


構文

以下の構文で抽出結果をファイルに出力することができます。

SELECT フィールド名 FROM テーブル名 INTO OUTFILE 'ファイル名'


 SELECT * FROM test;

+------+---------+
| id | name |
+------+---------+
| 1 | isono |
| 2 | fu”guta |
+------+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test INTO OUTFILE '/var/lib/mysql-files/test.dmp';
Query OK, 2 rows affected (0.00 sec)

mysql> system sudo cat /var/lib/mysql-files/test.dmp
1 isono
2 fu"guta


secure_file_priv

サーバオプションのsecure_file_privが設定されている場合は、設定されたディレクトリ以外への出力はエラーになります。

mysql> SELECT @@secure_file_priv;

+-----------------------+
| @@secure_file_priv |
+-----------------------+
| /var/lib/mysql-files/ |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test INTO OUTFILE '/tmp/test.dmp';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement


FILESオプションとLINESオプション

各フィールドはタブで区切られています。

FIELDSオプションを使用すれば区切り文字を、LINESオプションを使用すれば改行コードを変更できます。

mysql> SELECT * FROM test INTO OUTFILE '/var/lib/mysql-files/test.dmp'

-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';
Query OK, 2 rows affected (0.00 sec)

mysql> system sudo cat /var/lib/mysql-files/test.dmp
1,"isono"
2,"fu\"guta"

"がエクケープされていること確認できます。


ヘッダをつける

ヘッダをつけるオプションはないようです。

以下の例ではUNIONでヘッダをつけています。

mysql> SELECT 'id', 'name' UNION SELECT * FROM test INTO OUTFILE '/var/lib/mysql-files/test2.dmp';

;
Query OK, 3 rows affected (0.00 sec)

mysql> system sudo cat /var/lib/mysql-files/test2.dmp
id name
1 isono
2 fu"guta


リダイレクトによる出力

リダイレクトを使用した出力も可能です。

shell> echo 'SELECT * FROM test' | mysql -u root -p test > /tmp/test.dmp

Enter password:
shell> cat /tmp/test.dmp
id name
1 isono
2 fu"guta