MariaDB / DATA / CSV 파일 데이터 가져오는 방법

MariaDB에서 CSV 파일을 가져오려면 LOAD DATA INFILE에 파일의 구분자·따옴표·줄바꿈·문자셋과 대상 열을 지정합니다. 클라이언트 PC의 파일은 LOAD DATA LOCAL INFILE, 데이터베이스 서버의 파일은 LOCAL 없는 구문으로 읽습니다. 업무 테이블에 곧바로 넣기보다 별도 임시 적재 테이블에서 경고·행 수·자료형·중복을 확인한 뒤 반영하면 잘못된 변환을 발견하기 쉽습니다.

파일을 읽는 위치와 필요한 권한

LOAD DATA INFILE 문서의 LOCAL은 데이터베이스가 같은 PC에 있다는 뜻이 아닙니다. SQL을 실행하는 클라이언트가 파일을 읽어 서버로 보낸다는 뜻입니다. SSH로 원격 컴퓨터의 mariadb를 실행한다면 그 원격 컴퓨터가 클라이언트이므로, 내 노트북의 경로를 그대로 사용할 수 없습니다.

LOCAL은 FILE 권한을 요구하지 않지만, 신뢰할 수 없는 서버 연결에 무조건 허용할 기능도 아닙니다. 아래 명령은 접근 권한을 가진 신뢰할 수 있는 로컬 실습 서버에 연결하는 예이며, 사용자명·포트·데이터베이스는 실제 값으로 바꿉니다. --password 뒤에 값을 쓰지 않으면 비밀번호를 입력하라는 프롬프트가 나옵니다.

mariadb --host=127.0.0.1 --port=3306 --user=import_user --password --local-infile=1 --default-character-set=utf8mb4 learning

mariadb 클라이언트 옵션의 --local-infile=1은 그 연결에서 클라이언트 기능을 켜는 값입니다. 서버에서 local_infile을 금지했으면 이것만으로 허용되지 않습니다. 관리자 정책으로 차단된 서버의 전역 설정을 임의로 바꾸기보다 승인된 가져오기 경로를 사용하세요. 작업이 끝난 연결은 종료하고 일반 연결에 LOCAL 기능을 계속 켜 두지 않습니다.

CSV의 열·인코딩·줄바꿈 규칙 정하기

CSV(Comma-Separated Values)는 쉼표로 필드를 나누는 텍스트 형식입니다. RFC 4180은 필드 안 쉼표·큰따옴표 등의 표현을 설명하지만 실제 프로그램이 만드는 CSV의 세부 규칙은 다를 수 있습니다. 내보내기 프로그램에서 파일 인코딩·구분자·줄바꿈·헤더 유무를 확인해야 합니다.

Windows 예제 경로 C:/csv-import/items.csv에 아래 내용을 저장합니다. 폴더는 직접 만들고 UTF-8, BOM 없음, LF 줄바꿈으로 저장하세요. SQL 문자열에는 Windows에서도 / 경로 구분자를 쓰면 역슬래시 이스케이프 혼동을 줄일 수 있습니다. 다른 OS나 폴더에서는 파일의 실제 절대 경로로 바꿉니다.

sku,name,quantity
A001,"연필, 검정",10
A002,"메모 ""소형""",0
A003,공책,12

첫 줄은 열 이름입니다. 연필, 검정은 큰따옴표로 감싸 한 필드로 만들었고, 메모 "소형"의 큰따옴표는 두 번 연속 써서 표현했습니다. 이 예제에는 필드 안 줄바꿈과 역슬래시 이스케이프를 사용하지 않습니다. 빈 값은 자동으로 NULL과 같아지는 것으로 가정하지 않습니다. 엑셀 화면의 표시값과 저장한 텍스트의 실제 날짜·선행 0·소수점 형식이 다를 수도 있으므로 텍스트 편집기로 원본을 확인하세요.

원시 문자열로 적재할 테이블 준비

실습용 learning 데이터베이스가 준비되어 있고 CREATE·INSERT·SELECT 권한이 있다고 가정합니다. 아래 이름의 테이블이 이미 있다면 지우거나 덮어쓰지 말고 다른 실습 이름으로 바꾸세요. 적재용 csv_stage_demo는 수량도 문자열로 받아 숫자 변환 전에 원본을 살펴보기 위한 일반 테이블입니다. SQL의 TEMPORARY TABLE은 아니므로 접속을 바꿔도 같은 테이블을 조회할 수 있습니다.

