欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL导入GeoIP的CSV数据

程序员文章站 2022-05-15 09:44:42
...

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