[MySQL] csv 파일을 직접 테이블로 Import

csv 파일을 직접 테이블로 Import

-- LOAD DATA INFILE statement

LOAD DATA [LOCAL] INFILE 'file_name' 
  INTO TABLE tbl_name
  [CHARACTER SET charset_name]
  [{FIELDS | COLUMNS}
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
  ]
  [LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]

※ 주의점

CSV File 를 읽어 이를 처리 하는 방버은 매우 빠른 속도를 제공합니다.

그러나 데이터 등록시 해당 테이블에는 lock이 걸릴 수 있습니다.

처리 하려는 CSV 파일의 데이터가 정확해야 합니다. 

sample 1. 테이블 구조와 CSV file 구조가 동일 할때

-- table 구조
CREATE TABLE emp (
    id INT NOT NULL AUTO_INCREMENT,
    emp_name VARCHAR(255) NOT NULL,
    salary DECIMAL(10 , 2 ) NULL,
    hire_date DATE NOT NULL,
    PRIMARY KEY (id)
);

/* csv file format */
id,emp_name,salary,hire_date
1,"철수",1000.00,2017-01-01
2,"영희",500.10,2017-02-01
3,"길동",100.00,2017-03-01

별도의 옵션없이 사용하면 됩니다.

LOAD DATA INFILE '/tmp/emp.csv' 
INTO TABLE emp
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

sample 2. 테이블 구조와 CSV file 구조가 다르거나 포멧이 다른 경우

-- table 구조
CREATE TABLE emp (
    id INT NOT NULL AUTO_INCREMENT,
    emp_name VARCHAR(255) NOT NULL,
    salary DECIMAL(10 , 2 ) NULL,
    hire_date DATE NOT NULL,
    PRIMARY KEY (id)
);

/* csv file format */
id,emp_name,salary,bonuns,fire_date, hire_date
1,"철수",1000.00,300,01/01/2017
2,"영희",500.10,300,01/02/2017
3,"길동",100.00,400,01/03/2017

테이블의 column과 포멧을 맞춰 줍니다. 

LOAD DATA INFILE '/tmp/emp.csv'
INTO TABLE emp
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@id,@emp_name,@salary, @bonuns,@hire_date)
SET id = @id,
    emp_name = @emp_name,
    salary = @salary,
    hire_date = STR_TO_DATE(@hire_date, '%m/%d/%Y');

sample 3. DB Server내에 있는 파일이 아니고, 클라이언트에 있는 CSV file 을 업로드 하고자 하는 경우 

-- table 구조
CREATE TABLE emp (
    id INT NOT NULL AUTO_INCREMENT,
    emp_name VARCHAR(255) NOT NULL,
    salary DECIMAL(10 , 2 ) NULL,
    hire_date DATE NOT NULL,
    PRIMARY KEY (id)
);

/* csv file format */
id,emp_name,salary,bonuns,fire_date, hire_date
1,"철수",1000.00,300,01/01/2017
2,"영희",500.10,300,01/02/2017
3,"길동",100.00,400,01/03/2017

LOAD DATA INFILE 명령이 아닌 LOAD DATA LOCAL INFILE 을 사용합니다. 

나머지 명령은 동일합니다.

LOAD DATA LOCAL INFILE 'd:\\temp\\emp1.csv' 
INTO TABLE emp
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@id,@emp_name,@salary, @bonuns,@hire_date)
SET id = @id,
    emp_name = @emp_name,
    salary = @salary,
    hire_date = STR_TO_DATE(@hire_date, '%m/%d/%Y');

단, 이 경우는  클라이언트에 있는 파일에 대한 읽기 권한이 있어야 합니다. 실행 속도 역시 느립니다.  

서버에서 클라이언트의 파일을 읽어 처리 하므로 권장하지 않습니다.

MariaDB 최신 버전에서는  LOAD DATA LOCAL INFILE 명령어에 대해서 처리 규칙이 엄격해 진 것으로 알고 있습니다.  

---