mysql - select data 파일로

select 결과를 파일로..

가끔 DB 의 내용을 txt 파일이나 csv 로 변환하여 엑셀등의 프로그램으로 읽어야 하는 경우가 있다. 이 때마다 (워낙에 자주 있는 일이 아니라 그렇겠지만) 매번 까먹어서 다시 책이나 인터넷을 뒤적거리곤 하는데, 그래서 한번 정리해봤다. 나 같은 사람들이 분명 또 있을터, 그런 사람들에게도 도움이 되었음 한다.

SELECT col1, col1, col3 INTO OUTFILE "~/outfile.csv" FIELDS TERMINATED BY ',' FROM mem_main;

INTO OUTFILE "file name" : select 의 결과를 저장할 파일을 지정함
파일의 경로를 명시하지 않을 경우 default 로 mysql 설치 디렉토리에 저장된다.
FIELDS TERMINATED BY '구분자': 각 columns 을 구분할 구분자를 지정함 (*.csv 로 저장하여 엑셀에서 파일을 읽기를 원한다면 공백 또는 , 로 지정해야 함)

============================================================================================

LOAD DATA INFILE 문은 텍스트 파일을 매우 빠르게 읽어 테이블의 row값에 매김한다. 또한
mysqlimport를 사용하여 LOAD DATA INFILE이 수행되게 할 수도 있다.
SELECT ... INTO OUTFILE 'file_name' FROM tbl_name 문으로 백업한 자료를
restore 하는데 쓰인다.

【형식】
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
      [REPLACE | IGNORE]
      INTO TABLE tbl_name
      [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\']
      ]
      [LINES
         [STARTING BY '']
         [TERMINATED BY '\n']
      ]
      [IGNORE number LINES]
      [(col_name,...)]

LOW_PRIORITY는 테이블에서 데이터를 읽고 있는 클라이언트가 없을 때 LOAD DATA 문을 실행한다.
LOCAL은 클라이언트인 자기자신으로부터 파일을 읽어들이는데 반하여
LOCAL이 지정되지 않으면 서버로부터 파일을 읽어들인다.
LOCAL은 클라이언트에서 서버로 파일을 전송하여야 하기 때문에 억세스하는 것이 약간 느리지만,
서버의 file privilege가 없어도 local 파일을 upload할 수 있다.
MyISAM 형식의 테이블에서 CONCURRENT를 지정하면, LOAD DATA가 실행 중에도 다
른 스레드가 테이블에서 데이터를 읽을 수 있다.
또한, mysqlimport 유틸리티를 사용하여 데이터 파일을 업로드할 수 도 있는데, --local 옵
션은 클라이언트로부터 데이터 파일을 읽어오게 한다.

서버에 데이터가 있을 때 서버는 다음 규칙을 따른다:
? 절대경로를 지정하면 그 경로를 따른다.
? 상대경로를 지정하면 그 경로를 따른다.
? 경로 없이 파일이름을 지정하면 현재의 디렉토리에서 데이터를 찾는다.

그러므로 서버의 데이터 디렉토리로부터 읽을 파일이 './myfile.txt'라고 하면, 이는 현재 데
이터베이스의 데이터베이스 디렉토리로부터 읽을 'myfile.txt'를 의미한다. 다음은 현재의 데
이터베이스 db1으로부터 'data.txt' 파일을 읽어서 db2 데이터베이스에 로드하는 예이다.
mysql> USE db1;
mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;

REPLACE와 IGNORE는 unique key로 지정한 레코드를 복제할지 여부를 지정하는 것으로
REPLACE는 unique key로 지정한 기존의 row는 새로운 값으로 대치하라는 의미이고,
IGNORE는 unique key로 지정한 기존의 row는 그냥 건너뛰라는 의미이다.
데이터를 로드할 때 foreign key 제약을 원한다면, LOAD DATA 문을 실행하기 전에 SET
FOREIGN_KEY_CHECK=0으로 설정할 수 있다.
LOCAL 키워드로 데이터를 로드하고 있는 도중에 데이터 전송을 중지할 수 없다.
LOAD DATA INFILE은 SELECT ... INTO OUTFILE과 반대동작을 한다. 그러므로 데이터베이스에
서 파일로 데이터를 출력하고자 할 때 SELECT ... INTO OUTFILE을 사용하며, 반대로 파일에
서 데이터베이스로 읽어들이고자 할 때는 LOAD DATA INFILE을 사용한다. FIELDS와 LINES
절은 두 명령에서 쓰임새가 같으며, 두 절을 동시에 사용할 경우에는 FIELDS가 LINES보
다 앞에 있어야 한다.
FIELDS 절을 지정하는 경우, 그 서브 절(TERMINATED BY, [OPTIONALLY]
ENCLOSED BY, ESCAPED BY)도 옵션이지만, 적어도 하나의 서브 절은 반드시 사용해야
한다.
FIELDS 절을 사용하지 않는 경우, 그 디폴트 값은 다음과 같다.

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY'\\'

