[MySQL][Backup n Recovery] - SELECT...INTO Outfile과 Load Data

SELECT... INTO Outfile과 Load Data는 쌍으로 이어지는 데이터 언로드와 데이터 로드 문법입니다. 둘다 mysql 커맨드 인터페이스 프로그램에서 수행됩니다. 문제가 자주 발생되는 테이블(사용자의 데이터 삭제나 엔진문제로 인한 크래쉬)에 대해 데이터베이스 레벨로 덤프를 받기는 부담스러울때 백업용도로 받거나 데이터 이관, 혹은 마이그레이션 같은 특정 용도로 사용할때 좋습니다.

 

■ mysqldump와 SELECT...INTO Outfile 문법의 차이점.

mysqldump와 같이 SELECT...INTO문법은 테이블 데이터를 덤프하지만 차이는 mysqldump는 Query 문법으로 Database를 덤프하지만 SELECT...INTO Outfile문법은 순수하게 데이터만 덤프합니다. 

다음의 예제는 어떠한 옵션도 붙이지 않고 순수하게 명령문만을 이용해서 출력한 예제입니다.

 

▶ mysqldump 프로그램을 이용한 덤프파일내용

SQL 문법으로 구성됩니다.

create database db_name;
create table tb_name(c1 char(10));
insert into tb_name values(1,2),(3,4);

 

▶ SELECT...INTO Outfile 문법을 이용한 덤프파일 내용

(아무 옵션도 없이 데이터 덤프시. 옵션이 없다라기보단 기본 옵션으로 출력된 데이터입니다. 위의 기본문법에 의해 출력된 데이터입니다.)

순수하게 데이터 내용으로만 구성되어 있습니다.

10001   Senior Engineer 1986-06-26      9999-01-01
10002   Staff           1996-08-03      9999-01-01
10003   Senior Engineer 1995-12-03      9999-01-01
10004   Engineer        1986-12-01      1995-12-01
10004   Senior Engineer 1995-12-01      9999-01-01
10005   Senior Staff    1996-09-12      9999-01-01

 

 

■ SELECT...INTO Outfile 사용방법

▶ 필드 옵션과 라인 옵션

SELECT...INTO Outfile 문이던 Load Data 문이던 둘다 다음과 같은 필드 옵션을 이용해서 데이터를 덤프하거나 로드합니다. 다음의 옵션을 적적히 사용하여 덤프및 로드를 같이 사용해서 데이터를 옮겨야 합니다.

 

