[MySQL] 우체국 우편번호 DB 파일 Table 에 저장하기

우체국에서 최신 우편번호 DB 파일을 다운로드 받으면 텍스트 형태로 되어 있습니다.

이를 mysql 에 데이터로 밀어넣는 방법입니다.

https://www.epost.go.kr/search/zipcode/areacdAddressDown.jsp
https://www.epost.go.kr/search/areacd/zipcode_DB.zip
여기에서 파일을 다운로드 받아서 압축을 풀고 서버로 업로드를 합니다.

우편번호|
시도|
시도영문|
시군구|
시군구영문|
읍면|
읍면영문|
도로명코드|
도로명|
도로명영문|
지하여부|
건물번호본번|
건물번호부번|
건물관리번호|
다량배달처명|
시군구용건물명|
법정동코드|
법정동명|
리명|
행정동명|
산여부|
지번본번|
읍면동일련번호|
지번부번|
구우편번호|
우편번호일련번호

파일을 열어서 보면 위에 같은 형태로 되어 있는데 똑같은 구조로 테이블을 만듭니다.

CREATE TABLE zipcode (
 zipcode        VARCHAR(5) NULL,
 sido           VARCHAR(25) NULL,
 sido_en        VARCHAR(20) NULL,
 sigungu        VARCHAR(30) NULL,
 sigungu_en     VARCHAR(30) NULL,
 eupmyun        VARCHAR(20) NULL,
 eupmyun_en     VARCHAR(25) NULL,
 doro_code      VARCHAR(12) NULL,
 doro           VARCHAR(40) NULL,
 doro_en        VARCHAR(50) NULL,
 under_yn       VARCHAR(1) NULL,
 buildno1       VARCHAR(5) NULL,
 buildno2       VARCHAR(4) NULL,
 buildnum       VARCHAR(25) NULL,
 multiple       VARCHAR(1) NULL,
 buildname      VARCHAR(70) NULL,
 dong_code      VARCHAR(10) NULL,
 dong           VARCHAR(20) NULL,
 ri             VARCHAR(20) NULL,
 dong_hj        VARCHAR(30) NULL,
 mount_yn       VARCHAR(1) NULL,
 jibun1         VARCHAR(4) NULL,
 eupmyundong_no VARCHAR(2) NULL,
 jibun2         VARCHAR(4) NULL,
 zipcode_old    VARCHAR(7) NULL,
 zipcode_seq    VARCHAR(3) NULL,
 idx            INT(10)    UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

이제 DB Text 파일을 ANSI 에서 UTF-8 로 변환 합니다.

iconv -c -f cp949 -t utf-8 강원도.txt > 강원도1.txt
iconv -c -f cp949 -t utf-8 경기도.txt > 경기도1.txt
iconv -c -f cp949 -t utf-8 경상남도.txt > 경상남도1.txt
iconv -c -f cp949 -t utf-8 경상북도.txt > 경상북도1.txt
iconv -c -f cp949 -t utf-8 광주광역시.txt > 광주광역시1.txt
iconv -c -f cp949 -t utf-8 대구광역시.txt > 대구광역시1.txt
iconv -c -f cp949 -t utf-8 대전광역시.txt > 대전광역시1.txt
iconv -c -f cp949 -t utf-8 부산광역시.txt > 부산광역시1.txt
iconv -c -f cp949 -t utf-8 서울특별시.txt > 서울특별시1.txt
iconv -c -f cp949 -t utf-8 세종특별자치시.txt > 세종특별자치시1.txt
iconv -c -f cp949 -t utf-8 울산광역시.txt > 울산광역시1.txt
iconv -c -f cp949 -t utf-8 인천광역시.txt > 인천광역시1.txt
iconv -c -f cp949 -t utf-8 전라남도.txt > 전라남도1.txt
iconv -c -f cp949 -t utf-8 전라북도.txt > 전라북도1.txt
iconv -c -f cp949 -t utf-8 제주특별자치도.txt > 제주특별자치도1.txt
iconv -c -f cp949 -t utf-8 충청남도.txt > 충청남도1.txt
iconv -c -f cp949 -t utf-8 충청북도.txt > 충청북도1.txt

변환된 파일을 mysql load data 를 이용해서 데이터로 밀어넣습니다.

LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/강원도1.txt'         INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/경기도1.txt'         INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/경상남도1.txt'       INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/경상북도1.txt'       INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/광주광역시1.txt'     INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/대구광역시1.txt'     INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/대전광역시1.txt'     INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/부산광역시1.txt'     INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/서울특별시1.txt'     INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/세종특별자치시1.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/울산광역시1.txt'     INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/인천광역시1.txt'     INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/전라남도1.txt'       INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/전라북도1.txt'       INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/제주특별자치도1.txt' INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/충청남도1.txt'       INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE '/home/ivps/www/zipcode/zipcode_DB/충청북도1.txt'       INTO TABLE zipcode.zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;

생각보다 빨리 처리가 되는군요~

idx 컬럼 때문에 warning 이 발생하긴 하지만 idx 컬럼이 없으면 로딩시 느려집니다.

load data 뒤쪽에 SET idx = NULL 을 넣어도 워닝은 발생하네요~

INSERT INTO TABLE (컬럼명, ...) 이렇게 지정하면 되긴 하지만 저 많은 컬럼을 다 나열하자니~ ㅎ