LINES절을 지정하지 않는 경우의 그 디폴트 값은 다음과 같다.

LINES TERMINATED BY '\n'

위 문장은 윈도우에서 텍스트 파일을 작성한 경우 종종 LINES TERMINATED BY '\r\n'처럼 변
경해야 할 경우도 있는데, 이는 윈도우의 line terminator을 두 문자로 사용하는 경우가 있
기 때문이다.

LOAD DATE INFILE은 파일을 읽을 때 다음과 같다고 가정한다.
? newline에서 line 경계선을 찾는다.
? LINES STARTING BY prefix가 사용된 경우, prefix를 찾아 prefix 다음 문자부터 읽기 시작한다.
? tab은 line을 분리한다.(즉, 필드가 분리됨)
? 인용문 내에서 필드 분리가 없다.
? tab, newline, '\' 문자가 그 문자 앞에 '\'를 가지면 문자로 본다.

SELECT ... INTO OUTFILE은 파일을 저장할 때 다음과 같다고 가정한다.
? 필드 사이는 tab이 들어간다.(tab으로 필드 분리됨)
? 인용문 내에서 필드 분리가 없다.
? tab, newline, '\' 문자가 그 문자 앞에 '\'를 가지면 문자로 본다.
? line 끝에는 newline이 기록된다.

IGNORE number LINES 옵션은 파일의 시작부분에 있는 컬럼 이름 헤더를 무시하는데 사
용되며, 다음은 그 예시이다.

mysql> LOAD DATA INFILE "/tmp/file_name" INTO TABLE test IGNORE 1 LINES;

SELECT ... INTO OUTFILE과 LOAD DATA INFILE은 서로 유기적이므로 두 명령으로 처리하는
필드와 line은 서로 매치되어야 한다.

예를 들어, SELECT ... INTO OUTFILE문에서 컴마로 필드를 구분한 경우,
mysql> SELECT * INTO OUTFILE 'data.txt'
-> FIELDS TERMINATED BY ','
-> FROM ...;

위와 같이 컴마로 필드를 구분하여 저장된 파일에서 읽어 들이는 경우는 다음과 같다.

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
-> FIELDS TERMINATED BY ',';

위와 같이 하지 않으면 LOAD DATA INFILE은 동작하지 않게 되어 아래 문장처럼 필드 사이의
tab을 찾게 된다.

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
-> FIELDS TERMINATED BY '\t';

LOAD DATA INFILE 문은 다른 외부 소스로 만들어진 파일로부터도 데이터를 읽어 들일 수 있다.
예를 들어, dBASE 형식의 데이터는 컴마로 필드를 분리하고, 이중 인용부호가 사용된다.
그러므로 각 dBASE의 데이터의 각 line이 newline으로 이루어져 있다면 다음과 같이 하면된다.

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
-> FIELDS TERMINATED BY ', ENCLOSED BY ‘"’
-> LINES TERMINATED BY '\n';

필드와 line를 다루는 옵션중에는 empty string('')일 수도 있으며, 만약 empty가 아니라면,
FIELDS [OPTIONALLY] ENCLOSED BY나 FIELDS ESCAPED BY의 값은 한 개의 문자가 아닌 복수
문자가 된다.
예를 들어, carriage return-linefeed로 끝나는 파일에서 읽어 들이는 경우에는
LINES TERMINATED BY '\r\n'이 된다.
다음은 line이 '%%'로 분리된 joke 파일에서 읽는 예이다.

    CREATE TABLE jokes (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
           joke TEXT NOT NULL);
    LOAD DATA INFILE "/tmp/jokes.txt" INTO TABLE jokes
           FIELDS TERMINATED BY ""
           LINE TERMINATED BY "\n%%\n" (joke);

FIELDS [OPTIONALLY] ENCLOSED BY는 필드의 인용부호를 지정하며, SELECT ... INTO OUTFILE
의 출력 파일에서 OPTIONALLY가 없는 경우, 모든 필드는 ENCLOSED BY 문자로 쌓여 있게 된다.

다음은 컴마를 필드 구분자를 사용하여 출력한 예이다.

