MariaDB 강좌 / CSV 가져오기·내보내기: 경로·권한·문자 인코딩

CSV 가져오기는 구분자·따옴표·줄바꿈·인코딩을 맞춘 뒤 임시 적재 테이블에서 검증하고 실제 타입으로 옮기는 과정입니다. LOAD DATA LOCAL INFILE은 클라이언트 파일을 읽고 SELECT INTO OUTFILE은 서버에 파일을 쓰므로 경로와 권한의 기준이 서로 다릅니다.

파일을 누가 읽고 쓰는지 먼저 구분하기

LOCAL 허용은 신뢰할 서버에 접속하는 상황에서만 사용합니다. 클라이언트가 읽어 보낼 수 있는 파일 범위를 고려해야 하며, 오류를 없애려고 모든 서버의 전역 옵션과 파일 권한을 무조건 풀지 않습니다. LOAD DATA의 LOCAL·인코딩·엄격 모드 조건을 확인하세요. SQL 파일 실행과 클라이언트 경로는 명령줄 클라이언트로 접속하고 SQL 파일 실행하기, 실제 타입 열에 행을 옮길 때의 열·값 대응은 INSERT로 한 행과 여러 행 추가하기와 같은 원칙을 적용합니다.

UTF-8 CSV와 문자형 적재 테이블 준비하기

클라이언트 PC에 C:/mariadb-lab 폴더를 만들고 아래 내용을 products.csv로 저장합니다. UTF-8을 사용하고 첫 줄은 열 이름입니다. 쉼표가 들어 있는 상품명은 큰따옴표로 감싸야 합니다. 기존 파일을 덮어쓰지 말고 새 실습 파일인지 확인합니다.

product_id,product_name,price
1,"노트, 대형",3500.00
2,"머그컵",8000.00
3,"볼펜",1500.00

shopcourse에 접속해 csv45_stage·csv45_products가 없는지 확인한 다음 아래 코드를 실행합니다. 처음부터 숫자 열로 밀어 넣지 않고 원본 문자열을 남겨 잘못된 값을 찾습니다.

USE shopcourse;
CREATE TABLE csv45_stage(raw_id TEXT,raw_name TEXT,raw_price TEXT)
 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE csv45_products(product_id INT PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,price DECIMAL(10,2) NOT NULL CHECK(price>=0))
 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

클라이언트 파일을 적재하고 경고 확인하기

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --local-infile=1 --default-character-set=utf8mb4 shopcourse

LOAD DATA LOCAL INFILE 'C:/mariadb-lab/products.csv'
INTO TABLE csv45_stage CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id,@name,@price)
SET raw_id=@id,raw_name=@name,raw_price=TRIM(TRAILING '\r' FROM @price);
SHOW WARNINGS;
SELECT * FROM csv45_stage;
SELECT * FROM csv45_stage
WHERE raw_id IS NULL OR raw_id NOT REGEXP '^[0-9]+$'
 OR raw_price IS NULL OR raw_price NOT REGEXP '^[0-9]+([.][0-9]{1,2})?$'
 OR raw_name IS NULL OR raw_name='';
SELECT CAST(raw_id AS UNSIGNED) AS normalized_id,COUNT(*)
FROM csv45_stage GROUP BY CAST(raw_id AS UNSIGNED) HAVING COUNT(*)>1;
-- 형식 오류가 없음을 확인한 뒤 범위와 길이 검사
SELECT * FROM csv45_stage
WHERE CAST(raw_id AS DECIMAL(65,0))<1
 OR CAST(raw_id AS DECIMAL(65,0))>2147483647
 OR CAST(raw_price AS DECIMAL(65,2))>99999999.99
 OR CHAR_LENGTH(raw_name)>100;

서버의 local_infile이 허용되지 않으면 관리자와 정책을 먼저 확인합니다. LOCAL 적재는 strict 모드라고 모든 잘못된 값을 오류로 거절한다고 가정할 수 없으므로 경고와 원본 문자열을 확인해야 합니다. 이 예제는 LF로 줄을 나누고 마지막 가격의 CR을 제거해 일반적인 CRLF 파일도 처리하지만, 따옴표 안의 복잡한 줄바꿈·이스케이프 규칙은 실제 파일 생산자와 맞춰 추가 검증해야 합니다.

Ubuntu 클라이언트에서는 먼저 터미널에서 작업 폴더를 준비하고 UTF-8 products.csv를 그 안에 저장합니다.

