郵便番号検索を作る─5.住所検索できるようにする(前編)

今回からやっと住所による郵便番号検索を実装してみる。住所を入力して検索っていうようにしないと使いづらいしね。

■作成手順
・郵便番号データを作成
・郵便番号の全文一致でリストを出力
・郵便番号の前方一致でリストを出力
・都道府県、市区町村によるリストを作成
・住所(漢字、カナ、かな)で検索できるように←今回ココ
・郵便番号データに知人／友人を登録できるようにする
・レイアウトをカスタマイズする

やったこと自体は大したことないんだけど、結構長くなるから２回に分けて記述していきます。今回は作り方の方針を決めたりテーブルを作成したりするだけ。

どうやって実装するか

住所みたいに文字列での検索を実装する際は、likeでの部分一致で検索していくことになるわけだけど、MysqlではMyISAM+Sennaとか使わない限りはインデックスが働かない(Mysql5.6.3ではinnoDB全文検索ができるようになるらしいけど、そっちも日本語はまだ対応してないっぽいし)。

というわけで、SH2さんが書いてる転置インデックスのやり方で検索用テーブルを作って無理矢理インデックスを適応させてみる。ついでにそれをcakePHP内でjoinしてみることにする。
このやり方はInsertやUpdateする場合は向かないけど、これ以上書き換える必要のないテーブルなら問題なさげ。

あと今のテーブル構成だと検索しずらいから、住所フィールドを全部ひとつにまとめたaddressフィールドを作成する。

というわけで、Mysqlの操作

#addressフィールドを作る
mysql> alter table postal_codes add address varchar(100) collate utf8_bin not null default '';
Query OK, 124650 rows affected (12.32 sec)
Records: 124650  Duplicates: 0  Warnings: 0

#データを入れる
mysql> update postal_codes set address = concat(state , city , street , state_kana , city_kana , street_kana);
Query OK, 0 rows affected (5.36 sec)
Rows matched: 124650  Changed: 0  Warnings: 0

#転置インデックス用のテーブルを作成
mysql> create table if not exists postal_tips (
    -> area varchar(2) collate utf8_bin not null default '',
    -> postal_code_id int(11) unsigned not null ,
    -> primary key (area , postal_code_id)
    -> )Engine=Innodb default character set utf8 collate utf8_bin;
Query OK, 0 rows affected (0.41 sec)

解説:外部キーの命名規約】

なお、このpostal_code_idというのが命名規約にそった外部キーの書き方になるみたい。

【外部キー】joinテーブルの名前(アンダースコア型)_id

この命名規約に沿ってると、そのモデルで$hasMany、$belongsTo、$hasOneなどを最初からセットしておく際、外部キーをわざわざ指定しなくてもJOINしてくれるようになる。
(上記のが機能するのはあくまでモデルに最初から変数としてセットした場合か、bindModelを適応した時だけみたい。詳しいことはhasManyなどをまとめる時に書きます。とりあえず、わかりやすいから命名規約には沿って作った方がいいよね)

データを挿入していく

どうやって入れようか迷ったけど、すんごい時間がかかることを覚悟でストアドプロシージャを使って入れていくことにした。
どうせ１回だけだしね。
なお、蛇足ですがinnoDBの場合auto_incrementみたいに順繰りに繰り上がっていく数値以外を主キーにした場合、大量に挿入するとデータが断片化されてクラスタ化が機能しない。
だから、こういう形のテーブルで大量に挿入したら最後Optimize Tableで最適化しないといけない。

#ストアドプロシージャを作成
mysql> delimiter //
mysql> create procedure func()
    -> begin
    ->    declare textlength int;
    ->    declare ins_id int;
    ->    set @now = 1;
    ->    select max(char_length(address)) into textlength from postal_codes;
    ->    
    ->    while textlength > @now do
    ->       insert ignore into postal_tips (area , postal_code_id) 
    ->       select substring(address , @now , 2) as area , id from postal_codes where char_length(address) >= @now;
    ->       set @now = @now + 1;
    ->    end while;
    -> end;
    -> //
Query OK, 0 rows affected (0.07 sec)
mysql> delimiter ;
#呼び出す
mysql> call func();
.
.
.
Query OK, 0 rows affected (47min 36.36 sec)
#この部分保存しわすれてたから少し表記が違うかも。。ただうんこなくらい時間かかった。
#どれくらい挿入されたか確認
mysql> select count(*) as cnt from postal_tips;
+---------+
| cnt     |
+---------+
| 3570206 |
+---------+
1 row in set (3.64 sec)

#もういらないから消す
mysql> drop procedure func;
Query OK, 0 rows affected (0.05 sec)

#テーブルの最適化
mysql> optimize table postal_tips;
------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                          |
+------------------+----------+----------+-------------------------------------------------------------------+
| cake.postal_tips | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| cake.postal_tips | optimize | status   | OK                                                                |
+------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (18 min 45.28 sec)

PostalTipモデルを作成

単に作成しておくだけで、今回はこっちでは特に何も設定はしない。

//app/Model/PostalTip.php
<?php
    class PostalTip extends AppModel{
    }
?>

とりあえず、今回は時間がかかりすぎて心折れたからここまでにしました。
次回からcakePHPをもうちょっといじって実装していきます。

【参考リンク】
SH2の日記─MySQL InnoDBだけで全文検索

CakePHP2.1初心者メモ

2012年6月12日火曜日