"1","a string","100.20"
"2","a string containing a , comma","102.20"
"3","a string containing a \" quote","102.20"
"4","a string containing a \", quote and comma","102.20"

ENCLOSED BY 문자에 의해서 쌓이게 되는 필드 중에서 ESCAPED BY 문자를 미리 지정
하여 벗어 날 수 있다. 이 경우 OPTIONALLY는 아무런 영향을 미치지 않게 되며,
다음 예처럼 처리된다.

   "The ""BIG"" boss"   -> The "BIG" boss
    The "BIG" boss      -> The "BIG" boss
    The ""BIG"" boss    -> The ""BIG"" boss

FIELDS ESCAPED BY는 특수 문자를 읽거나 기록하는 것을 지정한다.
FIELDS ESCAPED BY 문자가 비어 있지 않다면,
다음과 같은 경우에 출력 다음 문자를 미리 지정하는데 사용한다.
? FIELDS ESCAPED BY 문자
? FIELDS [OPTIONALLY] ENCLOSED BY 문자
? FIELDS TERMINATED BY나 LINES TERMINATED BY의 값 중 그 첫 문자
? ASCII 0

FIELDS ESCAPED BY 문자가 비어 있다면,
벗어날 문자는 없게 되지만, 바람직한 생각이 아니다.

입력될 때, FIELDS ESCAPED BY 문자가 비어 있지 않다면, \0나 \N을 제외하고 뒤따르
는 문자는 필드 값의 일부로 처리한다. 여기서 \0는 ASCII 0, \N는 NULL을 의미한다.
때로는 필드와 LINE를 다루는 옵션이 다음과 같이 상호 작용한다:
? LINES TERMINATED BY가 빈 문자이고 FIELDS TERMINATED BY가 비어 있지
   않으면, line도 FIELDS TERMINATED BY로 종료 된다.
? FIELDS TERMINATED BY와 FIELDS   ENCLOSED BY가 둘 다   비어('')있으면,
   fixed-row 포맷이 사용된다. fixed-row 포맷에서는 필드 구분자가 없기 때문에 읽히거나
   기록할 때, 컬럼의 크기는 "display" 폭을 사용한다. 예를 들어, INT(7)로 선언된 컬럼은
   7-문자로 된 필드값이 그 컬럼 값이 된다. 읽을 때도 7-문자씩 끊어 컬럼 값에 매김한
   다.
   LINE TERMINATED BY는 line 분리자로 사용된다. line이 필요한 모든 필드를 가지고 있지
   않다면, 나머지 필드는 디폴트 값으로 매김한다. line 분리자가 없다면, ''으로 분리자를
   지정하며, 이 경우 텍스트 파일은 각 row에 모든 필드를 가지고 있어야 한다.

NULL은 FIELDS와 LINES 옵션에 따라 처리하는 값이 변한다.

? 디폴트 FIELDS와 LINES에 따라, 출력에는 \N으로 쓰여지고 입력에서는 NULL로 읽혀
진다.
? FIELDS ENCLOSED BY가 비어 있지 않다면, 필드는 문자 NULL을 가지고 있다.
? FIELDS ESCAPED BY가 비어 있다면, NULL은 단어 NULL로 기록된다.
? FIELDS TERMINATED BY와 FIELDS ENCLOSED BY가 둘 다 비어있는 경우에 발
생하는 fixed-row 포맷인 경우, NULL은 빈 문자처럼 기록된다.

다음의 경우 LOAD DATA INFILE에서 지원되지 않는다.
? Fixed-size row와 BLOB 혹은 TEXT 컬럼
? 분리자가 다른데서 prefix와 같은 때, LOAD DATA INFILE은 불확실하게 작동함. 예를
들어, 다음은 FIELDS 절에 문제가 있다.

FIELDS TERMINATED BY ‘"’ ENCLOSED BY ‘"’

? FIELDS ESCAPED BY가 비어 있는 경우, LOAD DATA INFILE에서 필드와 line 끝을
인식하지 못해서 읽기를 정지한다.

다음 예는 persondata로부터 모든 컬럼을 로드하는 예이다.
mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

이 예에서는 필드를 지정하지 않았기 때문에 LOAD DATA INFILE은 FIELDS와 LINES로
디폴트 값을 사용한다.
다음 예는 필드를 지정한 예이다.

mysql> LOAD DATA INFILE 'persondata.txt'
-> INTO TABLE persondata (col1,col2, ...);

