在SQL中查询GeoLite2国家CSV [英] Querying GeoLite2 Country CSV in SQL
问题描述
有人知道如何使用SQL从MaxMind的GeoLite2国家CSV查找IP4地址吗?
多年来，我一直在使用MaxMind的免费GeoIP数据，并希望升级到其GeoLite2数据.我已经将数据块和位置数据加载到MySQL表中，但是不确定如何确定IP4地址所属的地址范围.旧格式的每个块都有一个开始/结束编号；新格式似乎只有一个起始编号.
我已经搜寻过MaxMind开发人员文档和Googled，但似乎找不到有关如何查询新格式的任何信息.我敢肯定这很明显，如果我在临时过程中发现了该帖子，将对其进行编辑.
我思考谢谢，我必须找到第一个大于或等于IP4地址以及LIMIT 1的块条目.
我将这些数据用于Web应用程序外观以及直接在SQL中查询以生成报告.因此我通常需要确保可以在Perl代码和纯SQL中都实现查找.
我正在升级，因为我看到旧数据看来似乎是来自法国的日本游客的有趣结果.
非常感谢
Geolite2 CSV中使用的地址格式包括一个块IP地址开头，后跟一个前缀长度#，可以将其转换为块IP地址结尾./p>
(有些令人困惑的是，Maxmind使用"Network_Mask_Length"代替了公认的IPv6术语"Prefix_Length"来标记该字段.)
Geolite2 CSV的阻止"字段布局:
network_start_ip,network_mask_length,geoname_id,registered_country_geoname_id,re
presented_country_geoname_id,postal_code,latitude,longitude,is_anonymous_proxy,i
s_satellite_provider
示例:(从Geolite2-Country-Blocks.csv中提取的记录)
::ffff:81.248.136.0,120,3578476,3017382,,,,,0,0
鉴于上述示例，分配给该块的最后一个IPv4地址是什么?
First IP address: 81.248.136.0
Prefix_Length/Network_mask_Length: 120
Last IP address: 81.248.136.255
以下URL可以方便地快速查找用于特定Prefix_Length的IP地址数量:
http://www.gestioip.net/cgi-bin/subnet_calculator.cgi
__ philippe
Does anyone know how to look up an IP4 address from MaxMind's GeoLite2 Country CSV using SQL?
I have been using MaxMind's free GeoIP data for many years, and would like to upgrade to their GeoLite2 data. I have the blocks and locations data loaded into MySQL tables, but am not sure how to determine the address range that an IP4 address falls into. The old format had a start/end number for each block; the new format only seems to have a start number.
I have already hunted through the MaxMind developer docs, and Googled, but can't seem to find any information on how to query the new format. I'm sure it's obvious, and will edit this posting if I figure it out in the interim.
I think thank I'd have to find the first blocks entry that is greater or equal to the IP4 address, and LIMIT 1, perhaps.
I use this data both for web application looks, and for querying directly in SQL for generating reports; so I usually need to make sure I can implement the lookup in both Perl code and pure SQL.
I am upgrading because I'm seeing some funny results for Japanese visitors appearing to be from France on the old data.
many thanks
The address format used in Geolite2 CSV includes a block IP address start, followed by a Prefix length #, which can be converted into the block IP address end.
(Somewhat confusingly, Maxmind is using "Network_Mask_Length" instead of "Prefix_Length", the accepted IPv6 terminology, to label this field.)
Geolite2 CSV's blocks fields layout:
network_start_ip,network_mask_length,geoname_id,registered_country_geoname_id,re
presented_country_geoname_id,postal_code,latitude,longitude,is_anonymous_proxy,i
s_satellite_provider
Exemple:(record extracted from Geolite2-Country-Blocks.csv)
::ffff:81.248.136.0,120,3578476,3017382,,,,,0,0
Given the above exemple, what is the Last IPv4 address assigned to the block?
First IP address: 81.248.136.0
Prefix_Length/Network_mask_Length: 120
Last IP address: 81.248.136.255
The following URL might be handy to quickly look up the number of IP addresses available for a specific Prefix_Length:
http://www.gestioip.net/cgi-bin/subnet_calculator.cgi
__philippe
这篇关于在SQL中查询GeoLite2国家CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
