MySQL LOAD DATA

MySQL 테이블에 Text 파일을 빠르게 Insert 하는 방벙

MySQL LOAD DATA

• https://dev.mysql.com/doc/refman/8.0/en/load-data.html
  • The LOAD DATA statement reads rows from a text file into a table at a very high speed

LOAD DATA statement

사전 준비 : secure_file_priv

• secure_file_priv 의 설정값이 NULL 로 되어 있는데 빈문자열로 수정해야 함

SHOW VARIABLES LIKE 'secure_file_priv'  -- NULL 

• my.cnf 에서 수정 및 재시작 필요

[mysqld]
secure-file-priv=""

LOAD DATA 최소 설정

LOAD DATA INFILE '/path/data.txt' INTO TABLE data_table 

Field and Line Handling : Default 설정

LOAD DATA INFILE '/path/data.txt' INTO TABLE data_table
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

헤더 라인 무시

LOAD DATA INFILE '/path/data.txt' INTO TABLE data_table IGNORE 1 LINES

LOAD DATA 기본 설정 (CSV)

LOAD DATA INFILE '/path/data.txt' IGNORE INTO TABLE data_table 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\n' 
(filed1, filed2, filed3 )

Duplicate-Key and Error Handling

• IGNORE : unique key 값이 있으면 새로운 행은 버려짐 default
• REPLACE : unique key 값 기준으로 새로운 행으로 변경

필드 개수, 순서

• 기본적으로 파일의 구분자 기준 순서와 필드와 1:1 매치됨
• 필드 지정을 통해 순서 조정 가능
  • 파일의 구분자 필드가 많으면 : 버려짐
  • 테이블의 필드가 많으면 : 나머지 필드는 Default 값이나 NULL 처리 (NULL 허용시)

Input Preprocessing

• 테이블의 중간 필드를 무시 (Default 값 or NULL 처리) 하려면 @dummy 키워드로 필드 지정
• 필드 가공은 SET 명령으로 처리
• @변수 명으로 받아서 가공후 필드에 지정할 수 있음

LOAD DATA INFILE '/path/data.txt' IGNORE INTO TABLE data_table 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' 
(filed1, @dummy, filed3, @var1, filed5 )
SET filed2 = CURRENT_TIMESTAMP, filed4 = @var1 * 2;

Index Handling : LOAD DATA 문의 성능향상을 위한 Index check option

• unique index checks option : SET unique_checks = 0 SET unique_checks = 1
• foreign key check option : SET foreign_key_checks = 0 SET foreign_key_checks = 1

---

로컬(클라이언트) 데이터 원격 INSERT

• https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html

사전 준비 : local_infile 설정 (server and client)

• server : local_infile 의 설정값이 ON(1) 로 수정해야 함
• client : 접속시 --local-infile 옵션 추가

SHOW VARIABLES LIKE 'local_infile' ; -- OFF
SET GLOBAL local_infile = 1 ;
SHOW VARIABLES LIKE 'local_infile' ; -- ON

LOCAL PATH 지정

• LOAD DATA LOCAL INFILE ...

LOAD DATA LOCAL INFILE '/path/data.txt' INTO TABLE data_table