MySQL 5.7 の全文検索で不正確な郵便番号に立ち向かう

[OPENLOGI Advent Calendar 2017- Qiita](https://qiita.com/advent-calendar/2017/openlogi) の6日目の記事です。

「物流の未来を、動かす。」をミッションに、日夜物流の課題に取り組んでいる[OPENLOGI](https://www.openlogi.com/corp/index.html)のエンジニアの@hmarui66です。
今回は皆さんにも身近な存在である「郵便番号」を取り上げようと思います。

OPENLOGIが取り組んでいる事業の一つである物流代行サービスでは、ECなどで販売された商品を購入者に向けて宅配便で発送する業務がおこなわれています。郵便番号はこの宅配において重要な役割を果たしている存在です。

## 郵便番号の役割

[Wikipedia: 郵便番号](https://ja.wikipedia.org/wiki/%E9%83%B5%E4%BE%BF%E7%95%AA%E5%8F%B7#.E6.97.A5.E6.9C.AC)に詳しい説明が載っていますので、ここでは軽く触れるだけにします。

郵便番号は郵便物を配送先の地域ごとに分類するために使われており、配送の効率を上げるために役立てられています。番号を割り振っているのは郵便局ですが、その他の配送業者でも広く利用されています。
日本においては1968年に3桁もしくは5桁の番号が導入され、その後1998年に現在使われている7桁の番号に変更されました。
それ以降は大きな変更はありませんが、郵便局の統廃合や受け持ち地域の変更などにより定期的に更新され、[公開](http://www.post.japanpost.jp/zipcode/download.html)されています。

## 郵便番号に関わる問題

「郵便番号に関わる問題」といってピンと来る人は少なさそうですが、日々数多くの発送業務を行っているECなどの小売業、物流の現場、また住所入力フォームのあるWEBサービスを提供している人には割と通じる話だと思います。以下に自分が感じている問題点をいくつか挙げます。

1. 宅配業者によっては不正な番号として扱われる郵便番号が存在する
    - 郵便局とその他の宅配業者の事業所のカバー地域のズレから発生
1. 郵便番号が更新されたことに気づかず古い番号を使い続けてしまう
    - 知り合いや取引先の郵便番号が更新されたことを把握するのは難しい
    - 自宅の郵便番号が変わる場合はさすがに気づくかもしれないが、忘れて古い番号を使うことはありそう
1. 郵便番号の更新がされると業務システムが保持するマスタデータのメンテナンスも必要になる

これらのうち3つ目についてはシステマティックに対応可能で、例えば郵便番号から住所を自動補完するのに便利なライブラリである[yubinbango](https://github.com/yubinbango/yubinbango)は自動的な更新の仕組みを構築しています。

ただ、古くなったり宅配業者が対応していない郵便番号について、バリデーションで不正と判定すると販売機会を失う可能性のあるECサイト（実際そんなに気にしてないかな...）や、顧客住所情報を更新する作業やフローが煩雑である事業者、そしてそれらの人々から商品の発送・配送を委託されている業者が、手修正せざるを得ない状況はなかなか解消しなさそうです。

## ではどうすればよいのか

（前置きが長くなりましたが、ここから強引に記事のタイトルに結びつけます）
発送が決まっている宛先の郵便番号が不正確であることが発覚した時、我々は一体どんな手を打てるのか...
こんな状況では住所情報を元に正しいと思われる郵便番号を調べるしか手はありません。
調査にあたって一番頼りになるのはやっぱりGoogleですが、自社サービス上でのサポート機能に結びつけることを念頭におき何か対応できないかを考えたいところです。

ということで、以前から気になっていたMySQL 5.7の全文検索機能を試してみることにしました。
郵便番号と住所のデータは郵便局が公開しているので、それを取り込んで検索結果を見てみようと思います。

## MySQL 5.7の準備

Dockerのmysql:5.7を利用します。ただし、そのままでは形態素解析エンジンのmecabが入っていないため以下のようなDockerfileを作成します。

```Dockerfile
FROM mysql:5.7

RUN apt-get update && apt-get install -y mecab libmecab-dev mecab-ipadic-utf8 && \
echo "[mysqld]" >> /etc/mysql/my.cnf && \
echo "character-set-server=utf8" >> /etc/mysql/my.cnf && \
echo "collation-server=utf8_general_ci" >> /etc/mysql/my.cnf && \
echo "plugin-load=mecab=libpluginmecab.so" >> /etc/mysql/my.cnf && \
echo "loose-mecab-rc-file=/etc/mecabrc" >> /etc/mysql/my.cnf && \
echo "innodb_ft_min_token_size=2" >> /etc/mysql/my.cnf
```
一番下の行の`innodb_ft_min_token_size=2`はインデックスがつけられる単語の最小桁数で、デフォルトの3だと住所の解析には長すぎるのため指定しています。

[ [参考] MySQL 5.6リファレンスマニュアル > 12.9.6 MySQL の全文検索の微調整](https://dev.mysql.com/doc/refman/5.6/ja/fulltext-fine-tuning.html)

また、adminerでデータを確認するためdocker-composeを利用して立ち上げます

```yaml
version: '2'

services:

  db:
    build: .
    restart: always
    ports:
      - "3306:3306"
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
      TZ: "Asia/Tokyo"

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080
```

`docker-compose up -d`で起動して準備完了です。

http://localhost:8080
に接続するとadminerの画面をみることができます。

## Databaseの準備

adminerのログイン画面に`Username: root`を入力してログイン（パスワードは空でOK）、遷移した画面で"Create database"をクリックしてDatabaseを作成します。すると作成されたDatabaseが選択された状態となりますのでそのままテーブルを作成します。
左側のメニューの"SQL command"をクリックして以下のSQLを実行します。

```sql
CREATE TABLE postcodes (
  FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  postcode VARCHAR(7),
  address VARCHAR(100),
  address_roma VARCHAR(100),
  FULLTEXT (address, address_roma) WITH PARSER mecab
) ENGINE=InnoDB CHARACTER SET utf8;
```

`FULLTEXT (address, address_roma) WITH PARSER mecab` と指定することで、該当のフィールドがmecabで解析され全文検索対象となります。
`id`フィールドの代わりに`FTS_DOC_ID`を定義しているのは、この[リファレンス](https://dev.mysql.com/doc/refman/5.6/ja/innodb-fulltext-index.html)を参考にしてます。

## データの準備

郵便番号データは郵便局が公開しているものを使います。
http://www.post.japanpost.jp/zipcode/download.html
いくつかの種類のファイルがありますが、今回は[住所の郵便番号（ローマ字）](http://www.post.japanpost.jp/zipcode/dl/roman-zip.html)の「全国一括.zip」を利用します。

ダウンロードしたデータをmysqに流し込むために少し加工します。

```bash
iconv -f SJIS '{ダウンロードしたファイル: KEN_ALL_ROME.CSV}' | sed 's/"//g' | awk -F "," '{ print $1","$2" "$3$4","$5$6$7 }' >> postcodes.csv
```

という感じでutf8に変換しつつ住所情報を連結します。

続いてデータをインポートするために作成したファイルをmysqlサーバー上に配置して取り込みます。

```bash:ファイルをコピー
docker cp postcodes.csv <コンテナID>:/var/lib/mysql-files/
```

```sql:インポート
LOAD DATA INFILE '/var/lib/mysql-files/postcodes.csv' INTO TABLE postcodes FIELDS TERMINATED BY ',' (postcode, address, address_roma);
```

これでデータの準備が完了したので、検索を試してみましょう。

## 全文検索を試す

全文検索関数`MATCH`を利用して検索を実行します。
[ [参考] MySQL 5.6リファレンスマニュアル > 12.9 全文検索関数](https://dev.mysql.com/doc/refman/5.6/ja/fulltext-search.html)

全文検索の種類は以下のようにいくつか存在していますが、

```
MATCH (col1,col2,...) AGAINST (expr [search_modifier])
```
```
search_modifier:
  {
       IN NATURAL LANGUAGE MODE
     | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
     | IN BOOLEAN MODE
     | WITH QUERY EXPANSION
  }
```
以下ではブール検索を使っていきます。

`MATCH`関数は検索キーワードに対するスコアを返してくれるため、以下のようなSQLでスコアが高いものを確認することができます。

```sql
SELECT
  *,
  MATCH(address, address_roma) AGAINST('東京 池袋' IN BOOLEAN MODE) score
FROM
  postcodes
ORDER BY score DESC
LIMIT 5;
```

| FTS_DOC_ID 	| postcode 	| address 	| address_roma 	| score  |
| --- | --- | --- | --- | --- |
| 39687 	| 1700014 	| 東京都  豊島区池袋　（１丁目） 	| TOKYO  TOTOSHIMA  KUIKEBUKURO  (1-CHOME) 	| 23.46796417236328  |
| 39688 	| 1710014 	| 東京都  豊島区池袋　（２〜４丁目） 	| TOKYO  TOTOSHIMA  KUIKEBUKURO  (2-4-CHOME) 	| 23.46796417236328  |
| 41166 	| 2310834 	| 神奈川県  横浜市　中区池袋 	| KANAGAWA  KENYOKOHAMA  SHI  NAKA  KUIKEBUKURO 	| 21.222841262817383  |
| 37327 	| 1006490 	| 東京都  千代田区丸の内　東京ビルディング（地階・階 	| TOKYO  TOCHIYODA  KUMARUNOCHI  TOKYOBIRUDEINGU(CHIKAI.KA 	| 4.490244388580322  |
| 37328 	| 1006401 	| 東京都  千代田区丸の内　東京ビルディング（１階） 	| TOKYO  TOCHIYODA  KUMARUNOCHI  TOKYOBIRUDEINGU(1-KAI) 	| 4.490244388580322  |

検索キーワードはスペース区切りで指定することでOR条件として働きます。
そのため上記の条件では`東京`と`池袋`それぞれにヒットするものにスコアがついています。

また、スペースを削除した検索キーワードを指定すると、キーワードがmecabを通された上でそれぞれのキーワードの順番にマッチするものだけにスコアがつきます。

```sql
SELECT
  *,
  MATCH(address, address_roma) AGAINST('東京池袋' IN BOOLEAN MODE) score
FROM
  postcodes
ORDER BY score DESC
LIMIT 5;
```

| FTS_DOC_ID 	| postcode 	| address 	| address_roma 	| score  |
| --- | --- | --- | --- | --- |
| 39687 	| 1700014 	| 東京都  豊島区池袋　（１丁目） 	| TOKYO  TOTOSHIMA  KUIKEBUKURO  (1-CHOME) 	| 23.46796417236328  |
| 39688 	| 1710014 	| 東京都  豊島区池袋　（２〜４丁目） 	| TOKYO  TOTOSHIMA  KUIKEBUKURO  (2-4-CHOME) 	| 23.46796417236328  |
| 65536 	| 4280415 	| 静岡県  榛原郡　川根本町上岸 	| SHIZUOKA  KENHAIBARA  GUN  KAWANEHON  CHOKAMIKISHI 	| 0  |
| 256 	| 0650014 	| 北海道  札幌市　東区北十四条東 	| HOKKAIDOSAPPORO  SHI  HIGASHI  KUKITA14-JOHIGASHI 	| 0  |
| 65792 	| 4620844 	| 愛知県  名古屋市　北区清水 	| AICHI  KENNAGOYA  SHI  KITA  KUSHIMIZU 	| 0  |

`東京 > 池袋` の順番でマッチするものだけにスコアがつき、3つ目以降のスコアは0になっています。

### 池袋のサンシャインビルの郵便番号

はじめに書いたとおり、郵便番号は郵便物を仕分けて効率よく配送するために使われます。
そのため大きなビルの場合はフロアごとに異なる郵便番号が振られることもあります。

池袋を代表するサンシャイン６０もそんなビルの一つです。

```sql
SELECT
  *
FROM
  postcodes
WHERE
  address LIKE '%サンシャイン６０%'
```

を実行すると、

| FTS_DOC_ID 	| postcode 	| address 	| address_roma  |
| --- | --- | --- | --- |
| 39704 	| 1706090 	| 東京都豊島区東池袋サンシャイン６０（地階・階 	| TOKYO  TO  TOSHIMA  KU  HIGASHIIKEBUKURO  SANSHAIN60(CHIKAI.  |
| 39705 	| 1706001 	| 東京都豊島区東池袋サンシャイン６０（１階） 	| TOKYO  TO  TOSHIMA  KU  HIGASHIIKEBUKURO  SANSHAIN60(1-KAI)  |
| 39706 	| 1706002 	| 東京都豊島区東池袋サンシャイン６０（２階） 	| TOKYO  TO  TOSHIMA  KU  HIGASHIIKEBUKURO  SANSHAIN60(2-KAI)  |
| 39707 	| 1706003 	| 東京都豊島区東池袋サンシャイン６０（３階） 	| TOKYO  TO  TOSHIMA  KU  HIGASHIIKEBUKURO  SANSHAIN60(3-KAI)  |
| 39708 	| 1706004 	| 東京都豊島区東池袋サンシャイン６０（４階） 	| TOKYO  TO  TOSHIMA  KU  HIGASHIIKEBUKURO  SANSHAIN60(4-KAI)  |
以下続く...

というレコードが61件ヒットします。
（地階+60階それぞれに郵便番号が振られている！）

ただ、これらのレコードは `東京 > 池袋` という条件にもヒットするように思われますが、`東京池袋` という検索キーワードを試した上記の結果には含まれてきていません。そしてキーワードを`東京東池袋`とするとヒットしてきます。
これはmecabでの形態素解析の結果、`東池袋`が固有名詞として判断されているためです。mecabでの解析結果は以下のように確認することができます。

```bash
docker-compose exec db bash

root@<コンテナID>:/# mecab
東京東池袋
東京	名詞,固有名詞,地域,一般,*,*,東京,トウキョウ,トーキョー
東池袋	名詞,固有名詞,地域,一般,*,*,東池袋,ヒガシイケブクロ,ヒガシイケブクロ
EOS
```

またmysqlのレコードの解析結果は`information_schema.INNODB_FT_INDEX_TABLE` に格納されています。

```sql
SET GLOBAL INNODB_FT_INDEX_TABLE = '{作成したDababase名}/postcodes'; # 確認前に対象のテーブル情報をセットする必要がある
SELECT
  word,
  doc_count,
  doc_id,
  position
FROM
  information_schema.INNODB_FT_INDEX_TABLE
WHERE
  word LIKE '%池袋%'
LIMIT
  10;
```
| word 	| doc_count 	| doc_id 	| position  |
| --- | --- | --- | --- |
| 上池袋 	| 1 	| 39691 	| 19  |
| 南池袋 	| 1 	| 39765 	| 19  |
| 東池袋 	| 62 	| 39703 	| 19  |
| 東池袋 	| 62 	| 39704 	| 19  |
| 東池袋 	| 62 	| 39705 	| 19  |
| 東池袋 	| 62 	| 39706 	| 19  |
| 東池袋 	| 62 	| 39707 	| 19  |
| 東池袋 	| 62 	| 39708 	| 19  |
| 東池袋 	| 62 	| 39709 	| 19  |
| 東池袋 	| 62 	| 39710 	| 19  |

このように少々考えないといけないこともありますが、例えばサンシャインビルの10階の郵便番号を知りたい場合は
キーワードを`東京サンシャイン 10`とすることで情報を取得することができます。

```sql
SELECT
  *,
  MATCH(address, address_roma) AGAINST('東京サンシャイン 10' IN BOOLEAN MODE) score
FROM
  postcodes
ORDER BY score DESC
LIMIT 5;
```
| FTS_DOC_ID 	| postcode 	| address 	| address_roma 	| score  |
| --- | --- | --- | --- | --- |
| 39714 	| 1706010 	| 東京都  豊島区東池袋　サンシャイン６０（１０階） 	| TOKYO  TOTOSHIMA  KUHIGASHIIKEBUKURO  SANSHAIN60(10-KAI) 	| 21.841922760009766  |
| 39704 	| 1706090 	| 東京都  豊島区東池袋　サンシャイン６０（地階・階 	| TOKYO  TOTOSHIMA  KUHIGASHIIKEBUKURO  SANSHAIN60(CHIKAI. 	| 13.183591842651367  |
| 39705 	| 1706001 	| 東京都  豊島区東池袋　サンシャイン６０（１階） 	| TOKYO  TOTOSHIMA  KUHIGASHIIKEBUKURO  SANSHAIN60(1-KAI) 	| 13.183591842651367  |
| 39706 	| 1706002 	| 東京都  豊島区東池袋　サンシャイン６０（２階） 	| TOKYO  TOTOSHIMA  KUHIGASHIIKEBUKURO  SANSHAIN60(2-KAI) 	| 13.183591842651367  |
| 39707 	| 1706003 	| 東京都  豊島区東池袋　サンシャイン６０（３階） 	| TOKYO  TOTOSHIMA  KUHIGASHIIKEBUKURO  SANSHAIN60(3-KAI) 	| 13.183591842651367  |

`東京都  豊島区東池袋　サンシャイン６０（１０階）`の階数は全角数字ですが、きちんとヒットしています。

### キーワードの重み付け

1つ前の例のキーワードを少し変えて`東京 サンシャイン 10`とします。

| FTS_DOC_ID 	| postcode 	| address 	| address_roma 	| score  |
| --- | --- | --- | --- | --- |
| 39714 	| 1706010 	| 東京都  豊島区東池袋　サンシャイン６０（１０階） 	| TOKYO  TOTOSHIMA  KUHIGASHIIKEBUKURO  SANSHAIN60(10-KAI) 	| 21.841922760009766  |
| 37337 	| 1006410 	| 東京都  千代田区丸の内　東京ビルディング（１０階） 	| TOKYO  TOCHIYODA  KUMARUNOCHI  TOKYOBIRUDEINGU(10-KAI) 	| 13.213813781738281  |
| 37493 	| 1036010 	| 東京都  中央区日本橋　東京日本橋タワー（１０階） 	| TOKYO  TOCHUO  KUNIHOMBASHI  TOKYONIHOMBASHITAWA(10-K 	| 13.213813781738281  |
| 39102 	| 1631410 	| 東京都  新宿区西新宿　東京オペラシティ（１０階） 	| TOKYO  TOSHINJUKU  KUNISHISHINJUKU  TOKYOOPERASHITEI(10-K 	| 13.213813781738281  |
| 39704 	| 1706090 	| 東京都  豊島区東池袋　サンシャイン６０（地階・階 	| TOKYO  TOTOSHIMA  KUHIGASHIIKEBUKURO  SANSHAIN60(CHIKAI. 	| 13.183591842651367  |

この指定方法ではスペースで区切られたキーワードがOR条件となるため、サンシャイン以外のビルもヒットしてきています。
(`サンシャイン`を含むビルよりも高いスコアになっている理由は[ここのページ](https://dev.mysql.com/doc/refman/5.6/ja/fulltext-boolean.html)の`TF-IDF`の説明が参考になります)

あくまでサンシャインの情報をみたいということであれば、`東京 >サンシャイン 10`とすることで重み付けができます。

```sql
SELECT
  *,
  MATCH(address, address_roma) AGAINST('東京 >サンシャイン 10' IN BOOLEAN MODE) score
FROM
  postcodes
ORDER BY score DESC
LIMIT 5;
```

| FTS_DOC_ID 	| postcode 	| address 	| address_roma 	| score  |
| --- | --- | --- | --- | --- |
| 39714 	| 1706010 	| 東京都  豊島区東池袋　サンシャイン６０（１０階） 	| TOKYO  TOTOSHIMA  KUHIGASHIIKEBUKURO  SANSHAIN60(10-KAI) 	| 22.841922760009766  |
| 39704 	| 1706090 	| 東京都  豊島区東池袋　サンシャイン６０（地階・階 	| TOKYO  TOTOSHIMA  KUHIGASHIIKEBUKURO  SANSHAIN60(CHIKAI. 	| 14.183591842651367  |
| 39705 	| 1706001 	| 東京都  豊島区東池袋　サンシャイン６０（１階） 	| TOKYO  TOTOSHIMA  KUHIGASHIIKEBUKURO  SANSHAIN60(1-KAI) 	| 14.183591842651367  |
| 39706 	| 1706002 	| 東京都  豊島区東池袋　サンシャイン６０（２階） 	| TOKYO  TOTOSHIMA  KUHIGASHIIKEBUKURO  SANSHAIN60(2-KAI) 	| 14.183591842651367  |
| 39707 	| 1706003 	| 東京都  豊島区東池袋　サンシャイン６０（３階） 	| TOKYO  TOTOSHIMA  KUHIGASHIIKEBUKURO  SANSHAIN60(3-KAI) 	| 14.183591842651367  |

`サンシャイン`を含むレコードのスコアが全体的に上がって、他のビルのスコアよりも大きくなりました。

キーワードの前につける演算子は`<`,`+`,`-`など他にもあり、条件を細かく調整することができます。
[ [参考]MySQL 5.6リファレンスマニュアル > 12.9.2 ブール全文検索 ](https://dev.mysql.com/doc/refman/5.6/ja/fulltext-boolean.html)

## まとめ

MySQLを使って手軽に全文検索を試すことができました。
郵便番号から住所を補完するフォームは一般的ですが、逆に住所から郵便番号の候補をサジェストするようなことにも使えそうですね。

ただ残念なことにAmazon RDS for MySQLでは5.7を選択してもmecabがサポートされていないとのこと(ngramは利用可能)。
[ [参考]Amazon RDS for MySQL と全文検索](https://dev.classmethod.jp/cloud/aws/amazon-rds-for-mysql-fulltext-search/)

mecabを使いたい場合は、サポート開始を待つか自分でデータベースサーバーを管理する必要がありそうです。

オープンロジでは郵便番号問題だけでなく、物流に関わる様々な課題に一緒に取り組んでくれるエンジニアを募集しています！
https://www.wantedly.com/projects/171442

## その他の参考資料

- MySQL 5.7 Reference Manual > 12.9.9 MeCab Full-Text Parser Plugin: https://dev.mysql.com/doc/refman/5.7/en/fulltext-search-mecab.html
- MySQL 5.7 InnoDB 日本語全文検索(その1): https://www.slideshare.net/yoyamasaki/20160209-inno-dbftsjp
- MySQL 5.7 InnoDB 日本語全文検索(その2): https://www.slideshare.net/yoyamasaki/mysql-57-innodb