MySQLで郵便番号を保持するスキーマ

概要

郵便番号から住所を探して入力フォームに補完したいという要望があり、避けては通れなくはなってしまった。自社で郵便番号の最新情報を持つ。

なんといっても、面倒なのは月に1回、100件ほどの更新がある郵便番号データ。データ自体は郵便局が提供しています。

設計

運用は以下のようにする。
・月1回丸ごとデータを入れ替える
　・時間はかかるけど、差分を積み重ねたときの不整合が出てしまうリスクを回避
　・更新用のスクリプトを2つ書かなくてすむ。
・入れ替える際に、SQLクライアントから参照不可能になっては困るので、トランザクションをかけてデータを更新。
　・重いけど、月1回だし、ロジックが楽。
・マスタにあって利用しない情報も一応保管しておく。

ビジネスロジックでは、郵便番号から住所を検索する用途にしか利用しないため、indexは郵便番号だけ。
スキーマは以下の通り。

CREATE TABLE `postcode` (
  `postcode_id` int(10) unsigned NOT NULL auto_increment,
  `postcode_jis` varchar(5) NOT NULL default '',
  `postcode_old` varchar(5) NOT NULL default '',
  `postcode_new` varchar(7) NOT NULL default '',
  `postcode_prefecture_kana` varchar(255) NOT NULL default '',
  `postcode_city_kana` varchar(255) NOT NULL default '',
  `postcode_suburb_kana` varchar(255) default NULL,
  `postcode_prefecture` varchar(255) NOT NULL default '',
  `postcode_city` varchar(255) NOT NULL default '',
  `postcode_suburb` varchar(255) default NULL,
  `postcode_is_separated_suburb` tinyint(3) unsigned NOT NULL default '0',
  `postcode_is_koaza` tinyint(3) unsigned NOT NULL default '0',
  `postcode_is_chome` tinyint(3) unsigned NOT NULL default '0',
  `postcode_is_include_area` tinyint(3) unsigned NOT NULL default '0',
  `postcode_status` tinyint(3) unsigned NOT NULL default '0',
  `postcode_reason` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`postcode_id`),
  KEY `postcode_new` (`postcode_new`(4))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

データをつっこむスクリプト要点(日本語のところは適宜置き換えてね)
トランザクションかけているので途中でデータの挿入に失敗しても、中途半端にならないはず。

ini_set('max_execution_time',600);

$handle = fopen(ふぁいる, 'r');

とらんざくしょんはじめ。

れこーどをけす (truncate table postcode)

while (!feof($handle)) {

$line = fgets($handle, 8192);
$line = trim($line);

if(!$line) continue;

// 文字列の処理
$line = str_replace('以下に掲載がない場合', '',$line);

$field_array = explode(',', $line);

// 文字列の処理
foreach($field_array as &$field){
$field = str_replace('"','',$field);
}

// 補足として書いてある括弧を消す
$field_array[5] = mb_ereg_replace('(\([^\)]+\))','',$field_array[5]);
$field_array[8] = mb_ereg_replace('(（[^）]+）)','',$field_array[8]);

えんてぃてぃ作る

てーぶるに いんさーと

}
fclose($handle);

とらんざくしょんこみっと。

あとは、月1回全国のデータをダウンロードして、上記のスクリプトを実行すればダウンタイムなしに、
しかも楽に更新ができる。

余裕があれば、
・上記のURLから自動でファイルをダウンロード
・解凍
・プログラム実行