USE learning;
CREATE TABLE csv_stage_demo (
    sku VARCHAR(100),
    name VARCHAR(200),
    quantity_text VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE csv_items_demo (
    sku VARCHAR(100) PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    quantity INT UNSIGNED NOT NULL,
    CHECK (quantity <= 999999)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SELECT @@global.local_infile AS server_local_infile;

server_local_infile=1이면 서버 측 LOCAL 기능이 허용된 상태입니다. 0이면 연결 옵션만 반복해서 바꾸지 말고 관리자 정책을 확인합니다. 적재용 테이블도 열 길이를 넘는 값을 무제한 보존하는 것은 아니므로, 실제 파일의 최대 길이를 고려해 설계해야 합니다. 새 파일을 기존 적재 테이블에 누적하면 중복 검토가 혼란스러워지므로 이번 실습에서는 방금 만든 빈 테이블에 한 번만 가져옵니다.

LOAD DATA 실행 후 경고와 세 행 확인

LOAD DATA LOCAL INFILE 'C:/csv-import/items.csv'
INTO TABLE csv_stage_demo
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(sku, name, quantity_text);
SHOW WARNINGS;
SELECT sku, name, quantity_text FROM csv_stage_demo ORDER BY sku;
SELECT COUNT(*) AS loaded_rows FROM csv_stage_demo;

FIELDS는 쉼표를 열 구분자로, OPTIONALLY ENCLOSED는 큰따옴표로 감싼 필드를 허용하도록 정합니다. ESCAPED BY도 큰따옴표로 정해 이 파일의 겹큰따옴표 규칙에 맞춥니다. IGNORE 1 LINES는 첫 헤더 줄만 건너뛰며, 중복 키를 무시하는 별도의 IGNORE 옵션과는 다릅니다. 마지막 열 목록은 파일 순서대로 어느 테이블 열에 넣을지 정합니다.

경고가 없고 loaded_rows=3이어야 합니다. 이름은 A001의 연필, 검정과 A002의 메모 "소형"처럼 저장되고, quantity_text에는 10·0·12가 남아야 합니다. SHOW WARNINGS는 같은 연결에서 적재 직후 실행합니다. 다른 테이블 조회를 먼저 하면 확인하려던 경고 목록이 지워질 수 있습니다. Query OK만 보고 전체 파일이 정확히 반영됐다고 판단하지 않습니다.

파일이 CRLF 줄바꿈이라면 LINES TERMINATED BY의 값을 \r\n으로 바꿉니다. LF 파일에 그 값을 쓰거나 CRLF 파일을 무조건 \n으로 읽으면 마지막 열에 남은 문자 등 예상하지 못한 결과가 생길 수 있습니다. 헤더가 없으면 IGNORE 1 LINES를 제거합니다. 실제 파일이 CP949 등의 인코딩이면 가능하면 UTF-8로 정확히 변환한 뒤 가져오고, CHARACTER SET utf8mb4를 썼다는 이유만으로 원본 바이트가 UTF-8이 되는 것으로 생각하지 마세요.

형식·중복 검사 후 본 테이블에 반영

다음 세 조회 결과가 모두 비어 있어야 이 예제의 데이터 규칙을 충족합니다. 여기서는 sku·name이 비어 있지 않고 수량이 0~999999 범위의 1~6자리 십진수라는 규칙을 정했습니다. 음수·소수·천 단위 쉼표·앞뒤 공백은 이 예제에서 허용하지 않습니다. 잘못된 행을 발견하면 원본 또는 적재 데이터를 검토하고, 해결되기 전에는 다음 INSERT를 실행하지 마세요.

SELECT sku, name, quantity_text
FROM csv_stage_demo
WHERE sku IS NULL OR TRIM(sku) = ''
   OR name IS NULL OR TRIM(name) = ''
   OR quantity_text IS NULL
   OR quantity_text NOT REGEXP '^[0-9]{1,6}$';

SELECT sku, COUNT(*) AS duplicate_count
FROM csv_stage_demo GROUP BY sku HAVING COUNT(*) > 1;

SELECT s.sku
FROM csv_stage_demo AS s
JOIN csv_items_demo AS t ON t.sku = s.sku;

첫 조회는 누락·수량 형식을, 둘째는 파일 안 SKU 중복을, 셋째는 기존 대상 테이블과의 충돌을 확인합니다. SKU의 대소문자를 같은 값으로 볼지는 열의 정렬 규칙에 따릅니다. 같은 파일의 재실행은 “새로 추가”인지 “기존 값 수정”인지도 구분해야 합니다. 문제를 없애려고 REPLACE나 IGNORE를 일괄 추가하면 기존 값을 바꾸거나 행을 누락시킬 수 있습니다.

검사를 통과한 작은 실습 파일만 아래로 진행합니다. 이 블록은 세 행을 반영한 결과를 본 뒤 ROLLBACK으로 취소하는 사전 확인용입니다. 대규모 적재에서는 잠금·로그·복제 영향까지 별도로 계획해야 합니다.

START TRANSACTION;
INSERT INTO csv_items_demo (sku, name, quantity)
SELECT sku, name, CAST(quantity_text AS UNSIGNED)
FROM csv_stage_demo;
SELECT ROW_COUNT() AS inserted_rows;
SELECT sku, name, quantity FROM csv_items_demo ORDER BY sku;
ROLLBACK;
SELECT COUNT(*) AS remaining_rows FROM csv_items_demo;

inserted_rows=3이고 수량 10·0·12를 확인한 뒤, ROLLBACK 후 remaining_rows=0이어야 합니다. 이 예제는 InnoDB에서 일반 INSERT를 취소하는 절차이며 LOAD DATA 자체의 취소를 가정하지 않습니다. 확정하려면 충돌·검증 상태를 다시 확인하고 같은 트랜잭션의 INSERT와 결과 검토를 수행한 뒤 ROLLBACK 대신 COMMIT으로 마칩니다. 오류가 나면 다음 단계로 강행하지 말고 현재 트랜잭션을 취소합니다.

LOCAL 적재에서는 엄격 모드에 기대해 숫자 오류가 모두 중단된다고 가정하면 안 됩니다. 원시 문자열 적재와 검증을 분리한 이유입니다. 경고가 없더라도 잘못된 업무 값이 문법상 유효할 수 있으므로 행 수와 필수 값·허용 범위·중복을 함께 검토하세요. 반영을 확정한 뒤의 복구는 ROLLBACK만으로 해결되지 않으므로 운영 자료는 변경 전 백업과 적재 작업 식별 기록이 필요합니다.

파일에 일부 열만 있는 경우

이름만 있는 names.csv를 별도로 만들고 UTF-8·LF로 저장했다고 가정합니다. 내용은 아래 두 줄이며 헤더가 없습니다.

연필
공책

다음은 별도의 새 테이블에 파일 첫 필드를 name으로 지정하는 전체 예입니다. 파일에 없는 sku는 NULL, quantity는 기본값 0으로 남습니다. 열을 맞추려고 원본 각 줄 앞에 억지로 쉼표를 붙일 필요는 없습니다.

CREATE TABLE csv_name_only_demo (
    sku VARCHAR(100) NULL,
    name VARCHAR(200),
    quantity INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
LOAD DATA LOCAL INFILE 'C:/csv-import/names.csv'
INTO TABLE csv_name_only_demo
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\n'
(name);
SHOW WARNINGS;
SELECT sku, name, quantity FROM csv_name_only_demo ORDER BY name;

실제 파일의 순서가 테이블 순서와 다르면 괄호 안의 열 순서를 파일에 맞춥니다. 값을 전처리해야 한다면 열 대신 @csv_value 같은 사용자 변수로 받은 뒤 SET 절에서 대상 열에 대응시킬 수도 있지만, 잘못된 값을 변환으로 조용히 감추지 않도록 원본과 검증 규칙을 먼저 정합니다.

자주 실패하는 조건과 재시도 기준

• 파일 없음: LOCAL 여부에 따라 어느 컴퓨터의 경로인지부터 확인합니다. Windows 역슬래시·공백·파일 읽기 권한도 대조합니다.
• LOCAL 기능 거절: 서버 local_infile과 해당 클라이언트 옵션을 각각 확인합니다. 공용 서버의 정책을 우회하기 위해 FILE 권한이나 전역 허용을 무작정 요구하지 않습니다.
• 열 수·잘림 경고: 구분자·따옴표·헤더·줄바꿈·열 목록·최대 길이를 확인합니다. 한 줄이 예상과 다르게 나뉘었으면 본 테이블 반영을 멈추고 파일 규칙을 고칩니다.
• 한글 깨짐: 파일의 실제 인코딩, LOAD DATA의 CHARACTER SET, 대상 열과 클라이언트 표시 인코딩을 각각 확인합니다.
• 재실행 시 중복: 앞 시도가 일부 반영됐는지 먼저 조회합니다. 새 적재 테이블과 원본 파일을 기준으로 검토하고, 성공 여부를 모른 채 같은 파일을 반복 실행하지 않습니다.

가져오기가 끝나면 접속을 종료하고, 실습용 파일·적재 테이블·결과 테이블 중 보관할 것을 구분해 정리합니다. 자동화할 때는 경고·검증 실패에서 중단하는 분기와 파일 식별 정보·행 수 기록을 구현하고, 콘솔에서 사람이 확인한 과정을 생략한 채 전체 SQL을 그대로 일괄 실행하지 않습니다.