【MySQL】SELECT結果をファイル出力する

Notionのメモを整理している時に出てきたので供養。

INTO OUTFILE

以下で可能です 。

SELECT * INTO OUTFILE '/tmp/output.txt' FROM {TABLE};

mysql> SELECT user_id, email, birthday INTO OUTFILE '/tmp/output.txt' FROM users\G;
Query OK, 1 row affected (0.00 sec)

$ cat /tmp/output.txt
1   p1@test.com 2021-12-23

また次のようなコマンドでcsv形式にもできます。

# extract.sql
SELECT
  CONCAT('"', user_id, '"') AS user_id,
  CONCAT('"', email, '"') AS email,
  CONCAT('"', birthday, '"') AS birthday
FROM
  users;

# tsvからcsvに置き換える
$ mysql -u user -p {DB} -e "`cat extract.sql`" | sed -e 's/\t/,/g' > result.csv

$ cat result.csv
user_id,email,birthday
"1","p1@test.com","2021-12-23"

前提: secure_file_priv

MySQLの secure_file_priv システム変数はファイルを読み取りおよび書き込みできる場所を制限します。この値がnullなどになっているとそもそも出力ができないので、必要に応じて設定しておく必要があります。

mysql> select @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT * INTO OUTFILE '/tmp/output.txt' FROM users \G;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
ERROR:
No query specified

# my.cnf
[mysqld]
secure_file_priv=""


mysql> select @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)

余談

RDS on MySQLではできません...。

以下のような権限エラーが出ますが、RDSの使用上変更できないです。尚、Auroraであればs3に置いてあげるようにすることができるそうな。

mysql> SELECT * INTO OUTFILE '/tmp/output.txt' FROM table\G;
ERROR 1045 (28000): Access denied for user 'USER'@'%' (using password: YES)
ERROR:
No query specified