MySQLで住所CSVをDBテーブルにロードする

概要

住所情報をDBに入れて、検索させて候補表示したりすることはよくあると思います。

なので、その準備方法の一つとして、方法を記載しておきます。

なお、今回使うのは住所.jp様のCSVです。

項目の意味等は別途下記サイトから調べてください。

住所データCSV【住所.jp】

日本郵政にも同じようなCSVファイルがありましたが、読みが半角カナで変換が面倒だったので、 こちらを使用。

環境

Ubuntu 18.04

MySQL 14.14

テーブルを作る

下記SQLでテーブルを作成します。命名がダサいとおもった方は適宜変えてください。

 CREATE TABLE JUSYO(
        JUSYO_CD VARCHAR(10),
        TODOFUKEN_CD VARCHAR(10),
        SIKU_CD VARCHAR(10),
        CHOU_CD VARCHAR(10),
        ZIP VARCHAR(8),
        JIGYOSHO_FLG VARCHAR(1),
        HAISI_FLG VARCHAR(1),
        TODOFUKEN VARCHAR(100),
        TODOFUKEN_KANA VARCHAR(100),
        SIKU VARCHAR(100),
        SIKU_KANA VARCHAR(100),
        CHOU VARCHAR(100),
        CHOU_KANA VARCHAR(100),
        CHOU_HOSOKU VARCHAR(100),
        KYOTO_TORINA VARCHAR(100),
        AZA VARCHAR(100),
        AZA_KANA VARCHAR(100),
        HOSOKU VARCHAR(100),
        JIGYOSHO_NAME VARCHAR(100),
        JIGYOSHO_KANA VARCHAR(100),
        JIGYOSHO_JUSYO VARCHAR(100),
        NEW_JUSYO_CD VARCHAR(100)
    );

ファイルをサーバーに置く

適当な場所でよいです。

もしかしたらサーバーとDBの設定を変えれば、リモート越しでもできるかもしれません。

CSVロード

該当ファイルがある場所でmysql にログイン

↓

先ほど作ったテーブルが存在するDBを選択し、下記SQLを実行 （ファイル名は zenkoku.csv を想定）

LOAD DATA LOCAL INFILE "zenkoku.csv"
INTO TABLE JUSYO
CHARACTER SET SJIS
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"';
IGNORE 1 LINES;

参考： 【MySQL】CSVファイルをデータベースにインポートする | (株)シャルーン

エラー対処法

住所.jp様からDLできるCSVファイルはSJISなので、 DBがUTF8 かつ CHARACTER SET SJIS がない場合、下記のようなエラーで落ちる

ERROR 1300 (HY000): Invalid utf8 character string: '"' 

INTO TABLE *** の直後に CHARACTER SET SJIS をもってこないと、下記エラーになる模様

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'character SET sjis IGNORE 1 LINES' at line 1

おまけ

全部連結したテーブルを作っておけば、 サジェストなどに使いやすい形になると思います。

こんな感じのデータになります。

郵便番号	住所	住所読み
060-0042	北海道札幌市中央区大通西１丁目	ホッカイドウサッポロシチュウオウクオオドオリニシ０１チョウメ

作成したければ、下記のようなSQLで、SELECT結果からTABLEを作成することができます。

create table JUSYO_MIN
as 
SELECT 
zip, 
replace(concat(TODOFUKEN,SIKU,CHOU,AZA,JIGYOSHO_NAME,JIGYOSHO_JUSYO),"　", "") as JUSYO_ALL, 
replace(concat(TODOFUKEN_KANA,SIKU_KANA,CHOU_KANA,AZA_KANA,JIGYOSHO_KANA), "　", "") as JUSYO_KANA_ALL 
FROM JUSYO;