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到城市的映射库,从知乎上进行搜索,了解了目前大概弄了个列表如下所示:
- 1)腾讯-腾讯IP分享计划_IP分享
链接网址:http://ip.qq.com
- 2)新浪
链接网址:http://int.dpool.sina.com.cn/iplookup/iplookup.php?ip=8.8.8.8&format=js
- 3)百度-Web服务Geocoding API-百度地图
链接网址:http://developer.baidu.com/map/ip-location-api.htm
- 4)淘宝-淘宝IP地址库
链接网址:http://ip.taobao.com/accurancy.php
- 5)geoip-MaxMind-IP地理定位和在线欺诈预防
链接网址:http://www.maxmind.com
- 6)纯真
链接网址:http://www.cz88.net
- 7)17mon-IP归属地数据库下载_17MON网络工具集
链接网址: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 );
3.2)导入到本地mysql数据库,shell脚本如下所示:
# 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";'注意:
- (1)其中MYSQL_USER是数据库用户,PASSWORD是相应的密码,geoip是3.1中创建的数据名,--local-infile=1是允许在导入数据时使用本地绝对路径,-e后跟的是SQL语句。
- (2)在-e后的SQL语句中,LOCATION_CSV_FILE是geoip中下载解压后的一个location数据文件名,BLOCKS_CSV_FILE是geoip中下载解压后的一个blocks数据文件名。
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;注意:
以上的查询应该很浅显易懂,其中INET_ATON是MYSQL提供的,它的功能是将点分十进制的IP表示转换成INT型数据它是网络字节序的(即大端序),同样也有一个将INT型数据转换为点分十进制的函数
INET_NTOA
。 4)通过3中的几个步骤就可以成功的将数据导入到本地的MySQL库中。
三、小结
通过数据导入到数据库中,这样在上层就可以编程应用。存在的问题:还需要后期对IP地址数据库进行更新与维护。对于其它几类的IP地址库的使用效果,在后续还需要进行对比研究,以选择效果更好的IP地址库。
参考文献
- http://www.zhihu.com/question/19584593
- http://dev.maxmind.com/geoip/legacy/csv/
- http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet-aton
- http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#load-data