mkdir -p "$HOME/mariadb-lab"
printf '%s\n' "$HOME/mariadb-lab/products.csv"

LOAD DATA LOCAL의 경로는 위 명령이 출력한 실제 절대 경로로 바꿉니다. 예를 들어 로그인 이름이 learner인 PC의 다음 경로는 그 클라이언트 파일을 뜻합니다.

LOAD DATA LOCAL INFILE '/home/learner/mariadb-lab/products.csv'
INTO TABLE csv45_stage CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id,@name,@price)
SET raw_id=@id,raw_name=@name,raw_price=TRIM(TRAILING '\r' FROM @price);
SHOW WARNINGS;

Windows 예제와 Ubuntu 예제 중 자신의 클라이언트에 맞는 하나만 실행합니다. 같은 stage에 둘 다 적재하면 중복됩니다. 서버가 Ubuntu인 OUTFILE은 아래 경로 정책을 별도로 따릅니다.

검증이 끝난 행만 실제 타입으로 옮기기

불량 형식 조회와 중복 조회가 모두 빈 결과인지 확인합니다. ID는 INT 양수 범위, 가격은 DECIMAL(10,2)의 범위, 상품명은 100문자 이내인지도 확인하세요. 여기의 세 행은 그 범위 안입니다. 문자형 ID 01과 1은 숫자로 같아질 수 있으므로 실제 변환 후 고유성도 판단해야 합니다. 잘못된 행이 있으면 아래 INSERT를 실행하지 말고 원본·정책을 고칩니다.

INSERT INTO csv45_products(product_id,product_name,price)
SELECT CAST(raw_id AS UNSIGNED),raw_name,CAST(raw_price AS DECIMAL(10,2))
FROM csv45_stage;
SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id;

결과는 세 상품이며 첫 상품명은 쉼표를 포함한 노트, 대형입니다. LOAD를 같은 stage에 반복하면 행이 누적될 수 있으므로 단순히 다시 실행하지 않습니다. 실패한 적재를 정리할 때도 원본 파일과 필요한 오류 자료를 먼저 보관합니다.

서버 파일 내보내기와 클라이언트 출력을 구분하기

아래 코드는 FILE 권한이 있는 관리자 또는 승인된 내보내기 계정의 연결에서 실행합니다. 서버의 secure_file_priv와 실제 쓰기 권한을 확인하고 새 파일명을 사용합니다. 서버가 원격이면 C: 경로도 그 서버의 경로이지 현재 PC의 경로가 아닙니다.

SELECT @@secure_file_priv;

SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id
INTO OUTFILE 'C:/mariadb-lab/products-export.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

출력은 열 이름 없는 CSV 세 행이며 기존 파일을 덮어쓰지 않습니다. 헤더가 필요하면 별도 도구나 명시한 내보내기 절차로 추가합니다. mariadb --batch의 기본 탭 구분 출력을 확장자만 .csv로 바꾸면 CSV가 되는 것은 아닙니다. SELECT INTO OUTFILE의 위치·권한 조건을 확인하세요.

Ubuntu 서버에서 OUTFILE을 사용할 때는 SELECT @@secure_file_priv;가 반환한 허용 경로와 MariaDB 서비스 계정의 쓰기 권한을 확인합니다. 예를 들어 허용 디렉터리가 /var/lib/mysql-files/라면 새 파일명을 다음처럼 지정합니다. 이 경로는 서버의 경로이며 클라이언트 홈 폴더와 다릅니다.

SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id
INTO OUTFILE '/var/lib/mysql-files/products-export-new.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

실제 허용 경로가 다르면 그 경로로 바꾸고 해당 서버에서 한 번만 실행합니다. 기존 파일을 지우거나 FILE 권한을 넓혀 오류를 우회하지 않습니다.

원본·행 수·문자열을 대조하고 정리하기

행 수뿐 아니라 쉼표·한글·빈 문자열·NULL·줄바꿈이 같은 의미로 왕복하는지 확인합니다. CSV는 모든 도구에서 똑같은 NULL 규약을 갖지 않으므로 빈 셀과 값 없음의 정책을 정해야 합니다. 이 예제는 세 열 모두 값이 있는 간단한 교환 형식입니다. 파일은 보관 필요성을 확인해 별도로 정리하고 실습용 테이블만 삭제합니다.

DROP TABLE shopcourse.csv45_products;
DROP TABLE shopcourse.csv45_stage;