SELECT * | Column1, column2 INTO OUTFILE 'directory/file_name'
[{FIELDS | COLUMNS}
     [TERMINATED BY 'string']
     [[OPTIONALLY] ENCLOSED BY 'char']
     [ESCAPED BY 'char']
]
[LINES
     [STARTING BY 'string']
     [TERMINATED BY 'string']

 

- 옵션설명

FIELDS 

TERMINATED BY : 필드 구분자

ENCLOSED BY : 필드를 구분지을 수 있는 시작과 끝 문자. '문자' -> 싱글쿼테이션(')을 의미

OPTIONALLY ENCLOSED BY : 필요할경우 필요한 구분자를 더 추가 

FIELDS ESCAPED BY : 필드를 빠져나올 수 있는 구분자. 예를 들어 데이터에 빈값이 들어있다면 이 필드를 빠져나올 수 있는 방법이 있어야 합니다. 그렇지 않으면 컬럼 갯수 대비 데이터가 맞지 않거나, 순서가 틀리거나 어긋날 수 있습니다.

LINES TERMINATED BY : 라인(행) 구분자.

 

▶ 기본문법

쿼리문 형태와 비슷합니다. select 문법을 이용해서 데이터를 덤프합니다. select 후 덤프할 원하는 컬럼을 입력합니다. 특수한 상황이 아닌이상 컬럼보다는 * 을 더 많이 이용합니다. 그리고 from이 아닌 outfile문을 이용하여 어느 디렉토리, 파일명으로 덤프할것인지 정합니다. 다음 덤프할 때 어떤 방법으로 덤프할지 옵션을 지정합니다. 옵션은 LINE 옵션과 필드 옵션이 있습니다. 위에서 설명했습니다. 이것을 이용하여 데이터를 어떻게 덤프할지 정합니다. 다음 덤프할 테이블명을 입력합니다. from 테이블 명으로 합니다. 마지막으로 옵션절입니다. 특정 데이터 범위만 덤프할 수 도 있고 연산을 통하여 덤프할 수도 있습니다.

 

SELECT col1, col2 INTO OUTFILE 'data.txt'
  FIELDS TERMINATED BY ','
  FROM table2
where col1 > 10
and col2=‘A’;

 

위와같이 쿼리문을 이용하여 원하는 방법으로 유연하게 데이터를 덤프할 수 있습니다.

 

■ Load Data 사용방법 및 각 옵션 설명 그리고 예제

Load Data는 고속 데이터 적재 방법입니다. mysqldump로 덤프된 파일보다 훨씬 빠릅니다. 또한 mysqlimport라는 프로그램을 제공하는데 이 프로그램이 내부적으로 load data 문법을 호출해서 수행합니다. 당연히 mysql클라이언트에 로그인하지 않고 로드하고 싶다면 mysqlimport프로그램을 사용하시면 됩니다.

 

LOAD DATA 및 SELECT ... INTO OUTFILE 문의 경우 FIELDS 및 LINES 절의 구문이 동일합니다. 두 절 모두 선택 사항이지만 둘 다 지정되면 FIELDS가 LINES보다 앞에 와야합니다.

 

FIELDS 절을 지정하면, 각각의 부속 절 (TERMINATED BY, [OPTIONALLY] ENCLOSED BY 및 ESCAPED BY)도 선택 사항이지만, 그 중 하나 이상을 지정해야합니다. 이 절의 인수는 ASCII 문자 만 포함 할 수 있습니다.

 

 

▶ 필드 및 라인 옵션

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...]

 

 

- Partition Table 지원

파티션 테이블인경우 파티션명을 지정해서 데이터를 로드할 수 있습니다. 파티션 구조를 알면 직접 지정해서 로드하므로 좀더 빨리 작업을 할 수 있습니다.

 

- 입력 파일 이름, 위치 및 내용 해석

서버는 character_set_database 시스템 변수로 표시된 문자 세트를 사용하여 파일의 정보를 해석합니다. SET NAMES 및 character_set_client 설정은 입력 해석에 영향을 미치지 않습니다. 입력 파일의 내용이 기본값과 다른 문자 세트를 사용하는 경우 일반적으로 CHARACTER SET 절을 사용하여 파일의 문자 세트를 지정하는 것이 좋습니다. 이진 문자 집합은 "변환 없음"을 지정합니다.

 

LOAD DATA는 필드 값이 로드되는 열의 데이터 유형에 관계없이 파일의 모든 필드가 동일한 문자 세트를 갖는 것으로 해석합니다. 파일 내용을 올바르게 해석하려면 파일이 올바른 문자 세트로 작성되었는지 확인해야합니다. 예를 들어, mysqldump -T를 사용하거나 mysql에서 SELECT ... INTO OUTFILE 문을 발행하여 데이터 파일을 작성하는 경우 --default-character-set 옵션을 사용하여 출력이 문자 세트에 기록되도록 합니다. 파일이 LOAD DATA와 함께로드 될 때 사용됩니다.

 

- LOW_PRIORITY | CONCURRENT | LOCAL

LOW_PRIORITY 옵션을 사용하면 다른 클라이언트가 테이블에서 읽을 때까지 LOAD DATA 문의 실행이 지연됩니다. 이는 테이블 레벨 잠금 만 사용하는 스토리지 엔진 (예 : MyISAM, MEMORY 및 MERGE)에만 영향을줍니다.

 

동시 인서트 조건 (중간에 사용 가능한 블록이 없음)을 충족시키는 MyISAM 테이블과 함께 CONCURRENT 옵션을 지정하면 LOAD DATA가 실행되는 동안 다른 스레드가 테이블에서 데이터를 검색 할 수 있습니다. 이 수정자는 다른 스레드가 테이블을 동시에 사용하지 않더라도 LOAD DATA의 성능에 약간 영향을줍니다.

 

LOCAL 옵션은 원격 클라이언트에서 로드를 할때 사용합니다. 서버 환경설정에서 사용을 막으면 원격 클라이언트에서 사용이 불가능합니다.

 

- 중복키 처리

REPLACE 및 IGNORE 수정자는 고유 키 값에서 기존 행을 복제하는 입력 행 처리를 제어합니다.

 

REPLACE를 지정하면 입력 행이 기존 행을 대체합니다. 즉, 기본 키 또는 고유 인덱스에 대해 기존 행과 동일한 값을 갖는 행입니다. 

 

IGNORE를 지정하면 고유 키 값에서 기존 행을 복제하는 행이 삭제됩니다.

 

수정자를 지정하지 않으면 LOCAL 수정자가 지정되었는지 여부에 따라 동작이 달라집니다. LOCAL이 없으면 중복 키 값이 발견되면 오류가 발생하고 나머지 텍스트 파일은 무시됩니다. LOCAL을 사용하면 기본 동작은 IGNORE가 지정된 경우와 동일합니다. 서버가 작업 중에 파일 전송을 중지 할 방법이 없기 때문입니다.

 

- Index Handling

로드 조작 중 외래 키 제한 조건을 무시하려면 LOAD DATA를 실행하기 전에 SET foreign_key_checks = 0 문을 실행하면 유용합니다.

 

▶ FIELDS 와 LINES 옵션 설명 그리고 예제들.

- FIELDS

TERMINATED BY : 필드 구분자

ENCLOSED BY : 필드를 구분지을 수 있는 시작과 끝 문자. '문자' -> 싱글쿼테이션(')을 의미

OPTIONALLY ENCLOSED BY : 필요할경우 필요한 구분자를 더 추가 

FIELDS ESCAPED BY : 필드를 빠져나올 수 있는 구분자. 예를 들어 데이터에 빈값이 들어있다면 이 필드를 빠져나올 수 있는 방법이 있어야 합니다. 그렇지 않으면 컬럼 갯수 대비 데이터가 맞지 않거나, 순서가 틀리거나 어긋날 수 있습니다. FIELDS ECAPED BY 필드가 비어있는 경우, FIELDS ENCLOSED BY 또는 LINES TERMINATED BY 필드 뒤에 FIELDS TERMINATED BY 값이 포함 된 필드 값으로 인해 LOAD DATA INFILE이 필드 또는 행 읽기를 너무 일찍 중지합니다. LOAD DATA INFILE이 필드 또는 행 값이 끝나는 위치를 제대로 결정할 수 없기 때문에 발생합니다.

기본적으로 Escaped by는 데이터에 필요하지 않은 데이터에 나타나는 문자를 기준으로 열을 이스케이프하는 데 사용됩니다.

 

- LINES

[STARTING BY 'string']
[TERMINATED BY 'string']

STARTING BY : 데이터 로드를 시작할 구분자. kim 이라면 kim으로 시작하는 문자를 건너뛰고 그 다음 필드부터 데이터 로드 시작합니다.

TERMINATED BY : 라인(행) 구분합니다.

 

- Load data 기본문법

LOAD DATA INFILE 문 입력 후 덤프파일이 있는 디렉토리 및 파일명을 입력 해 줍니다. 그리고 이 덤프를 어디 테이블에 적재할지 입력합니다(INTO TABLE table1). 데이터를 적재할 때 덤프파일이 구성되어 있는 방법대로 데이터 컬럼을 분해합니다. 그리고 데이터를 적재합니다.

LOAD DATA INFILE 'data.txt' INTO TABLE table1;

 

- Load data 기본 옵션

select outfile이나 load data시 아무런 옵션도 표현하지 않으면 다음과 같은 기본 옵션이 적용됩니다.

FIELDS TERMINATED BY '\t' ENCLOSED BY ''(쌍따옴표아님. 아무값도 없는걸 의미) ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

그래서 맨 위 처음 소개했던 데이터 유형을 보면 이 방법으로 출력되었다는것을 알수 있습니다.

 

백 슬래시는 SQL 문에서 문자열 내의 MySQL 이스케이프 문자입니다. 따라서 리터럴 백 슬래시를 지정하려면 값이 단일 백 슬래시로 해석되도록 두 개의 백 슬래시를 지정해야합니다. 이스케이프 시퀀스 '\ t'및 '\ n'은 각각 탭과 줄 바꿈 문자를 지정합니다.

 

- Load data의 Fields 처리 기본사항.

새로운 라인들에서 라인의 끝을 찾습니다.

어떠한 라인 접두어도 그냥 넘어가지 않습니다.

라인들에서 탭으로 시작하는 곳에서 필드로 나눕니다.

쌍따옴표 부호로 필드를 묶지 않습니다.

이스케이프 문자 \ 가 앞에 오는 문자를 이스케이프 시퀀스로 해석합니다. 예를 들어, \t, \n 및 \\는 각각 탭, 줄 바꿈 및 백 슬래시를 나타냅니다.

 

- Filed 옵션 예제

다음과 같은 데이터를 가지고 있는 파일이 /tmp/exam1.txt에 있다고 가정합니다.

Test1    1
Test2    2
Test3    3

 

이것을 로드하는 방법은 다음과 같습니다.

mysql > LOAD DATA INFILE '/tmp/exam1.txt' INTO TABLE exam1;

가장 기본적인 로드 방법으로 field옵션을 써서 사용하지는 않습니다.

테이블의 컬럼 갯수와 일치하면 자동으로 맞게 입력이 됩니다.

 

모든 입력 행에 무시하려는 공통 접두사가있는 경우 LINES STARTING BY 'prefix_string'을 사용하여 접 두부 및 그 이전의 항목을 건너 뛸 수 있습니다. 행에 접두사가 포함되어 있지 않으면 전체 행을 건너 뜁니다. 먼저 다음과 같은 데이터를 가지고 있는 파일이 /tmp/test.txt 위치에 저장있다고 가정합니다.

xxx"abc",1
something xxx"def",2
"ghi",3

 

"abc",1과 "def",2 만을 테이블에 로드하고 싶습니다.

이를 로드하는 방법은 다음과 같습니다.

mysql > LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
  FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';

필드 옵션을 써서 각 필드 종료자를 ‘,’로, 라인 시작을 ‘xxx’로 했기 때문입니다.

각 라인 시작에서 xxx로 시작하는 곳부터 데이터를 로드하기 시작하는데(starting by) xxx로 시작되는 라인은 위에서 2개의 라인밖에 없기 때문입니다.

 

위의 데이터에서 특정 라인을 무시하고 싶다면 다음과 같이 입력합니다.

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

 

LOAD DATA와 함께 SELECT ... INTO OUTFILE을 사용하여 데이터베이스에서 파일로 데이터를 쓴 다음 나중에 데이터베이스로 파일을 다시 읽을 때 두 명령문의 필드 및 라인 처리 옵션이 일치해야합니다. 그렇지 않으면 LOAD DATA는 파일 내용을 올바르게 해석하지 않습니다. SELECT ... INTO OUTFILE을 사용하여 쉼표로 구분 된 필드가있는 파일을 작성한다고 가정합니다.

SELECT * INTO OUTFILE 'data.txt'
  FIELDS TERMINATED BY ','
  FROM table2;

 

콤마 구분자로 된 파일을 읽을때 다음과 같이 입력합니다.

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

 

LOAD DATA는 외부 소스에서 얻은 파일을 읽는 데 사용할 수 있습니다. 예를 들어, 많은 프로그램은 CSV (쉼표로 구분 된 값) 형식으로 데이터를 내보낼 수 있습니다. 이렇게하면 행에 쉼표로 구분되고 큰 따옴표로 묶인 필드에 열 이름의 처음 행이 있습니다. 이러한 파일의 행이 캐리지 리턴 / 줄 바꾸기 쌍으로 종료되는 경우 여기에 표시된 명령문은 파일을로드하는 데 사용할 필드 및 행 처리 옵션을 보여줍니다.

 

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

 

입력 값이 반드시 인용 부호로 묶여 있지 않은 경우 ENCLOSED BY 옵션 앞에 OPTIONALLY를 사용합니다.

 

필드 또는 라인 처리 옵션은 빈 문자열 (' ')을 지정할 수 있습니다. 비어 있지 않으면 FIELDS [OPTIONALLY] ENCLOSED BY 및 FIELDS ECAPED BY 값은 단일 문자여야 합니다. FIELDS TERMINATED BY, LINES STARTING BY 및 LINES TERMINATED BY 값은 둘 이상의 문자 일 수 있습니다. 예를 들어, 캐리지 리턴/줄 바꿈이 쌍으로 종료되는 라인을 쓰거나 그러한 라인이 포함 된 파일을 읽으려면 LINES TERMINATED BY '\r\n'절을 지정하세요.

 

%%로 구성된 행으로 구분 된 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 ''
LINES TERMINATED BY '\n%%\n' (joke);

 

필드 [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"

 

OPTIONALLY를 지정하면 ENCLOSED BY 문자는 문자열 데이터 유형 (예 : CHAR, BINARY, TEXT 또는 ENUM)이있는 열의 값을 묶는 데만 사용됩니다.

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 문자가 접두사로 이스케이프됩니다. 또한 비어있는 ESCAPED BY 값을 지정하면 LOAD DATA에서 제대로 읽을 수없는 출력을 실수로 생성 할 수 있습니다. 예를 들어, 이스케이프 문자가 비어 있으면 방금 표시된 위의 출력이 다음과 같이 나타납니다. 네 번째 줄의 두 번째 필드에는 따옴표 뒤에 쉼표가 포함되어 있습니다.

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 문자 (있는 경우)는 필드 값 끝에서 제거됩니다. (이것은 OPTIONALLY가 지정되었는지 여부에 관계없이 적용됩니다. OPTIONALLY는 입력 해석에 영향을주지 않습니다.) 앞에 오는 ENBYD BY 문자의 발생은 현재 필드 값의 일부로 해석됩니다.

 

필드가 ENCLOSED BY 문자로 시작하면 해당 문자의 인스턴스는 필드 또는 행 TERMINATED BY 순서가 뒤에 오는 경우에만 필드 값을 종료하는 것으로 인식됩니다. 모호성을 피하기 위해 필드 값 내에서 ENCLOSED BY 문자의 발생을 두 배로 늘릴 수 있으며 문자의 단일 인스턴스로 해석됩니다. 예를 들어 ENCLOSED BY ' "'를 지정하면 다음과 같이 따옴표가 처리됩니다.

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

 

FIELDS ECAPED BY는 특수 문자를 읽거나 쓰는 방법을 제어합니다.

- 입력의 경우 FIELDS ESCAPED BY 문자가 비어 있지 않으면 해당 문자의 발생이 제거되고 다음 문자는 문자 그대로 필드 값의 일부로 사용됩니다. 첫 문자가 이스케이프 문자 인 예외 인 일부 두 문자 시퀀스. 이 순서는 다음 표에 나와 있습니다 (이스케이프 문자에 \ 사용). NULL 처리 규칙은이 섹션의 뒷부분에서 설명합니다.

Character Escape Sequence
\0 An ASCII NUL (X'00') character
\b A backspace character
\n A newline (linefeed) character
\r A carriage return character
\t A tab character.
\Z ASCII 26 (Control+Z)
\N NULL

FIELDS ESCAPED BY 문자가 비어 있으면 이스케이프 시퀀스 해석이 발생하지 않습니다.

 

출력의 경우, FIELDS ESCAPED BY 문자가 비어 있지 않은 경우 출력에서 ​​다음 문자를 접두어로 사용합니다.

 1. FIELDS ECAPED BY 문자.

 2. 필드 [OPTIONALLY] 문자로 묶여 있습니다.

 - ENCLOSED BY 문자가 비어 있거나 지정되지 않은 경우 FIELDS TERMINATED BY 및 LINES TERMINATED BY 값의 첫 문자입니다.

 3. ASCII 0 (이스케이프 문자 다음에 실제로 쓰여지는 것은 0 값 바이트가 아닌 ASCII 0입니다).

 

FIELDS ESCAPED BY 문자가 비어 있으면 문자가 이스케이프되지 않고 \ N이 아닌 NULL로 NULL이 출력됩니다. 특히 데이터의 필드 값에 방금 제공된 목록의 문자가 포함 된 경우 빈 이스케이프 문자를 지정하는 것은 좋지 않습니다.

 

경우에 따라 필드 및 라인 처리 옵션이 상호 작용합니다.

1. LINES TERMINATED BY가 빈 문자열이고 FIELDS TERMINATED BY가 비어 있지 않으면 행도 FIELDS TERMINATED BY로 종료됩니다.

2. FIELDS TERMINATED BY 및 FIELDS ENCLOSED BY 값이 모두 비어있는 경우 ( '') 고정 행 (구분되지 않은) 형식이 사용됩니다. 고정 행 형식을 사용하면 필드 사이에 구분 기호가 사용되지 않습니다 (단, 줄 종결자를 사용할 수는 있음). 대신, 필드의 모든 값을 보유 할 수있을만큼 넓은 필드 너비를 사용하여 열 값을 읽고 씁니다. TINYINT, SMALLINT, MEDIUMINT, INT 및 BIGINT의 경우 선언 된 디스플레이 너비에 관계없이 필드 너비는 각각 4, 6, 8, 11 및 20입니다.

3. LINES TERMINATED BY는 여전히 줄을 구분하는 데 사용됩니다. 행에 모든 필드가 포함되지 않은 경우 나머지 열은 기본값으로 설정됩니다. 줄 종결자가없는 경우 이것을 '' 로 설정해야합니다. 이 경우 텍스트 파일에는 각 행의 모든 ​​필드가 포함되어야합니다.

4. 고정 행 형식은 나중에 설명하는 것처럼 NULL 값 처리에도 영향을줍니다.

 

NULL 값 처리는 사용중인 FIELDS 및 LINES 옵션에 따라 다릅니다.

1. 기본 FIELDS 및 LINES 값의 경우 출력의 경우 필드 값 \ N으로 NULL이 작성되고 입력의 경우 \ N 필드 값이 NULL로 읽힙니다 (ESCAPED BY 문자가 \ 인 경우).

2. FIELDS ENCLOSED BY가 비어 있지 않으면 리터럴 단어 NULL을 값으로 포함하는 필드를 NULL 값으로 읽습니다. 이는 FIELDS ENCLOSED BY 문자로 묶인 NULL 단어와 다르며 문자열 'NULL'로 읽습니다.

3. FIELDS ESCAPED BY가 비어 있으면 NULL은 단어 NULL로 작성됩니다.

4. 고정 행 형식 (FIELDS TERMINATED BY 및 FIELDS ENCLOSED BY가 모두 비어있을 때 사용됨)을 사용하면 NULL이 빈 문자열로 작성됩니다. 이로 인해 테이블에 NULL 값과 빈 문자열이 모두 빈 문자열로 기록되므로 파일에 쓸 때 구분할 수 없게됩니다. 파일을 다시 읽을 때 둘을 구분할 수 있어야하는 경우 고정 행 형식을 사용하지 않아야합니다.

 

NOT NULL 열에 NULL을로드하려고하면 열의 데이터 유형에 대한 내재 된 기본값과 경고 또는 엄격한 SQL 모드의 오류가 지정됩니다.

 

일부 경우는 LOAD DATA에서 지원되지 않습니다.

1. 고정 크기 행 (FIELDS TERMINATED BY 및 FIELDS ENCLOSED BY 비어 있음) 및 BLOB 또는 TEXT 열

2. 접두사와 같거나 다른 접두사를 지정하면 LOAD DATA가 입력을 올바르게 해석 할 수 없습니다. 예를 들어 다음 FIELDS 절은 문제를 일으킬 수 있습니다.

   [FIELDS TERMINATED BY '"' ENCLOSED BY '"']

3. FIELDS ESCAPED BY 필드가 비어 있으면 FIELDS ENCLOSED BY 또는 LINES TERMINATED BY 필드와 FIELDS TERMINATED BY 값이 포함 된 필드 값으로 인해 LOAD DATA가 필드 또는 행 읽기를 너무 일찍 중지합니다. 이는 LOAD DATA가 필드 또는 행 값이 끝나는 위치를 올바르게 결정할 수 없기 때문에 발생합니다.

 

▶ 컬럼 지정 로드

특정 컬럼을 지정해서 로드하고 싶다면 다음과 같이 합니다.

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata
(col_name 혹은 사용자변수, col_name 혹은 사용자변수);

 

▶ 전처리 후 데이터 로드

- 특정 컬럼에 값을 처리 후 로드

LOAD DATA INFILE 'file.txt'
INTO TABLE t1(column1, @var1)
SET column2 = @var1/100;

LOAD DATA INFILE 'file.txt'
INTO TABLE t1(column1, column2)
SET column3 = CURRENT_TIMESTAMP;

 

- 사용자 변수 컬럼처리 후 로드

LOAD DATA INFILE 'file.txt'
INTO TABLE t1(column1, @dummy, column2, @dummy, column3);