15.2.9 LOAD DATA Statement

15.2.9 LOAD DATA Statement

1LOAD DATA
2    [LOW_PRIORITY | CONCURRENT] [LOCAL]
3    INFILE 'file_name'
4    [REPLACE | IGNORE]
5    INTO TABLE tbl_name
6    [PARTITION (partition_name [, partition_name] ...)]
7    [CHARACTER SET charset_name]
8    [{FIELDS | COLUMNS}
9        [TERMINATED BY 'string']
10        [[OPTIONALLY] ENCLOSED BY 'char']
11        [ESCAPED BY 'char']
12    ]
13    [LINES
14        [STARTING BY 'string']
15        [TERMINATED BY 'string']
16    ]
17    [IGNORE number {LINES | ROWS}]
18    [(col_name_or_user_var
19        [, col_name_or_user_var] ...)]
20    [SET col_name={expr | DEFAULT}
21        [, col_name={expr | DEFAULT}] ...]

LOAD DATA statement는 텍스트 파일에서 테이블로 행을 매우 높은 속도로 읽어 들입니다. LOCAL 한정자가 주어졌는지에 따라 파일은 서버 호스트나 클라이언트 호스트에서 읽을 수 있습니다. LOCAL은 데이터 해석과 에러 처리에도 영향을 줍니다.

LOAD DATA는 SELECT ... INTO OUTFILE의 보완 기능입니다. ( Section 15.2.13.1, “SELECT ... INTO Statement”를 참조하십시오.) 테이블에서 파일로 데이터를 쓰려면 SELECT ... INTO OUTFILE를 사용하십시오. 파일을 다시 테이블로 읽어 들이려면 LOAD DATA를 사용하십시오. FIELDS와 LINES 절의 구문은 두 statement에 대해 동일합니다.

mysqlimport 유틸리티는 데이터 파일을 로드하는 또 다른 방법을 제공합니다. 이 유틸리티는 서버로 LOAD DATA statement를 보내는 방식으로 동작합니다. Section 6.5.5, “mysqlimport — A Data Import Program”를 참조하십시오.

INSERT와 LOAD DATA의 효율성 비교 및 LOAD DATA를 빠르게 수행하는 방법에 대한 정보는 Section 10.2.5.1, “Optimizing INSERT Statements”를 참조하십시오.

• Non-LOCAL Versus LOCAL Operation

• Input File Character Set

• Input File Location

• Security Requirements

• Duplicate-Key and Error Handling

• Index Handling

• Field and Line Handling

• Column List Specification

• Input Preprocessing

• Column Value Assignment

• Partitioned Table Support

• Concurrency Considerations

• Statement Result Information

• Replication Considerations

• Miscellaneous Topics

Non-LOCAL Versus LOCAL Operation

LOCAL 한정자는 non-LOCAL 동작과 비교했을 때 LOAD DATA의 다음 측면에 영향을 줍니다:

• 입력 파일의 예상 위치를 변경합니다. Input File Location을 참조하십시오.

• statement에 필요한 보안 요구 사항을 변경합니다. Security Requirements를 참조하십시오.

• REPLACE가 같이 지정되지 않는 한, LOCAL은 입력 파일 내용 해석 및 에러 처리에 대해 IGNORE 한정자와 동일한 효과를 가집니다. Duplicate-Key and Error Handling 및 Column Value Assignment를 참조하십시오.

LOCAL은 서버와 클라이언트 둘 다에서 이를 허용하도록 설정된 경우에만 동작합니다. 예를 들어, mysqld가 local_infile 시스템 변수(system variable)가 비활성화된 상태에서 시작되었다면, LOCAL은 에러를 발생시킵니다. Section 8.1.6, “Security Considerations for LOAD DATA LOCAL”를 참조하십시오.

Input File Character Set

파일 이름은 리터럴 문자열로 지정해야 합니다. Windows에서는 경로 이름의 백슬래시를 슬래시 또는 백슬래시 두 개로 지정해야 합니다. 서버는 character_set_filesystem 시스템 변수에 의해 지정되는 문자 집합을 사용하여 파일 이름을 해석합니다.

기본적으로, 서버는 character_set_database 시스템 변수에 의해 지정되는 문자 집합을 사용하여 파일 내용을 해석합니다. 파일 내용이 이 기본값과 다른 문자 집합을 사용하는 경우, CHARACTER SET 절을 사용하여 해당 문자 집합을 지정하는 것이 좋습니다. binary 문자 집합은 “변환 없음”을 의미합니다.

SET NAMES와 character_set_client 설정은 파일 내용 해석에 영향을 주지 않습니다.

LOAD DATA는 파일의 모든 필드를, 값이 로드되는 컬럼의 데이터 타입과 상관없이 동일한 문자 집합을 사용하는 것으로 해석합니다. 파일이 올바르게 해석되려면 올바른 문자 집합으로 작성되었는지 확인해야 합니다. 예를 들어, mysqldump -T를 사용하거나 mysql에서 SELECT ... INTO OUTFILE statement를 실행하여 데이터 파일을 작성하는 경우, 파일이 나중에 LOAD DATA로 로드될 때 사용할 문자 집합으로 출력이 기록되도록 --default-character-set 옵션을 사용해야 합니다.

