[MySQL] 테이블 생성 및 Text 대용량 자료 DB 업로드, character set 확인

텍스트 자료를 분석하기 위해서 MySQL DB에 저장하는 방법이다.

텍스트 자료를 DB에 저장하기 위해서는 한글의 경우 Encoding 모드(character set)가 서로 일치해야 한다.

MySQL DB 에 테이블을 생성할 때부터

업로드할 텍스트자료의 형식을 일치시켜 줘야 한다.

가장 먼저 DB character set 설정이 어떻게 되어 있는지부터 확인한다.

mysql> show variables like '%char%';  -- character set 정보 조회

character set 이 UTF-8 로 설정되어 있는 것을 확인할 수 있다.

만약 이 설정값이 latin1 으로 되어 있다면, my.cnf 에서 변경해줘야 한다.

변경을 해줬다면 데몬을 재실행하여야 한다.

character set 설정 상태를 확인했다면 이제 DB 를 생성해보자.

mysql> create database work default character set utf8 collate utf8_general_ci; 

mysql> show databases;  -- 데이터베이스 보기

DB를 생성하고 나서는
mysql> use work; 로 생성된 DB로 접속한다.
다음으로는 자료를 업로드할 테이블을 생성해야 한다.

  ※ 테이블을 생성하는 방법은 phpmyadmin 을 이용해서 생성해도 되고, 오라클 사이트에서 제공하는

      MySQL Workbench 라는 툴을 이용하거나, Toad for MySQL 을 이용해도 된다.

      테이블 생성은 보통은 EditPlus 와 같은 텍스트 툴을 이용하여 만든 다음에 복사하여 붙여넣기하면 편하다.

PC에 있는 텍스트 자료를 업로드하는 방법은

FTP 를 이용하여 서버상에 파일을 업로드한다.

만약 MySQL DB가 PC상에 설치된 DB라면 폴더명만 지정해주면 된다.

LOAD DATA INFILE '등록할 파일명'

INTO TABLE 등록될_테이블명
FIELDS TERMINATED BY '컬럼구분자'
LINES TERMINATED BY '\r\n'
 IGNORE 1 LINES
 (등록될_테이블명_컬럼1, 등록될_테이블명_컬럼2, ...);

만약 파일의 크기가 크고 테이블에 index가 걸려 있으면 시간이 굉장히 오래 걸릴 수 있다.

그럴 경우에는 아래와 같이 index를 먼저 해제해 주고 파일을 올린 다음 다시 index를 걸면 상당히 빠르게 테이블에 데이터를 넣을 수 있다.

mysql> ALTER TABLE dbName.tableName DISABLE KEYS;

읽어들일 파일명 경로를 지정하고 읽어들인다.

           ※ 경고가 발생했다. 테이블 설계시 칼럼 크기가 잘못된 것이 있다는 발견

테이블을 삭제하고 테이블을 다시 생성했다.

load data local infile '/home/test/test_text.csv'
into table `test`
fields terminated by ';'  -- 칼럼을 구분할 구분자를 지정
lines terminated by '\r\n'   -- Row(행)을 구분해줄 구분자를 지정
ignore 1 lines     -- 등록할 파일의 첫줄을 무시하고 등록하라
 (`Name` ,
  `serviceNo`  ,
  `openDate` ,
  `closeDate`
); 

    ※ -- 표시는 주석을 의미하며, 위 쿼리문을 복사해서 텍스트에 저장하고 필요한 부분만 수정 이용

그런데 이번에도 경고가 나와서 SELECT 문으로 조회를 해봤다.

앗!! Name 에 한글명이 나와야 하는데 없다.. 아뿔싸 파일명을 FTP 업로드할 때 Encoding Mode 를 ANSI 에서 UTF-8 로 변경해서 올리지 않았던 거다.

테이블을 삭제했으니 테이블 데이터만 비워야겠다.

mysql> truncate table 테이블명; -- 테이블 자체를 Drop 후 테이블 새로 생성 (DELETE 로 지운 것보다 훨씬 빠름)
mysql> truncate table test;

테이블을 비우고 나서 Encoding Mode를 UTF-8 로 변경해서 파일을 다시 업로드하고 나서 아래 명령어로 대용량 데이터를 DB로 업로드했다.

이제 정상적으로 한글명칭이 보인다. 성공

mysql> ALTER TABLE dbName.tableName ENABLE KEYS;

OPTIMIZE TABLE 테이블명 : 대량의 데이터를 삭제했던가, 테이블의 잦은 변화가 있을 경우 사용하면 유용하다.
대부분의 경우 ,OPTIMIZE TABLE 를 실행할 필요는 없다.
가변장 레코드에 갱신을 많이 실시하는 경우에서도 , 주 또는 달에 한 번 , 특정의 테이블인 만큼 실행하는 것만으로 충분하다.
삭제된 레코드는 링크된 리스트에 보관 유지되어 원의 레코드 위치는 , 후속의 INSERT 조작에 의해 재이용된다.
OPTIMIZE TABLE 을 사용해 미사용 영역을 해제해, 데이터 파일을 최적화할 수가 있다.

1.39 sec 는 최적화가 되어 있는 상태로 보면 된다.

저장할 디렉토리가 /usr/local/apache/htdocs/sql_backup/ 라고 가정하고 파일을 저장해보자.

디렉토리에는 쓰기권한이 설정되어 있어야 한다.

SELECT * INTO OUTFILE '/usr/local/apache/htdocs/sql_backup/memberfile.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM member;

로 하면 텍스트 형태의 파일로 저장된 것을 확인할 수 있다.