IP2LocationのCSVデータをデータベースにインポートする方法

IP2Locationとは

IPアドレスからだいたいの住所や緯度・経度情報を割り出すのに便利なIP2Location。基本的には有償のサービスだけど、無償プランのほうを使う人も多いと思う。メアドを登録するだけでCSVデータを入手できる。

テーブルを作る

CREATE TABLE `ip2location_db3` (
    `ip_from` INT(10) UNSIGNED NULL DEFAULT NULL,
    `ip_to` INT(10) UNSIGNED NULL DEFAULT NULL,
    `country_code` CHAR(2) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `country_name` VARCHAR(64) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `region_name` VARCHAR(128) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `city_name` VARCHAR(128) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    INDEX `idx_ip_from` (`ip_from`),
    INDEX `idx_ip_to` (`ip_to`),
    INDEX `idx_ip_from_to` (`ip_from`, `ip_to`)
)
COMMENT='IPテーブル'
COLLATE='utf8_bin'
ENGINE=InnoDB
;

まず、上記のようにテーブルを作る。このテーブルにCSVデータを食わせる。DBのフィールド名はなんでもよくて、要は６列のCSVデータなのでフィールドが６つあればいい。後述の取り込みスクリプトではフィールド名を照合しないから、数さえ合っていれば問題ない。フィールドの型も、varcherであればとりあえず動作はする。

https://lite.ip2location.com/
データはここから入手する。ダウンロードできるデータはいくつか種類があって、都道府県名だけ分かればいいならDB3。市町村情報も取得できるが、あまりあてにならない。福岡県のデータを調べてみたら、福岡市・北九州市・久留米市・筑後市・苅田町・宗像市・大牟田市・八女市しかなかった。

緯度経度も必要なら DB5を選ぶ。上記のcreate table文はDB3用なので、DB5の場合はさらにフィールドを追加する必要がある。

データを取り込むSQL文

TRUNCATE ip2location_db3;

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\download\\ipdata.csv'
INTO TABLE ip2location_db3
FIELDS TERMINATED BY ','
ENCLOSED BY '"'   
LINES TERMINATED BY '\r\n'

これでデータを取り込める。フィールド名の照合を行わず無条件に追加する。稼働中のサービスに負荷をかけないようにLOW_PRIORITYをつけている。

シェル操作が苦手な人はこのスクリプトの実行方法が分からないかもしれないけど、phpMyAdminやAdminerの「SQLを実行」みたいなのに貼りつけて実行ボタンをクリックするだけでいいはず。

インポート元のデータは日本以外の国のデータも含まれているので、全部でかなりの量になる。日本国内だけ対象にすればよい場合は、grep "JP" data.csv > jp.csvなどとしてあらかじめデータを絞り込んでおく。だいぶコンパクトになる。

このCSVデータで扱われているIPアドレス情報はxxx.xxx.xxx.xxx形式ではなく、長整数表現と呼ばれる形式なので注意。PHP側で処理するならip2longを、MySQL側ならinet_atonで変換する。

ここまで書いといてアレだけど、
https://lite.ip2location.com/database/ip-country-region-city
下のほうにほぼ同じことが書いてある。細部が微妙に違うけど得られる結果はほぼ同じ。でも英語のページってだけで不安って人も多いと思うので、この記事を書くことにしました。