概要
住所情報を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の設定を変えれば、リモート越しでもできるかもしれません。
該当ファイルがある場所で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;