반드시 읽어 들일 파일의 필드 순서에 따라 위 예제에서 컬럼의 순서를 지정해 주어야 한다.
row가 소수의 필드로 구성되어 있을 경우, 읽어들일 필드를 지정하지 않으면, 컬럼은 디폴
트로 설정한다.
empty 필드는 missing 필드보다 다르게 다음처럼 해석된다:

? string 타입에서, 컬럼은 empty string으로 설정한다.
? numeric 타입에서, 컬럼은 0으로 설정한다.
? date와 time 타입에서, 컬럼은 'zero'값으로 설정한다.

TIMESTAMP 컬럼은 현재 날짜와 시각으로 설정된다.
입력될 row가 필요 이상으로 많은 필드를 가질 경우, 나머지 필드는 무시되고 warnings 수
로 표시되므로 SHOW WARNINGS로 자세히 알 수 있다. 이 warnings는 INSERT 문에서 받는
경고와 같지만, load data infile에서 입력 row가 필요 이하/이상의 필드를 가진 경우에 나타
나는 경고만 다르다. warnings는 어느 곳에도 저장되지 않는다.

============================================================================================

select...into outfile 문에 의한 데이터 백업과
load data infile 문에 의한 데이터 복구

http://radiocom.kunsan.ac.kr
--------------------------------------------------------------------------------

mysql> select * from river;
+----+------------+----------+
| id | river_name | province |
+----+------------+----------+
| 1 | 금강       | 전라북도 |
| 2 | 대동강     | 평안도   |
| 3 | 영산강     | 전라남도 |
| 4 | 한강       | 강원도   |
+----+------------+----------+
4 rows in set (0.00 sec)

mysql> select id,river_name,province
    -> into outfile "result.txt"
    -> fields terminated by ',' optionally enclosed by '"'
    -> lines terminated by '\n'
    -> from river;
Query OK, 4 rows affected (0.00 sec)

mysql> select id,river_name,province
    -> into outfile "result_tab.txt"
    -> lines terminated by '\n'
    -> from river;
Query OK, 4 rows affected (0.00 sec)

mysql> select id,river_name,province
-> into outfile "result_none.txt"
-> from river;
Query OK, 4 rows affected (0.00 sec)

mysql> exit

% cd /export/home/mysql/var/jijoeDB

% ls -l *.txt
-rw-rw-rw-   1 mysql    mysql         80 Oct 10 09:04 result.txt
-rw-rw-rw-   1 mysql    mysql         64 Oct 10 09:08 result_none.txt
-rw-rw-rw-   1 mysql    mysql         64 Oct 10 09:07 result_tab.txt

% cat result.txt
1,"금강","전라북도"
2,"대동강","평안도"
3,"영산강","전라남도"
4,"한강","강원도"

% cat result_tab.txt
1       금강    전라북도
2       대동강 평안도
3       영산강 전라남도
4       한강    강원도

% cat result_none.txt
1       금강    전라북도
2       대동강 평안도
3       영산강 전라남도
4       한강    강원도
%

【예제】LOAD DATA INFILE 예제1
mysql> truncate table river;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from river;
Empty set (0.00 sec)

mysql> load data infile 'result.txt'
-> into table river
-> fields terminated by ',' optionally enclosed by '"';
Query OK, 4 rows affected (0.04 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql>

【예제】LOAD DATA INFILE 예제2
mysql> truncate table river;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from river;
Empty set (0.00 sec)

mysql> load data infile 'result_tab.txt'
-> into table river;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql>

【예제】LOAD DATA INFILE 예제3
mysql> truncate table river;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from river;
Empty set (0.00 sec)

mysql> load data infile 'result_none.txt'
-> into table river;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql>

mysql> select * into outfile "employees_dump" from employees;
Query OK, 2 rows affected (0.05 sec)

mysql> truncate table employees;
Query OK, 0 rows affected (0.00 sec)

mysql> load data infile "employees_dump" into table employees;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

mysql>

저작자표시

'프로그래밍 > mysql' 카테고리의 다른 글

mysql - sqlrelay 설정 (0)	2012.07.19
mysql - split 함수 구현 (0)	2012.07.19
mysql - rownum 구현 (0)	2012.07.19
mysql - query cache 테스트 자료 (0)	2012.07.19
Mysql Query Cache 매뉴얼 번역문 (0)	2012.07.19

맘스토리

mysql - select data 파일로

'프로그래밍 > mysql' 카테고리의 다른 글

티스토리툴바

mysql - select data 파일로

'프로그래밍 > mysql' 카테고리의 다른 글

'프로그래밍/mysql' Related Articles

티스토리툴바