当前位置:Gxlcms > mysql > MySQL导入GeoIP的CSV数据

MySQL导入GeoIP的CSV数据

时间:2021-07-01 10:21:17 帮助过:59人阅读

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

人气教程排行