MySQL 조회결과를 csv 파일에 저장하기

MySQL 조회결과를 csv 파일에 저장하기

1 쿼리문

SELECT * INTO OUTFILE '파일명'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM 테이블명;

2 실습

root@zetadb:~# mysql -uroot -pP@ssw0rd test
... (생략)
mysql> SELECT * FROM employee;
+----+-----------+---------------+------------+
| id | name      | department_id | birth_date |
+----+-----------+---------------+------------+
|  1 | 한놈      |             1 |       1999 |
|  2 | 두시기    |             2 |       2000 |
|  3 | 석삼      |             2 |       1999 |
|  4 | 너구리    |             2 |       2000 |
+----+-----------+---------------+------------+

mysql> SELECT * INTO OUTFILE '/tmp/employee.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM employee;
Query OK, 4 rows affected (0.00 sec)

mysql> exit
Bye

root@zetadb:~# cat /tmp/employee.csv
1,"한놈",1,1999
2,"두시기",2,2000
3,"석삼",2,1999
4,"너구리",2,2000

3 같이 보기

• csv 파일

4 참고

• http://dev.mysql.com/doc/refman/5.7/en/select-into.html