[MySQL] 住所の文字列検索をサブクエリを使って実装する

Read More

MySQLで住所の文字列検索を実装する方法について。



やりたいことは画面から住所の文字列の一部を入力し、
DBの住所テーブルに検索をかけて該当するレコードを得たい。

この時DBのテーブルは住所コードでデータを持っているため途中で住所の文字列とコードの変換が必要となる。
ここがちょっとかったるい。

ここではサブクエリを使って 住所文字列⇔住所コード の違いを解消する。



--
想定している前提条件は以下のような感じ。

動作の概要。
1.Google先生の検索窓のようなものから住所の一部の文字列を入力して検索ボタンを押す。
2.ユーザテーブルが持つ住所情報に一致するものがあればそのユーザのレコードを一覧に表示させる。
3.ユーザテーブルには住所コード(一部文字列を含む)で格納されており、住所マスタは別に持っている。



住所マスタは以下のような構成。
CREATE TABLE `m_address` (
  `pref_cd` int(11) NOT NULL COMMENT '都道府県コード',
  `city_cd` int(11) NOT NULL COMMENT '市区群コード',
  `town_cd` int(11) NOT NULL COMMENT '町村コード',
  `choume_cd` int(11) NOT NULL COMMENT '丁目コード',
  `pref_name` varchar(255) NOT NULL DEFAULT '' COMMENT '都道府県名',
  `city_name` varchar(255) NOT NULL DEFAULT '' COMMENT '市区群名',
  `town_name` varchar(255) NOT NULL DEFAULT '' COMMENT '町村名',
  `choume_name` varchar(255) NOT NULL DEFAULT '' COMMENT '丁目名',
PRIMARY KEY (`pref_cd`, `city_cd`, `town_cd`, `choume_cd`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

この住所マスタは国土地理協会(有料)のデータをぶち込んでいるイメージ。
上記は説明のためのもので実際に用意する場合は、
master_id, parent_id, parent_flg, post_cd, status, kind, create_date, update_date
といったカラムを別途追加する必要がある。


ユーザテーブルは以下のような構成。
CREATE TABLE `t_user` ( 
  `id` INT NOT NULL AUTO_INCREMENT COMMENT 'id' , 
  `user_name` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'ユーザ名' , 
  `pref_cd` INT NOT NULL COMMENT '都道府県コード' , 
  `city_cd` INT NOT NULL COMMENT '市区群コード' , 
  `town_cd` INT NOT NULL COMMENT '町村コード' , 
  `choume_cd` INT NOT NULL COMMENT '丁目コード' , 
  `banchi_txt` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '番地' , 
  `gou_txt` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '号' , 
PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARSET=utf8;

こちらも必要最小限の構成とした。


用意したテーブルに適当にデータを流し込む。
INSERT INTO `m_address` (`pref_cd`, `city_cd`, `town_cd`, `choume_cd`, `pref_name`, `city_name`, `town_name`, `choume_name`) VALUES
(13, 107, 1, 1, '東京都', '墨田区', '吾妻橋', '1丁目'),
(13, 105, 1, 1, '東京都', '文京区', '大塚', '1丁目'),
(13, 108, 1, 1, '東京都', '江東区', '有明', '1丁目'),
(13, 109, 1, 1, '東京都', '品川区', '荏原', '1丁目');

INSERT INTO `t_user` (`id`, `user_name`, `pref_cd`, `city_cd`, `town_cd`, `choume_cd`, `banchi_txt`, `gou_txt`) VALUES
(1, '田中一郎', 13, 105, 1, 1, '10', ''),
(2, '鈴木次郎', 13, 107, 1, 1, '2', '3-4'),
(3, '山田三郎', 13, 108, 1, 1, '5', '7'),
(4, '山本四郎', 13, 108, 2, 1, '1', '1');


ユーザテーブルの情報を画面に表示させるSQLの例。
これは単に表示させているだけで住所の検索機能はまだ組み込まれていない。
SELECT 
  t_user.id,
  t_user.user_name,
  CONCAT(
    m_address.pref_name,
    m_address.city_name,
    m_address.town_name,
    m_address.choume_name,
    t_user.banchi_txt,
    CASE WHEN t_user.banchi_txt != '' AND t_user.gou_txt != '' THEN '-' END,
    t_user.gou_txt
  ) AS full_address
FROM
  t_user
  LEFT JOIN m_address ON m_address.pref_cd = t_user.pref_cd 
  AND m_address.city_cd = t_user.city_cd
  AND m_address.town_cd = t_user.town_cd 
  AND m_address.choume_cd = t_user.choume_cd


以下のようなデータが取れる。
+---------------------------------+
 1 田中一郎 東京都文京区大塚1丁目10
 2 鈴木次郎 東京都台東区北上野1丁目2-3-4
 3 山田三郎 東京都江東区有明1丁目5-7
 4 山本四郎 NULL
+---------------------------------+




ようやく本題。
今度はこれをサブクエリにして住所検索を行う。
SELECT
  *
FROM (
  SELECT
    t_user.id,
    t_user.user_name,
    CONCAT(
      m_address.pref_name,
      m_address.city_name,
      m_address.town_name,
      m_address.choume_name,
      t_user.banchi_txt,
      CASE WHEN t_user.banchi_txt != '' AND t_user.gou_txt != '' THEN '-' END,
      t_user.gou_txt
    ) AS full_address
  FROM 
    t_user
    LEFT JOIN m_address ON m_address.pref_cd = t_user.pref_cd 
    AND m_address.city_cd = t_user.city_cd
    AND m_address.town_cd = t_user.town_cd
    AND m_address.choume_cd = t_user.choume_cd
) AS TEMP1
WHERE full_address LIKE '%文京区%'



結果、文京区のものだけ取得できた。
+---------------------------------+
 1 田中一郎 東京都文京区大塚1丁目10
+---------------------------------+


内側のサブクエリであるSELECT文から見ていくと、
ここではWHERE句での絞り込みは行っておらず、
単にFROM句でt_userとm_addressの紐づけのみを行っている。

次のクエリのFROM句では先ほどのサブクエリを指定。
ここのWHERE句でfull_addressに対して住所検索の条件を指定している。



--
この住所の文字列検索のメリットはどんなワードでも引っ張ってこれる点。
例えば"有明1丁目"とか"1丁目10"とかでもレコードを引っ張ってこれる。
使う側からすればGoogleライクで非常に使いやすいと思う。

デメリットは文字列の検索なのでDBに対しての負荷が高く速度は遅い


また実際に運用をするとt_userの数が多くなればなるほど負荷が跳ね上がっていくことが予想される。
利用頻度やレコード数に応じて何かしら対策をしておきたいところ。


t_userにカラムのfull_addressを作っておきt_userに住所を登録する段階でそちらに文字列を入れるなんてのはどうだろう。
住所検索の際のDBへの問い合わせ数が減るので負荷と速度の面でメリットが見込める。
ただしこれは保守がめんどそう。
t_userの住所を変更する度にfull_addressに住所を入れなおす必要があるし、
m_addressを更新する場合もt_user.full_addressも合わせてupdateしなくてはならない。


他には住所の文字列検索の条件を少し引き上げ単語のみ許可をする形にするとか。
例えば入力欄から検索できる文字は
 ○ "東京都", "文京区", "大塚"
 × "東", "都文京", "区大"
という運用ルールにしておく。
ここでも住所を登録するタイミングでt_user.full_addressに住所の文字列を入れることを想定しているけど
入れる住所は単語単位でスペース区切りを使うようにしておく。
そしてfull_addressのカラムにFULLTEXTのインデックスを張る。
インデックスによるDBの肥大化が懸念点されるしユーザの使い勝手にも若干制限がかかるけど負荷や速度面ではかなり改善はみられる、と思う。


まあ使っているMySQLのVerによっては他にも方法があるみたいなのでその辺は適当に。
本題はFROM句に(あるいはWEHRE句に)サブクエリを使うってとこなので。



--
と、ここまで挙げたものはあくまで住所の文字列検索をすることを前提の話。
文字列での検索ではなく住所をselectboxで選択させるような検索方法、
あるいは住所文字列のリンクを踏んでいくタイプのものならば住所コードが渡せるのでこんなものは一切必要ない。

まあGoogle先生みたいな便利な検索を簡単に実装しようというのがそもそも間違い
あっちはあれで世界有数の企業としてやっているわけだから。



総括。
住所の文字列検索を実装しようと考えたらそれなりに面倒。




--
テンポラリテーブルとか作っちゃうとやっぱ遅くなるのかなあ。
試してないけどちょっと気になる。







コメント投稿

名前 *
(10文字以内)
コメント *
(1000文字以内)