LoginSignup
18
8

More than 5 years have passed since last update.

OPENLOGIAdvent Calendar 2017Day 6openlogi
@hmarui66

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

Last updated at Posted at 2017-12-05

OPENLOGI Advent Calendar 2017- Qiita の6日目の記事です。

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

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

郵便番号の役割

Wikipedia: 郵便番号に詳しい説明が載っていますので、ここでは軽く触れるだけにします。

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

郵便番号に関わる問題

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

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

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

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

ではどうすればよいのか

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

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

MySQL 5.7の準備

Dockerのmysql:5.7を利用します。ただし、そのままでは形態素解析エンジンのmecabが入っていないため以下のような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 の全文検索の微調整

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

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を実行します。

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を定義しているのは、このリファレンスを参考にしてます。

データの準備

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

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

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

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

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

ファイルをコピー
docker cp postcodes.csv <コンテナID>:/var/lib/mysql-files/
インポート
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 全文検索関数

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

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でスコアが高いものを確認することができます。

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を通された上でそれぞれのキーワードの順番にマッチするものだけにスコアがつきます。

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になっています。

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

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

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

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での解析結果は以下のように確認することができます。

docker-compose exec db bash

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

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

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とすることで情報を取得することができます。

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条件となるため、サンシャイン以外のビルもヒットしてきています。
(サンシャインを含むビルよりも高いスコアになっている理由はここのページのTF-IDFの説明が参考になります)

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

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 ブール全文検索

まとめ

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

ただ残念なことにAmazon RDS for MySQLでは5.7を選択してもmecabがサポートされていないとのこと(ngramは利用可能)。
[参考]Amazon RDS for MySQL と全文検索

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

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

その他の参考資料

18
8
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
18
8