우체국에서 최신 우편번호 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 (컬럼명, …) 이렇게 지정하면 되긴 하지만 저 많은 컬럼을 다 나열하자니~ ㅎ
이 글은 ivps.tistory.com 에서 RSS로 가져온 글입니다. 원본보기