참고

ucs2, utf16, utf16le, utf32 문자 집합을 사용하는 데이터 파일은 로드할 수 없습니다.

Input File Location

다음 규칙은 LOAD DATA 입력 파일 위치를 결정합니다:

• LOCAL이 지정되지 않은 경우, 파일은 서버 호스트에 있어야 합니다. 서버는 다음과 같이 파일을 직접 읽습니다:

  • 파일 이름이 절대 경로 이름이면, 서버는 이를 그대로 사용합니다.

  • 파일 이름이 선행 컴포넌트가 있는 상대 경로 이름이면, 서버는 자신의 데이터 디렉터리를 기준으로 파일을 찾습니다.

  • 파일 이름에 선행 컴포넌트가 없으면, 서버는 기본 데이터베이스의 데이터베이스 디렉터리에서 파일을 찾습니다.

• LOCAL이 지정된 경우, 파일은 클라이언트 호스트에 있어야 합니다. 클라이언트 프로그램은 다음과 같이 파일을 읽습니다:

  • 파일 이름이 절대 경로 이름이면, 클라이언트 프로그램은 이를 그대로 사용합니다.

  • 파일 이름이 상대 경로 이름이면, 클라이언트 프로그램은 자신의 실행 디렉터리를 기준으로 파일을 찾습니다.

LOCAL이 사용되면, 클라이언트 프로그램은 파일을 읽고 그 내용을 서버로 전송합니다. 서버는 파일을 임시 파일을 저장하는 디렉터리에 복사본으로 생성합니다. Section B.3.3.5, “Where MySQL Stores Temporary Files”를 참조하십시오. 이 디렉터리에 복사본을 저장할 충분한 공간이 없으면 LOAD DATA LOCAL statement가 실패할 수 있습니다.

non-LOCAL 규칙은 서버가 ./myfile.txt라는 파일을 자신의 데이터 디렉터리 기준으로 읽는다는 것을 의미하며, myfile.txt라는 파일은 기본 데이터베이스의 데이터베이스 디렉터리에서 읽습니다. 예를 들어, 다음 LOAD DATA statement가 기본 데이터베이스가 db1인 상태에서 실행되면, 서버는 파일을 db2 데이터베이스의 테이블로 명시적으로 로드하더라도 db1의 데이터베이스 디렉터리에서 data.txt 파일을 읽습니다:

1LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

참고

서버는 또한 non-LOCAL 규칙을 사용하여 IMPORT TABLE statement를 위한 .sdi 파일의 위치를 찾습니다.

Security Requirements

non-LOCAL load 작업에서는 서버 호스트에 있는 텍스트 파일을 서버가 읽으므로, 다음 보안 요구 사항을 만족해야 합니다:

• FILE 권한(privilege)이 있어야 합니다. Section 8.2.2, “Privileges Provided by MySQL”를 참조하십시오.

• 이 작업은 secure_file_priv 시스템 변수 설정의 영향을 받습니다:

  • 변수 값이 비어 있지 않은 디렉터리 이름이면, 파일은 해당 디렉터리에 있어야 합니다.

  • 변수 값이 비어 있으면(이는 안전하지 않습니다), 파일은 서버에서 읽을 수 있으면 됩니다.

LOCAL load 작업에서는 클라이언트 프로그램이 클라이언트 호스트에 있는 텍스트 파일을 읽습니다. 파일 내용은 클라이언트에 의해 연결을 통해 서버로 전송되므로, LOCAL을 사용하는 것은 서버가 파일에 직접 접근하는 경우보다 약간 느립니다. 반면에, FILE 권한이 필요 없고, 파일은 클라이언트 프로그램이 접근할 수 있는 아무 디렉터리에나 위치할 수 있습니다.

Duplicate-Key and Error Handling

REPLACE와 IGNORE 한정자는 유니크 키 값(PRIMARY KEY나 UNIQUE 인덱스 값)을 기준으로 기존 테이블 행을 중복하는 새로운(입력) 행을 어떻게 처리할지 제어합니다:

• REPLACE를 사용하면, 유니크 키 값이 기존 행과 동일한 새로운 행은 기존 행을 대체합니다. Section 15.2.12, “REPLACE Statement”를 참조하십시오.

• IGNORE를 사용하면, 유니크 키 값이 기존 행을 중복하는 새로운 행은 버려집니다. 자세한 내용은 The Effect of IGNORE on Statement Execution을 참조하십시오.

LOCAL 한정자는 IGNORE와 동일한 효과를 갖습니다. 이는 작업 도중에 서버가 파일 전송을 중간에 중단시킬 방법이 없기 때문에 발생합니다.

