[Mysql] query로 export하는 방법 정리

Workbench나 DBeaver의 UI로 export하는 방법도 있지만,

쿼리문을 익히다보니 문득 쿼리로 직접 export해보고 싶어 찾아봤습니다.

방법은 총 4가지가 있더군요. 환경에 따라 골라쓰면 될 듯 싶습니다.

감사합니다. ^^

 

방법 1

SELECT * FROM 저장할 테이블
INTO OUTFILE '저장할 경로/저장할 이름.확장자'
CHARACTER SET utf8
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n';

 

방법 2

SELECT * FROM (
	(
    	SELECT
        	'필드1' AS 'filed_1', '필드2' AS 'filed_2'
     ) UNION (
     	SELECT filed_1,filed_2
        FROM sample_table
     )
 ) AS sample_query
 INTO OUTFILE '저장할 경로/저장할 이름.확장자'
 CHARTER SET utf8
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 ESCAPTED BY '\\'
 LINES TERMINATED BY '\n'

 

방법 3

$mysql -p my_db -e "SELECT * FROM sample_table" | sed 's/\t/","/g;s/^/"/;s/$/"/;' > 저장할 이름.저장할 확장자

 

방법 4

#!/bin/bash
 
db=YOUR_DB
user=YOUR_USER
pass=YOUR_PASS
 
for table in $(mysql -u$user -p$pass $db -Be "SHOW tables" | sed 1d); do
  echo "exporting $table.."
  mysql -u$user -p$pass $db -e "SELECT * FROM $table" | sed 's/\t/","/g;s/^/"/;s/$/"/;' > $table.csv
done