[mysql] 텍스트파일의 데이터를 특정 테이블에 저장하기

### 텍스트파일의 데이터를 특정 테이블에 저장하기

 

1) 텍스트파일의 데이터를 특정 테이블에 저장하기 - mysqlimport

MYSQL의 mysqlimport유틸리티를 이용하면 리눅스 쉘상태에서 한번의 명령으로 특정 텍스트 파일의 전체내용을 MYSQL의 특정 데이터베이스의 특정 테이블에 입력을 할 수가 있다.

이 방법은 텍스트파일에 저장되어 있는 크기가 작은 전체 데이터들을 특정 데이터베이스에 편리하게 입력하기 위한 방법으로 주로 사용되고 있다.

아래는 mysqlimport를 사용하는 형식이다.

 

사용 형식 : mysqlimport -u MYSQL사용자 -p 데이터베이스명 텍스트파일명

 

위의 형식과 같이 실행하면 "텍스트파일명"에 저장되어 있는 데이터가 "데이터베이스명"의 특정 "테이블"의 데이터로 입력된다.

그런데 위의 형식에서 한가지 이상한 점이 있을 것이다. 즉, 텍스트파일의 데이터가 입력될 데이터베이스명은 지정이 되어 있는데 실제로 저장될 테이블명이 지정되어 있지 않다는 것이다.

위의 형식에서 데이터가 저장될 테이블이름은 "텍스트파일명"과 동일한 테이블이 된다. 즉, mysqlimport를 이용하여 텍스트파일의 데이터를 특정 데이터베이스의 테이블에 저장할 때에는 "텍스트파일명"과 "테이블명"이 동일해야 한다. 그리고 저장될 테이블은 이미 생성되어 있어야 한다.

 

이제 앞에서 설명한 mysqlimport를 이용하여 특정 텍스트파일의 내용을 특정 데이터베이스의 테이블로 입력해 보도록 하겠다.

아래는 이번 예를 설명하기 위한 간단한 텍스트파일이다. 텍스트 파일의 이름은 addressbook이다. (여기서 주의할 것은 파일 위치이다. mysqlimport에서 텍스트파일에 경로설정이 없으면 아래 위치에 있어야 함. /data/'DB명') 이 텍스트파일의 내용을 cat 명령어로 확인한 것이다.

[root@file bin]# pwd /usr/local/mysql/data/temp_db [root@file bin]# [root@file bin]# cat addressbook KIMJINSOO          011-111-1111    SEOUL   A+ KIMDONGMIN       011-111-2222    BUSAN   B+ KIMYOUNGHO      011-111-3333    SEOUL   C+ KIMMINJA            011-111-4444    SEOUL   D+ [root@file bin]#

 

텍스트파일을 생성할 때에는 가능한 TAB키를 이용하여 각 필드사이를 띄우시기 바란다. 물론 TAB키를 이용하지 않고 띄워도 입력은 되지만 아무런 에러없이 정확한 입력을 하려면 TAB키를 이용하여 각 필드데이터 사이를 띄우는 것이 좋다.

 

그리고 다음은 addressbook이라는 텍스트파일의 내용을 temp_db라는 데이터베이스의 addressbook이라는 테이블로 입력을 하기 위하여 mysqlimport를 사용한 것이다.

[root@file bin]#pwd /usr/local/mysql/bin [root@file bin]# ./mysqlimport -u root -p temp_db addressbook Enter password: temp_db.addressbook: Records: 4  Deleted: 0  Skipped: 0  Warnings: 0 [root@file bin]#

 

위의 mysqlimport명령문의 사용결과를 보면 "temp_db.addressbook: Records: 4  Deleted: 0  Skipped: 0  Warnings: 0" 과 같다. 이는 temp_db라는 데이터베이스의 addressbook이라는 테이블에 4개의 Record가 저장 되었으며 입력되지 않은 데이터 없이 모두 정상적으로 입력이 되었다는 것을 알수 있다. 만약, 텍스트파일이 확장자를 포함할 때(addressbook.txt)에는 텍스트파일의 확장자를 제외한 앞부분이 저장될 테이블이름이 된다.

 