REPLACE, IGNORE, LOCAL 중 어느 것도 지정되지 않으면, 중복 키 값이 발견될 때 에러가 발생하고 텍스트 파일의 나머지 부분은 무시됩니다.

위에서 설명한 중복 키 처리에 영향을 줄 뿐만 아니라, IGNORE와 LOCAL은 에러 처리에도 영향을 줍니다:

• IGNORE도 LOCAL도 지정되지 않은 경우, 데이터 해석 에러가 작업을 종료시킵니다.

• IGNORE 또는 REPLACE 없이 사용된 LOCAL이 지정된 경우, 데이터 해석 에러는 워닝으로 처리되며, SQL 모드가 제한적(restrictive)하더라도 load 작업은 계속됩니다. 예시는 Column Value Assignment를 참조하십시오.

Index Handling

load 작업 동안 외래 키 제약을 무시하려면, LOAD DATA를 실행하기 전에 SET foreign_key_checks = 0 statement를 실행하십시오.

빈 MyISAM 테이블에서 LOAD DATA를 사용하는 경우, 모든 비유니크 인덱스는 별도의 배치로 생성됩니다(이는 REPAIR TABLE와 동일합니다). 일반적으로, 이는 인덱스가 많을 때 LOAD DATA를 훨씬 빠르게 만듭니다. 어떤 극단적인 경우에는, 파일을 테이블에 로드하기 전에 ALTER TABLE ... DISABLE KEYS로 인덱스를 끄고, 파일 로드 후 ALTER TABLE ... ENABLE KEYS로 인덱스를 다시 생성하는 것이 인덱스를 더 빠르게 생성하는 방법이 될 수 있습니다. Section 10.2.5.1, “Optimizing INSERT Statements”를 참조하십시오.

Field and Line Handling

LOAD DATA와 SELECT ... INTO OUTFILE statement 모두에서 FIELDS와 LINES 절의 구문은 동일합니다. 두 절 모두 optional이지만, 둘 다 지정하는 경우 FIELDS가 LINES보다 먼저 와야 합니다.

FIELDS 절을 지정하는 경우, 그 하위 절(TERMINATED BY, [OPTIONALLY] ENCLOSED BY, ESCAPED BY) 역시 optional이지만, 최소한 하나는 지정해야 합니다. 이 절들의 인자에는 ASCII 문자만 사용할 수 있습니다.

FIELDS나 LINES 절을 전혀 지정하지 않으면, 이는 다음과 같이 작성한 것과 동일한 기본값을 사용합니다:

1FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
2LINES TERMINATED BY '\n' STARTING BY ''

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

다시 말해, 기본값은 입력을 읽을 때 LOAD DATA가 다음과 같이 동작하도록 만듭니다:

• 개행에서 라인 경계를 찾습니다.

• 어떤 라인 prefix도 건너뛰지 않습니다.

• 탭에서 라인을 필드로 분할합니다.

• 필드가 따옴표 문자 내에 둘러싸여 있다고 기대하지 않습니다.

• 이스케이프 문자 \가 앞에 오는 문자를 이스케이프 시퀀스로 해석합니다. 예를 들어, \t, \n, \\은 각각 탭, 개행, 백슬래시를 의미합니다. 전체 이스케이프 시퀀스 목록은 나중에 나오는 FIELDS ESCAPED BY 설명을 참조하십시오.

반대로, 기본값은 SELECT ... INTO OUTFILE가 출력(output)을 쓸 때 다음과 같이 동작하도록 만듭니다:

• 필드 사이에 탭을 기록합니다.

• 필드를 따옴표 문자 내에 둘러싸지 않습니다.

• 필드 값 내에 탭, 개행, \가 등장하는 경우 \를 사용해 이를 이스케이프합니다.

• 라인 끝에 개행을 기록합니다.

참고

Windows 시스템에서 생성된 텍스트 파일의 경우, Windows 프로그램은 일반적으로 두 문자를 라인 종료자로 사용하므로, 올바른 파일 읽기를 위해 LINES TERMINATED BY '\r\n'이 필요할 수 있습니다. WordPad와 같은 일부 프로그램은 파일을 쓸 때 \r을 라인 종료자로 사용할 수 있습니다. 이러한 파일을 읽으려면 LINES TERMINATED BY '\r'를 사용하십시오.

모든 입력 라인에 무시하고 싶은 공통 prefix가 있는 경우, LINES STARTING BY 'prefix_string'을 사용하여 prefix와 그 이전의 모든 것 을 건너뛸 수 있습니다. 라인에 prefix가 포함되지 않으면, 전체 라인이 건너뛰어집니다. 다음과 같은 statement를 실행한다고 가정해 봅니다:

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

데이터 파일이 다음과 같다면:

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

결과 행은 ("abc",1)과 ("def",2)입니다. 세 번째 행은 prefix를 포함하지 않기 때문에 건너뛰어집니다.

IGNORE number LINES 절은 파일 시작 부분의 라인을 무시하는 데 사용될 수 있습니다. 예를 들어, 컬럼 이름이 포함된 처음 헤더 라인을 건너뛰기 위해 IGNORE 1 LINES를 사용할 수 있습니다:

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

