MySQL은 파일로부터 데이터베이스로 import 하거나 데이터베이스에서 파일로 export 하는 SQL 문장을 지원한다.
1. LOAD DATE INFILE
-데이터 파일로 부터 레코드를 읽어 테이블에 삽입한다.
-하나의 테이블에 하나의 파일을 Import 한다.
-다음은 가장 간단한 유형의 SQL 문이다.
LOAD DATA INFILE 'file_name' INTO TABLE table_name;
0. 파일명은 작은 따옴표로 묶어야 한다.
0. 파일경로는 \대신 \\를 사용해야 한다. 다음은 예이다.
LOAD DATA INFILE 'c:/mydata/data.txt' INTO TABLES t;
LOAD DATA INFILE 'c:\\mydata\\data.txt' INTO TABLES t;
//이것은 C:\mydata\data.txt를 가리킨다.
-기본적으로 다른 언급이 없으면 파일은 서버에 있고, 컬럼은 tab으로 분리되고, \n newline 문자로 끝나는 파일형식을 따르며 각 라인은 테이블의 컬럼 값들로 구성되어있다고 간주한다. 다음과 같은 것들을 기술하여 데이터 로드 작업을 할 수 있다.
0. 로드할 테이블
0. 데이터 파일의 이름과 위치
0. 데이터 파일의 처음 몇 줄을 무시할지
0. 로드할 컬럼
0. 로드하기 전에 데이터 값을 스킵하거나 변경할지
0. 중복 레코드에 대해서 어떻게 처리할 지
0. 데이터 파일 형식
-다음은 LOAD DATA INFILE의 문법이다. 옵션은 []로 표시했다.
LOAD DATA [LOCAL] INFILE 'file_name'
[IGNORE | REPLACE]
INTO TABLE table_name
format_specifiers
[IGNORE n LINES]
[(column_list)]
[SET (assignment_list)]
0. 기본 적으로 MySQL은 파일이 서버에 있다고 본다.
0. LOAD DATA LOCAL INFILE 로 문장이 시작된다면 파일은 SQL문장이 수행된 클라이언트로 부터 읽혀진다. 이런 경우 클라이언트 프로그램이 데이터 파일을 읽어 들여 네트워크를 통해 서버로 보낸다.
-경로는 세가지 방법으로 기술된다.
0.전체 경로를 기술 할 수 있다.
0.디폴트 베이스라면 파일명만 적으면 된다.
0.상대경로를 사용해서 서버의 데이터 디렉토리나 그 아래의 어떤 파일도 참조할 수 있다.
-Skipping Data FILE lines
0. 데이터 파일의 처음 부분을 무시하기 위해서 IGNORE n LINES를 사용한다. 여기서 n은 스킵하는 줄 수 이다. 만약 첫 줄을 스킵하고자 하면 다음과 같이 쓰면된다.
LOAD DATA INFILE '/tmp/data.txt' INTO TABLE t IGNORE 1 LINES;
-데이터 파일의 모든 컬럼 값이 없거나 할때 컬럼을 지정해줄 수가 있다.
LOAD DATA INFILE '/tmp/people.txt' INTO TABLE teble_name(name, address);
-데이터파일의 컬럼을 무시할때는 @skip을 사용하면 된다.
-중복 발생시 처리방법
0. 중복 유니크 발생시 처리
-기본적으로 에러가 나고 로딩 작업이 멈추면서 그 전까지 처리된 레코드들만 테이블에 로드된다.
-IGNORE 키워드가 있을경우 중복된 새로운 레코드는 무시되고 에러 발생은 하지 않는다.
-REPLACE 키워드를 준 경우 기존 레코드 삭제후 삽입된다.
-IGNORE 와 REPLACE는 두가지를 동시에 사용할 수 없다.
-메시지 분석하기
0. 아래와 같은 형식으로 클라이언트에게 내용을 보여준다.
Records : 174 Deleted : 0 Skipped : 3 Warnings : 14
-Records는 테이블에 들어간 레코드 수가 아니라 데이터 파일로부터 읽은 레코드 수이다.
-Deleted는 유니크킥 중복 때문에 새로 들어간 레코드가 기존의 레코드를 대체한 레코드 수를 말한다.
-Skipped는 유니크 키 중복으로 인해 무시된 입력 레코드 수를 말한다.
-Warnings는 입력파일에서 발견된 문제의 수이다. SHOW WARNINGS;로 살펴 볼 수 있다.
** LOAD DATA INFILE은 INSERT문을 이용하는 것보다 효과적이다.
2. SELECT ... INTO OUTFILE
country 테이블의 내용을 country.txt.라는 파일에 쓰기 위한 문장은 다음과 같다.
SELECT * INTO OUTFILE 'country.txt' FROM country;
0. INTO OUTFILE은 SELECT 문의 동작을 여러 가지 방식으로 변경시킨다.
-만들어진 결과는 서버에만 존재한다. 결과를 네트워크를 통해 클라이언트로 보내는 것 대신 서버에 파일로 남겨지며 고의적이건 사고든간에 파일이 덮어씌워지는 걸 막기 위해 현재 존재하지 않은 파일을 기록하게 되어 있다.
-서버에 새로운 파일은 만들기 때문에 FILE 권한이 필요하다.
-결과 파일은 row당 한 줄로 표현되고 디폴트로 컬럼 값은 tab 문자로 분리되며 각 줄은 newlines로 종결된다. 파일명 뒤에 format specifier를 추가하여 결과 형식을 조절할 수 있다.
0. LOAD DATA INFILE처럼 SELECT ... INTO OUTFILE도 여러가지 형식을 정해 줄 수 있다.
-LOAD DATA INFILE과 INTO OUTFILE은 기본적으로 같으나 전자는 형식지정자가 테이블명 뒤에 들어갔지만 후자는 결과 파일 뒤에 적는다. 형식 지정자를 위한 문법은 두 문장 모두 같고 아래와 같다.
FIELDS
TERMINATED BY 'string'
ENCLOSED BY 'char'
ESCAPED BY 'char'
LINES TERMINATED BY 'string'
-FIELDS 절은 각 라인안에서의 데이터의 형식을 정의한다.
-LINES 절은 한 레코드가 어디까지인지 나타낸다.
-FIELDS 절의 TERMINATED BY, ENCLOSED BY, ESCAPED BY는 순서는 바뀌어도 상관이 없고 세 부분 모두 기술하지 않아도 된다.
: 데이터 값들은 tab문자로 구분된다고 가정된다. 다른 값을 나타내기 위해서는 TERMINATED BY 옵션을 사용하면 된다.
:데이터 값들은 인용부호로 묶여있지 않다고 간주한다. 인용부호를 사용하려면 ENCLOSED BY 옵션을 사용하면 된다. LOAD DATA INFILE에서 입력값에 있는 인용부호는 제거된다. SLELECT ... INTO OUTFILE에서 출력값들은 인용부호로 감싸져 있다.
:ENCLOSED BY의 다른 표현은 OPTIONALLY ENCLOSED BY이다. 이 것은 LOAD DATA INFILE에서 ENCLOSED 와 같지만 SELECT ... INTO OUTFILE에서는 다르다. OPTIONALLY절은 결과 값들 중에서 문자열 컬럼에 대해서만 인용부호로 묶는다.
:디폴트 에스케이프 문자는 '\'이다. 데이터 값내에 이 문자가 나타나면 그 문자는 다음과 같이 해석된다. 다른 문자를 에스케이프 문자로 사용하고 싶으면 ESCAPED BY 옵션을 넣으면 된다. MySQL은 아래의 특별한 에스케이프 sequence를 이해한다.
| Sequence | Meaning |
| \n | NULL VALUE |
| \o | NUL(zero) byte |
| \b | Backspace |
| \n | Newline (line feed) |
| \r | Carriage return |
| \s | space |
| \t | tab |
| \' | Single quote |
| \" | Double quote |
| \\ | Backslash |
'Database > MySql' 카테고리의 다른 글
| [MySql] Obtaining Database Metadata (0) | 2008/10/06 |
|---|---|
| [MySql] 사용자 변수 (0) | 2008/10/06 |
| [MySql] Importing and Exporting Data (0) | 2008/10/06 |
| [MySql] VIEWS (0) | 2008/10/06 |
| [MySql] Subqueries (0) | 2008/10/06 |
| [MySql] Joins (Outer join, Inner join) (0) | 2008/10/06 |