그리고 다음은 위와 같이 mysqlimport를 이용하여 addressbook텍스트파일의 전체 내용을 temp_db데이터베이스의 addressbook테이블에 저장하였으므로 실제로 입력이 되어있는가를 확인하기 위하여 MYSQL로 접속하여 확인한 것이다.

[root@file bin]# pwd /usr/local/mysql/bin [root@file bin]# ./mysql -u root -p temp_db Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 1205 to server version: 4.1.19-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select * from addressbook; +------------+--------------+---------+-------+ | name          | tel                | address | grade | +------------+--------------+---------+-------+ | KIMJINSOO  | 011-111-1111 | SEOUL   | A+    | | KIMDONGMIN | 011-111-2222 | BUSAN   | B+    | | KIMYOUNGHO | 011-111-3333 | SEOUL   | C+    | | KIMMINJA   | 011-111-4444 | SEOUL   | D+    | +------------+--------------+---------+-------+ 4 rows in set (0.00 sec) mysql> mysql> exit Bye [root@file bin]#

 

위의 예를 보면 temp_db라는 데이터베이스로 접속하여 "select * from addressbook"이라는 SQL문을 이용하여 입력된 4개의 레코드값들을 확인한 것이다. 텍스트 파일의 내용과 동일한 데이터들이 모두 정상적으로 저장되어 있다는 것을 확인할 수 있다.

 

 

2) 텍스트파일의 데이터를 특정 테이블에 저장하기 - load data local infile문

다른 방법으로 load data local infile문이 있으며, 아래 사항과 같이 실행하면 된다.

[root@file bin]# mysql -u root -p temp_db Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 1287 to server version: 4.1.19-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> load data local infile "addressbook" into table addressbook; Query OK, 4 rows affected (0.00 sec) Records: 4  Deleted: 0  Skipped: 0  Warnings: 0 mysql> mysql> exit Bye [root@file bin]#

 

여기에서도 테이블에 넣을 텍스트파일의 위치는 1)번 방법과 같이 아래 위치에 있어야 한다. (별도 경로 설정이 없을 경우)

[root@file bin]# pwd /usr/local/mysql/data/temp_db [root@file bin]# [root@file bin]# cat addressbook KIMJINSOO          011-111-1111    SEOUL   A+ KIMDONGMIN      011-111-2222    BUSAN   B+ KIMYOUNGHO      011-111-3333    SEOUL   C+ KIMMINJA            011-111-4444    SEOUL   D+ [root@file bin]#

 

위 명령어에 대한 달리 표현해서 실행시킬 수 도 있다.

1번) mysql> LOAD DATA local infile "/usr/local/backup.txt" into table TEST 2번) mysql> LOAD DATA local infile "[파일명]" into table [테이블] FILEDS TERMINATED BY "[구분자]"

 

1번은 파일의 경로을 설정한 경우로 /usr/local/backup.txt란 파일을 TEST란 테이블에 삽입하는 명령이다.
2번은 fileds terminated by "[구분자]"처럼 사용하면 해당 구분자로 구분되어 삽입된다.

 

가. 에러 발생 경우 1

위 명령어를 실행했는데, 아래와 같은 에러가 나면 MySQL 버전 문제가 아니라 INFILE 문제이다.

mysql> load data local infile "addressbook" into table addressbook; ERROR 1148 (42000): The used command is not allowed with this MySQL version

 

이럴때에는 local_infile 변수가 사용가능한지 확인을 하고,

mysql> show variables like 'local%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | local_infile       | ON    | +---------------+-------+ 1 row in set (0.00 sec) mysql>

 

접속시 아래의 붉은 글씨부분의 옵션을 추가로 넣어 접속하면 된다. 

mysql -u아이디 -p --local-infile=1

 

나. 에러 발생 경우 2

데이터들이 전부 들어가지 않고 skip되는 경우가 발생할 수 있는데, 해결책은 필드명도 함께 적어주면 안전하게 들어 간다. 데이터 파일에 해당 필드값이 없는경우에 테이블 필드명을 함께 적어주면 해결이 된다.

 

기존방식 mysql> load data local  infile "zipcode.txt" into table zipcode; 변경방식 mysql> load data local infile "zipcode.txt" into table zipcode(ZIPCODE,SIDO,GUGUN,DONG,RI,BUNJI);