MySQL导入GeoIP的CSV数据

MySQL导入GeoIP的CSV数据 一、缘由 因为需要使用IP到城市的映射库，从知乎上进行搜索，了解了目前大概弄了个列表如下所示： 1）腾讯－腾讯IP分享计划_IP分享 链接网址：http://ip.qq.com 2）新浪 链接网址：http://int.dpool.sina.com.cn/iplookup/iplookup.

MySQL导入GeoIP的CSV数据

一、缘由

因为需要使用IP到城市的映射库，从知乎上进行搜索，了解了目前大概弄了个列表如下所示：

链接网址：http://ip.qq.com

链接网址：http://int.dpool.sina.com.cn/iplookup/iplookup.php?ip=8.8.8.8&format=js

链接网址：http://developer.baidu.com/map/ip-location-api.htm

链接网址：http://ip.taobao.com/accurancy.php

链接网址：http://www.maxmind.com

链接网址：http://www.cz88.net

链接网址：http://tool.17mon.cn/ipdb.html

在以上所罗列的表项中，主要是了解了geoip的免费的数据库。这个数据可以自行管理更新，也可以通过官网更新。本次所遇到的主要是在将geoip的CSV数据导入mysql时遇到了问题。

二、导入数据流程

1）下载geopip的IP地址CSV数据，下载地址如下所示：http://dev.maxmind.com/geoip/legacy/geolite/

2）解压缩数据。

3）根据官网提供的数据表结构进行数据库设计，官网中对CSV数据的格式描述连接为：http://dev.maxmind.com/geoip/legacy/csv/

3.1）数据库的设计，此处只是做了一个粗略的数据映射，关于数据类型与约束关系等还可以再进行细化，脚本如下所示：

create database geoip;
use geoip;

-- 1.1 具体的位置信息表
create table location(
 locId bigint primary key,
 country varchar(2),
 region char(2),
 city varchar(255),
 postal varchar(8),
 latitude decimal,
 longtitude decimal,
 metroCode int,
 areaCode char(3)
);

-- 1.2 IP地址对应的位信息表
create table block(
 startIpNum int,
 endIpNum int,
 locId bigint
);

# 2.1 
mysql -u${MYSQL_USER} -p${PASSWORD} geoip --local-infile=1 -e 'load data local infile "${LOCATION_CSV_FILE}" replace into table location fields terminated by "," OPTIONALLY ENCLOSED BY "\"" lines terminated by "\n";'
# 2.2
mysql -u${MYSQL_USER} -p${PASSWORD} geoip --local-infile=1 -e 'load data local infile "${BLOCKS_CSV_FILE}" replace into table block fields terminated by "," OPTIONALLY ENCLOSED BY "\"" lines terminated by "\n";'

3.3）数据查询，脚本如下所示：

SELECT * 
FROM block as b left outer join location as l on(l.locId = b.locId)
WHERE
INET_ATON('174.36.207.186') BETWEEN b.startIpNum AND b.endIpNum
LIMIT 1;

4）通过3中的几个步骤就可以成功的将数据导入到本地的MySQL库中。

三、小结

通过数据导入到数据库中，这样在上层就可以编程应用。存在的问题：还需要后期对IP地址数据库进行更新与维护。对于其它几类的IP地址库的使用效果，在后续还需要进行对比研究，以选择效果更好的IP地址库。

参考文献