MySQL - LOAD DATA INFILE

 
대용량 데이터 import시 Tool(Workbench등..)을 사용할 경우 시간이 오래 걸리는데 LOAD DATA를 이용하면 빠르게 import할 수 있다.

1. MySQL 접속 및 설정 확인

mysql -h {HOST} -u {USER} -p --local-infile=1

• --local-infile=1: LOAD DATA LOCAL INFILE 사용을 활성화
• 비밀번호 입력 후 접속

2. CSV 파일 인코딩 확인 및 변환(MacOS 기준)

file -I /path/to/file.csv

• 출력 결과: charset=iso-8859-1
• UTF-8 변환 필요 시:

iconv -f CP949 -t UTF-8 /path/to/file.csv > /path/to/file-utf8.csv

• ⚠️ iconv 사용 시 한글이 깨지는 경우
  만약 한글이 깨진다면, 원본 데이터가 실제로 EUC-KR 또는 CP949였는데 잘못 저장된 것일 가능성이 있음
  이 경우, ISO-8859-1 대신 EUC-KR 또는 CP949로 변환
• Windows ↔︎ MacOS 인코딩 호환 필요 시 변환하여 사용

3. LOAD DATA INFILE 기본 구조

LOAD DATA LOCAL INFILE '/path/to/file.csv'
INTO TABLE test_table
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

옵션 설명

• CHARACTER SET utf8mb4: UTF-8 인코딩 설정
• FIELDS TERMINATED BY ',': 필드 구분자 설정 (쉼표)
• ENCLOSED BY '"': 문자열이 큰따옴표로 감싸져 있는 경우 처리
• LINES TERMINATED BY '\r\n': 줄바꿈 문자 설정
• IGNORE 1 LINES: 첫 번째 행(헤더) 무시

4. 데이터 형 변환 및 @ 변수 사용

문제 상황: 데이터 타입 불일치

• bit(1), int, boolean 컬럼의 경우 @ 변수 사용 필요
• CSV의 NULL 값은 공백('')으로 표시되며, 
  int_column = NULLIF(@int_column, ''); 또는 int_column = @int_column을 해도됨
• 컬럼을 명시 안하면 NULL 값으로 삽입됨
• 모든 컬럼을 @변수 사용을 하지 않을 경우 CSV파일 기준으로 컬럼순서 정렬 필요

LOAD DATA LOCAL INFILE '/path/to/file.csv'
INTO TABLE test_table
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(@bit_column, @int_column, normal_column)
SET
    bit_column = IF(LOWER(@bit_column) IN ('true', '1'), b'1', b'0'),
    int_column = NULLIF(@int_column, '');

5. 외래 키(Foreign Key) 체크 비활성화 (TRUNCATE 시 필요)

SET foreign_key_checks = 0;  -- 외래 키 제약 비활성화
TRUNCATE TABLE test_table;
SET foreign_key_checks = 1;  -- 외래 키 제약 활성화

6. 테이블별 데이터 적재 예시

test_table_config 테이블 (bit 데이터 변환 포함)

LOAD DATA LOCAL INFILE '/path/to/test_table_config.csv'
INTO TABLE test_table_config
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(@flag, biz_code, @is_visible, item_code, start_date, end_date)
SET
    flag = IF(LOWER(@flag) IN ('true', '1'), b'1', b'0'),
    is_visible = IF(LOWER(@is_visible) IN ('true', '1'), b'1', b'0');

test_table_logs 테이블

LOAD DATA LOCAL INFILE '/path/to/test_table_logs.csv'
INTO TABLE test_table_logs
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(test_table_config_id, log_details, log_type, user, log_sequence, created_at);

7. 에러 확인 방법

SHOW WARNINGS LIMIT 50;

• Row doesn't contain data for all columns: 컬럼 개수 불일치 오류
• Data too long for column: 데이터 길이 초과 오류
• Incorrect datetime value: 잘못된 날짜 형식 오류

---

✅ 핵심 요약

1. 파일 인코딩 확인: file -I 사용, UTF-8 변환 필요 시 iconv 사용
2. CSV 데이터와 테이블 컬럼 순서 맞추기
3. 형 변환(@변수 사용): bit(1), int, boolean 처리 시 필요
4. 외래 키 체크 해제 후 TRUNCATE 가능
5. 에러 발생 시 SHOW WARNINGS LIMIT 50; 로 원인 분석