데이터베이스에서 파일로 데이터를 쓴 후 나중에 그 파일을 데이터베이스로 다시 읽어 들이기 위해 SELECT ... INTO OUTFILE와 LOAD DATA를 함께 사용하는 경우, 두 statement의 필드 및 라인 처리 옵션은 일치해야 합니다. 그렇지 않으면 LOAD DATA가 파일 내용을 올바르게 해석하지 못합니다. SELECT ... INTO OUTFILE](https://dev.mysql.com/doc/refman/9.5/en/select-into.html "15.2.13.1 SELECT ... INTO Statement")를 사용하여 콤마로 구분된 필드를 가진 파일을 작성한다고 가정해 봅니다:

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

콤마로 구분된 파일을 읽으려면 올바른 statement는 다음과 같습니다:

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

반대로, 다음에 보이는 statement로 파일을 읽으려고 하면 동작하지 않습니다. 이 statement는 필드 사이에 탭이 있다고 가정하도록 LOAD DATA에 지시하기 때문입니다:

1LOAD DATA INFILE 'data.txt' INTO TABLE table2
2  FIELDS TERMINATED BY '\t';

그 결과로는 각 입력 라인이 단일 필드로 해석될 가능성이 큽니다.

LOAD DATA는 외부 소스에서 가져온 파일을 읽는 데 사용할 수 있습니다. 예를 들어, 많은 프로그램은 comma-separated values(CSV) 형식으로 데이터를 export할 수 있습니다. 이 형식에서는 라인이 콤마로 구분된 필드와 double quotation mark로 둘러싸인 필드를 가지며, 첫 라인에는 컬럼 이름이 있습니다. 이러한 파일의 라인이 carriage return/newline 쌍으로 종료되는 경우, 다음 예시는 파일을 로드할 때 사용할 필드 및 라인 처리 옵션을 보여 줍니다:

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

입력 값이 반드시 quotation mark로 둘러싸여 있지 않은 경우, ENCLOSED BY 옵션 앞에 OPTIONALLY를 사용하십시오.

필드 또는 라인 처리 옵션은 빈 문자열('')을 지정할 수 있습니다. 비어 있지 않은 경우, FIELDS [OPTIONALLY] ENCLOSED BY와 FIELDS ESCAPED BY 값은 단일 문자여야 합니다. FIELDS TERMINATED BY, LINES STARTING BY, LINES TERMINATED BY 값은 여러 문자일 수 있습니다. 예를 들어, carriage return/linefeed 쌍으로 종료되는 라인을 쓰거나, 이러한 라인을 포함하는 파일을 읽으려면 LINES TERMINATED BY '\r\n' 절을 지정하십시오.

%%로 구성된 라인으로 농담(joke)이 구분되는 파일을 읽으려면 다음과 같이 할 수 있습니다:

1CREATE TABLE jokes
2  (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3  joke TEXT NOT NULL);
4LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes
5  FIELDS TERMINATED BY ''
6  LINES TERMINATED BY '\n%%\n' (joke);

FIELDS [OPTIONALLY] ENCLOSED BY는 필드 quoting을 제어합니다. 출력( SELECT ... INTO OUTFILE)의 경우, OPTIONALLY를 생략하면 모든 필드가 ENCLOSED BY 문자로 둘러싸입니다. (콤마를 필드 구분자로 사용하는) 이런 출력의 예는 다음과 같습니다:

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

OPTIONALLY를 지정하면, ENCLOSED BY 문자는 문자열 데이터 타입(예: CHAR, BINARY, TEXT, ENUM 등의 타입)을 가진 컬럼 값만 둘러싸는 데 사용됩니다:

11,"a string",100.20
22,"a string containing a , comma",102.20
33,"a string containing a \" quote",102.20
44,"a string containing a \", quote and comma",102.20

필드 값에 등장하는 ENCLOSED BY 문자는 ESCAPED BY 문자를 prefix로 붙여 이스케이프됩니다. 또한 ESCAPED BY 값을 빈 값으로 지정하면 LOAD DATA가 제대로 읽을 수 없는 출력을 의도치 않게 생성할 수 있습니다. 예를 들어, 바로 앞에서 보여준 출력에서 이스케이프 문자가 비어 있으면 다음과 같이 나타납니다. 네 번째 라인의 두 번째 필드에는 따옴표 뒤에 콤마가 포함되어 있는데, 이 콤마는 필드를 종료하는 것으로 (잘못) 보입니다:

11,"a string",100.20
22,"a string containing a , comma",102.20
33,"a string containing a " quote",102.20
44,"a string containing a ", quote and comma",102.20

입력의 경우, ENCLOSED BY 문자가 있으면 필드 값의 양 끝에서 제거됩니다(이는 OPTIONALLY 지정 여부와 관계없이 동일하며, OPTIONALLY는 입력 해석에는 영향을 주지 않습니다). ENCLOSED BY 문자가 ESCAPED BY 문자로 prefix된 경우, 해당 문자는 현재 필드 값의 일부로 해석됩니다.

필드가 ENCLOSED BY 문자로 시작하는 경우, 해당 문자가 필드 또는 라인 TERMINATED BY 시퀀스 다음에 오는 경우에만 필드 값을 종료시키는 문자로 인식됩니다. 모호함을 피하기 위해, 필드 값 내부에 등장하는 ENCLOSED BY 문자는 두 번 써서 하나의 문자로 해석되도록 할 수 있습니다. 예를 들어, ENCLOSED BY '"'가 지정된 경우, quotation mark는 다음과 같이 처리됩니다:

1"The ""BIG"" boss"  -> The "BIG" boss
2The "BIG" boss      -> The "BIG" boss
3The ""BIG"" boss    -> The ""BIG"" boss

FIELDS ESCAPED BY는 특수 문자를 읽거나 쓰는 방식을 제어합니다:

• 입력의 경우, FIELDS ESCAPED BY 문자가 비어 있지 않다면, 해당 문자가 등장하면 제거되고 그 다음 문자는 필드 값의 일부로 그대로 사용됩니다. 이 규칙의 예외인 두 문자 시퀀스가 있는데, 첫 번째 문자는 이스케이프 문자입니다. 이러한 시퀀스는 다음 테이블에 나와 있습니다(이스케이프 문자로 \를 사용). NULL 처리 규칙은 이 섹션 뒷부분에서 설명합니다.

Character	Escape Sequence
\0	ASCII NUL (X'00') 문자
\b	백스페이스 문자
\n	개행(linefeed) 문자
\r	캐리지 리턴 문자
\t	탭 문자
\Z	ASCII 26(Control+Z)
\N	NULL

\ 이스케이프 구문에 대한 자세한 내용은 Section 11.1.1, “String Literals”를 참조하십시오.

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

• 출력의 경우, FIELDS ESCAPED BY 문자가 비어 있지 않다면, 다음 문자를 출력할 때 이 문자를 prefix로 사용합니다:

  • FIELDS ESCAPED BY 문자.

  • FIELDS [OPTIONALLY] ENCLOSED BY 문자.

  • ENCLOSED BY 문자가 비어 있거나 지정되지 않은 경우, FIELDS TERMINATED BY 및 LINES TERMINATED BY 값의 첫 글자.

  • ASCII 0 (이스케이프 문자 뒤에 실제로 쓰이는 것은 zero-valued 바이트가 아니라 ASCII 0입니다).

FIELDS ESCAPED BY 문자가 비어 있으면, 어떠한 문자도 이스케이프되지 않고 NULL은 \N이 아닌 NULL로 출력됩니다. 특히 필드 값에 위 목록의 문자가 포함되어 있는 경우, 이스케이프 문자를 빈 값으로 지정하는 것은 좋은 생각이 아닙니다.

일정한 경우에는 필드 및 라인 처리 옵션이 서로 상호작용합니다:

• LINES TERMINATED BY가 빈 문자열이고 FIELDS TERMINATED BY가 비어 있지 않으면, 라인은 FIELDS TERMINATED BY로도 종료됩니다.

• FIELDS TERMINATED BY와 FIELDS ENCLOSED BY 값이 모두 빈 값('')이면, fixed-row(비구분자) 형식이 사용됩니다. fixed-row 형식에서는 필드 사이에 구분자를 사용하지 않습니다(하지만 라인 종료자는 사용할 수 있습니다). 대신, 컬럼 값은 해당 필드의 모든 값을 담을 수 있을 만큼 충분히 넓은 필드 폭(field width)을 사용해 읽고 쓰입니다. TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT의 필드 폭은 선언된 표시 폭(display width)과 관계없이 각각 4, 6, 8, 11, 20입니다.

LINES TERMINATED BY는 여전히 라인을 구분하는 데 사용됩니다. 라인에 모든 필드가 포함되어 있지 않으면, 나머지 컬럼은 기본값으로 설정됩니다. 라인 종료자가 없으면, 이를 ''로 설정해야 합니다. 이 경우, 텍스트 파일에는 각 행에 대해 모든 필드가 포함되어 있어야 합니다.

fixed-row 형식에서는 NULL 값 처리 방식도 달라지며, 이에 대해서는 이 섹션 뒷부분에서 설명합니다.

참고

fixed-size 형식은 멀티바이트 문자 집합을 사용하는 경우 동작하지 않습니다.

NULL 값의 처리 방식은 사용 중인 FIELDS 및 LINES 옵션에 따라 달라집니다:

• 기본 FIELDS 및 LINES 값에서는, NULL은 출력 시 필드 값 \N으로 쓰이고, 입력 시 필드 값 \N은( ESCAPED BY 문자가 \라고 가정) NULL로 읽힙니다.

• FIELDS ENCLOSED BY가 비어 있지 않으면, 리터럴 단어 NULL을 값으로 포함하는 필드는 NULL 값으로 읽힙니다. 이는 FIELDS ENCLOSED BY 문자로 둘러싸인 단어 NULL이 문자열 'NULL'로 읽히는 것과 다릅니다.

• FIELDS ESCAPED BY가 비어 있으면, NULL은 단어 NULL로 쓰입니다.

• fixed-row 형식( FIELDS TERMINATED BY와 FIELDS ENCLOSED BY가 모두 빈 값일 때 사용)에서는 NULL이 빈 문자열로 쓰입니다. 이로 인해 테이블의 NULL 값과 빈 문자열이 파일에 쓰일 때 모두 빈 문자열로 기록되므로, 파일을 다시 읽어 들일 때 두 값을 구분할 수 없게 됩니다. 두 값을 구분해야 한다면 fixed-row 형식을 사용해서는 안 됩니다.

NOT NULL 컬럼에 NULL을 로드하려고 시도하면, Column Value Assignment에 설명된 규칙에 따라 워닝 또는 에러가 발생합니다.

LOAD DATA가 지원하지 않는 경우도 있습니다:

• fixed-size 행(FIELDS TERMINATED BY와 FIELDS ENCLOSED BY가 모두 빈 값)과 BLOB 또는 TEXT 컬럼의 조합.

• 한 구분자가 다른 구분자와 동일하거나 그 prefix인 경우, LOAD DATA는 입력을 올바르게 해석할 수 없습니다. 예를 들어, 다음과 같은 FIELDS 절은 문제를 일으킵니다:

1FIELDS TERMINATED BY '"' ENCLOSED BY '"'

• FIELDS ESCAPED BY가 비어 있으면, 필드 값에 FIELDS ENCLOSED BY 또는 LINES TERMINATED BY가 등장하고 그 뒤를 FIELDS TERMINATED BY 값이 따를 때, LOAD DATA는 필드 또는 라인 값을 너무 일찍 읽기 종료하게 됩니다. 이는 LOAD DATA가 필드 또는 라인 값의 끝을 제대로 판별할 수 없기 때문에 발생합니다.

Column List Specification

다음 예제는 persondata 테이블의 모든 컬럼을 로드합니다:

1LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

기본적으로, LOAD DATA statement 끝에 컬럼 목록을 제공하지 않으면, 입력 라인에는 테이블의 각 컬럼에 대한 필드가 포함되어 있어야 합니다. 테이블의 일부 컬럼만 로드하려면 컬럼 목록을 지정하십시오:

1LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata
2(col_name_or_user_var [, col_name_or_user_var] ...);

입력 파일에서 필드 순서가 테이블의 컬럼 순서와 다를 경우에도 컬럼 목록을 지정해야 합니다. 그렇지 않으면 MySQL은 입력 필드를 테이블 컬럼과 어떻게 매치해야 할지 알 수 없습니다.

Input Preprocessing

LOAD DATA 구문에서 각 col_name_or_user_var 는 컬럼 이름 또는 사용자 변수(user variable)입니다. 사용자 변수를 사용하면, SET 절로 컬럼에 결과를 할당하기 전에 해당 값에 대해 전처리 작업을 수행할 수 있습니다.

SET 절의 사용자 변수는 여러 방식으로 사용할 수 있습니다. 다음 예제에서 첫 번째 입력 컬럼은 t1.column1의 값으로 직접 사용되고, 두 번째 입력 컬럼은 나누기 연산을 수행한 후 t1.column2의 값에 사용되는 사용자 변수에 할당됩니다:

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

SET 절은 입력 파일에서 파생되지 않는 값을 제공하는 데 사용할 수 있습니다. 다음 statement는 column3을 현재 날짜와 시간으로 설정합니다:

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

입력 값을 사용자 변수에 할당한 다음, 해당 변수를 테이블 컬럼에 할당하지 않음으로써 입력 값을 버릴 수도 있습니다:

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

컬럼/변수 목록과 SET 절 사용에는 다음과 같은 제한 사항이 있습니다:

• SET 절의 할당에서 대입 연산자의 왼쪽에는 컬럼 이름만 있어야 합니다.

• SET 할당의 오른쪽에는 서브쿼리를 사용할 수 있습니다. 컬럼에 할당될 값을 반환하는 서브쿼리는 스칼라 서브쿼리만 허용됩니다. 또한, 로드 중인 테이블에서 select하는 서브쿼리는 사용할 수 없습니다.

• IGNORE number LINES 절에 의해 무시된 라인은 컬럼/변수 목록이나 SET 절에 대해 처리되지 않습니다.

• fixed-row 형식으로 데이터를 로드할 때는 사용자 변수를 사용할 수 없습니다. 사용자 변수에는 표시 폭(display width)이 없기 때문입니다.

Column Value Assignment

입력 라인을 처리하기 위해, LOAD DATA는 라인을 필드로 분할하고, 컬럼/변수 목록과 SET 절이 존재한다면 이에 따라 값을 사용합니다. 그런 다음, 결과 행을 테이블에 insert합니다. 테이블에 BEFORE INSERT 또는 AFTER INSERT 트리거가 있으면, 각각 행을 insert하기 전 또는 후에 트리거가 활성화됩니다.

필드 값의 해석 및 테이블 컬럼에 대한 할당은 다음 요소에 따라 달라집니다:

• SQL 모드( sql_mode 시스템 변수의 값). 모드는 제한적이지 않을 수도 있고(nonrestrictive), 여러 방식으로 제한적일 수도 있습니다. 예를 들어, strict SQL 모드를 활성화하거나, NO_ZERO_DATE나 NO_ZERO_IN_DATE와 같은 값을 포함하도록 설정할 수 있습니다.

• IGNORE 및 LOCAL 한정자의 존재 여부.

이러한 요소의 조합은 LOAD DATA가 데이터를 제한적으로 또는 비제한적으로 해석하도록 만듭니다:

• SQL 모드가 제한적이고 IGNORE 및 LOCAL 한정자가 모두 지정되지 않은 경우 데이터 해석은 제한적입니다. 에러가 발생하면 load 작업이 종료됩니다.

• SQL 모드가 제한적이지 않거나, IGNORE 또는 LOCAL 한정자가 지정된 경우 데이터 해석은 비제한적입니다. (특히, REPLACE 한정자가 생략된 경우 두 한정자 중 하나라도 지정되면 제한적 SQL 모드를 무시 합니다.) 에러는 워닝으로 바뀌고 load 작업은 계속됩니다.

제한적 데이터 해석은 다음 규칙을 사용합니다:

• 필드가 너무 많거나 너무 적으면 에러가 발생합니다.

• non-NULL 컬럼에 NULL(즉, \N)을 할당하면 에러가 발생합니다.

• 컬럼 데이터 타입의 범위를 벗어나는 값은 에러를 발생시킵니다.

• 잘못된 값은 에러를 발생시킵니다. 예를 들어, numeric 컬럼에 'x'와 같은 값을 사용하면 0으로 변환되지 않고 에러가 발생합니다.

반대로, 비제한적 데이터 해석은 다음 규칙을 사용합니다:

• 입력 라인에 필드가 너무 많으면, 초과 필드는 무시되고 워닝 개수가 증가합니다.

• 입력 라인에 필드가 너무 적으면, 필드가 부족한 컬럼은 기본값이 할당됩니다. 기본값 할당에 대해서는 Section 13.6, “Data Type Default Values”를 참조하십시오.

• non-NULL 컬럼에 NULL(즉, \N)을 할당하면 해당 컬럼 데이터 타입의 암시적 기본값(implicit default)이 할당됩니다. 암시적 기본값에 대해서는 Section 13.6, “Data Type Default Values”를 참조하십시오.

• 잘못된 값은 에러가 아닌 워닝을 발생시키며, 컬럼 데이터 타입에 대해 “가장 가까운” 유효한 값으로 변환됩니다. 예:

  • numeric 컬럼에 'x'와 같은 값을 사용하면 0으로 변환됩니다.

  • 범위를 벗어난 numeric 또는 temporal 값은 해당 컬럼 데이터 타입의 범위 끝점 중 가장 가까운 값으로 클리핑(clipping)됩니다.

  • DATETIME, DATE, TIME 컬럼에 대한 잘못된 값은 SQL 모드 NO_ZERO_DATE 설정과 관계없이 암시적 기본값으로 insert됩니다. 암시적 기본값은 타입에 적합한 “zero” 값입니다('0000-00-00 00:00:00', '0000-00-00', '00:00:00'). 이에 대해서는 Section 13.2, “Date and Time Data Types”를 참조하십시오.

• LOAD DATA는 빈 필드 값을 누락된 필드와 다르게 해석합니다:

  • 문자열 타입의 경우, 컬럼은 빈 문자열로 설정됩니다.

  • numeric 타입의 경우, 컬럼은 0으로 설정됩니다.

  • 날짜 및 시간 타입의 경우, 컬럼은 해당 타입에 적합한 “zero” 값으로 설정됩니다. Section 13.2, “Date and Time Data Types”를 참조하십시오.

이는 INSERT 또는 UPDATE statement에서 문자열, numeric, 날짜 또는 시간 타입에 빈 문자열을 명시적으로 할당할 때와 동일한 값입니다.

TIMESTAMP 컬럼은 해당 컬럼이 NULL 값을 허용하지 않고 컬럼에 NULL 값(즉, \N)이 있는 경우, 또는 TIMESTAMP 컬럼의 기본값이 현재 타임스탬프이며 필드 목록이 지정되었을 때 그 컬럼이 필드 목록에서 생략된 경우에만 현재 날짜와 시간으로 설정됩니다.

LOAD DATA는 모든 입력을 문자열로 간주하므로, INSERT statement에서 할 수 있는 것처럼 ENUM 또는 SET 컬럼에 numeric 값을 사용할 수 없습니다. 모든 ENUM 및 SET 값은 문자열로 지정해야 합니다.

BIT 값은 b'011010'과 같은 binary 표기법으로 직접 로드할 수 없습니다. 이를 해결하려면, SET 절을 사용하여 앞의 b'와 뒤의 '를 제거하고 base-2에서 base-10으로 변환하여 MySQL이 BIT 컬럼에 값을 올바르게 로드하도록 해야 합니다:

1$> cat /tmp/bit_test.txt
2b'10'
3b'1111111'
4$> mysql test
5mysql> LOAD DATA INFILE '/tmp/bit_test.txt'
6       INTO TABLE bit_test (@var1)
7       SET b = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-3), 2, 10) AS UNSIGNED);
8Query OK, 2 rows affected (0.00 sec)
9Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
10
11mysql> SELECT BIN(b+0) FROM bit_test;
12+----------+
13| BIN(b+0) |
14+----------+
15| 10       |
16| 1111111  |
17+----------+
182 rows in set (0.00 sec)

