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

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