0b011010과 같은 0b binary 표기법의 BIT 값을 사용하려면, 앞의 0b를 제거하도록 대신 다음 SET 절을 사용하십시오:

1SET b = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-2), 2, 10) AS UNSIGNED)

Partitioned Table Support

LOAD DATA는 PARTITION 절을 사용하여 하나 이상의 콤마로 구분된 파티션, 서브파티션 또는 둘 다의 이름 목록으로 명시적 파티션 선택을 지원합니다. 이 절을 사용하는 경우, 파일의 행 중 어느 것도 목록에 지정된 파티션 또는 서브파티션에 insert할 수 없으면, Found a row not matching the given partition set 에러와 함께 statement가 실패합니다. 자세한 내용과 예시는 Section 26.5, “Partition Selection”을 참조하십시오.

Concurrency Considerations

LOW_PRIORITY 한정자를 사용하면, LOAD DATA statement 실행은 다른 클라이언트가 테이블을 읽고 있지 않을 때까지 지연됩니다. 이는 MyISAM, MEMORY, MERGE와 같이 테이블 수준 잠금(table-level locking)만 사용하는 스토리지 엔진에만 영향을 줍니다.

CONCURRENT 한정자와, 가운데에 free block이 없는(concurrent insert 조건을 만족하는) MyISAM 테이블을 함께 사용하는 경우, LOAD DATA가 실행되는 동안 다른 스레드가 테이블에서 데이터를 조회할 수 있습니다. 이 한정자는 다른 스레드가 동시에 테이블을 사용하지 않더라도 LOAD DATA의 성능에 약간의 영향을 줍니다.

Statement Result Information

LOAD DATA statement가 끝나면, 다음 형식의 information 문자열을 반환합니다:

1Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

워닝은 INSERT statement를 사용하여 값을 insert할 때와 동일한 상황에서 발생합니다( Section 15.2.7, “INSERT Statement”를 참조). 단, LOAD DATA는 input 행에 필드가 너무 적거나 너무 많은 경우에도 워닝을 생성합니다.

SHOW WARNINGS를 사용하여 잘못된 점에 대한 정보로서 처음 max_error_count개 워닝 목록을 확인할 수 있습니다. Section 15.7.7.43, “SHOW WARNINGS Statement”를 참조하십시오.

C API를 사용하는 경우, mysql_info() 함수를 호출하여 statement에 대한 정보를 얻을 수 있습니다. mysql_info()를 참조하십시오.

Replication Considerations

LOAD DATA는 statement-based replication에 대해 unsafe한 것으로 간주됩니다. binlog_format=STATEMENT를 사용하여 LOAD DATA를 사용하는 경우, 변경 사항이 적용될 각 replica는 데이터를 포함하는 임시 파일을 생성합니다. 이 임시 파일은 소스에서 binary log 암호화가 활성화되어 있더라도 암호화되지 않습니다. 암호화가 필요하다면 replica가 임시 파일을 생성하지 않는 row-based 또는 mixed binary logging 형식을 대신 사용하십시오. LOAD DATA와 replication 간 상호작용에 대한 자세한 내용은 Section 19.5.1.20, “Replication and LOAD DATA”를 참조하십시오.

Miscellaneous Topics

Unix에서 LOAD DATA가 파이프에서 읽어야 하는 경우, 다음과 같은 기법을 사용할 수 있습니다(이 예제는 / 디렉터리의 listing을 db1.t1 테이블에 로드합니다):

1mkfifo /mysql/data/db1/ls.dat
2chmod 666 /mysql/data/db1/ls.dat
3find / -ls > /mysql/data/db1/ls.dat &
4mysql -e "LOAD DATA INFILE 'ls.dat' INTO TABLE t1" db1

이 경우, 데이터를 생성하는 명령과 mysql 명령은 별도의 터미널에서 실행하거나(위 예제처럼) 데이터 생성 프로세스를 백그라운드에서 실행해야 합니다. 이렇게 하지 않으면, mysql 프로세스가 데이터를 읽을 때까지 파이프가 블록됩니다.