`
Fangrn
  • 浏览: 818124 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

如何使用SQL查询IP地址所属IP段

阅读更多

最近有个朋友说他要做的人员注册量的统计,有两张表,一张是用户注册表,一张是IP段对应城市表。需要根据用户注册时的IP查询到对应的城市,从而知道该城市有多少人注册。

其实没什么,关键是IP地址和IP段的匹配问题。

一开始,我使用的是BETWEEN,结果显然是不行的。

最后我想到用函数直接将192.168.0.1形式的IP按256进制转为数字,然后进行between(或者"<"AND">")。

其实想到方法,实现就简单的多了。下面把函数贴上来,有需要的朋友可以偷偷懒 ^_^

1、用户注册表

     create table SINO_USER
(
  ID          NUMBER(19),
  USERNAME    VARCHAR2(255),
  UPDATE_TIME TIMESTAMP(6),
  IP          VARCHAR2(255)
)

2、IP地址段表

create table SINO_IP
(
  ID       NUMBER(19),
  IP_BEGIN VARCHAR2(255),
  CITY     VARCHAR2(255),
  IP_END   VARCHAR2(255)
)

注:这两张表是我简化了,千万别以为是生产库的表.....

3、创建函数

create or replace function f_ip2number(ip in varchar2) return varchar2 as
v_ip_1 number;
v_ip_2 number;
v_ip_3 number;
v_ip_4 number;
v_result number;
begin
select to_number(substr(ip,1,instr(ip,'.',1,1)-1)),
       to_number(substr(ip,instr(ip,'.',1,1)+1,instr(ip,'.',1,2)-instr(ip,'.',1,1)-1)),
       to_number(substr(ip,instr(ip,'.',1,2)+1,instr(ip,'.',1,3)-instr(ip,'.',1,2)-1)),
       to_number(substr(ip,instr(ip,'.',1,3)+1,length(ip)-instr(ip,'.',1,3))) into v_ip_1,v_ip_2,v_ip_3,v_ip_4
  from dual;
v_result := v_ip_1 * 256*256*256 + v_ip_2 * 256*256 + v_ip_3 * 256 + v_ip_4;
return v_result;
end;

4、查询的SQL

select *
  from sino_user t, sino_ip t1
 where f_ip2number(t.ip) between f_ip2number(t1.ip_begin) and
       f_ip2number(t1.ip_end)

    IP                          IP_BEGIN                   IP_END                  CITY
1 200.220.0.10        200.220.0.1           255.255.255.255        城市5
2 199.168.200.21    192.168.255.255   200.168.105.10          城市4
3 199.168.200.20    192.168.255.255   200.168.105.10          城市4
4 192.168.200.20    192.168.21.1         192.168.225.10          城市3
5 192.168.1.21        192.168.1.1           192.168.1.35              城市2
6 192.168.1.20        192.168.1.1           192.168.1.35              城市2
7 192.168.0.50        192.168.0.1           192.168.0.255            城市1

相信大家都注意到问题在哪里了,对了,执行效率问题。

解决方法:

方法一、加冗余字段

在两个表都加上冗余字段,统计查询的时候直接根据冗余字段来查询;

方法二、加函数索引

如sino_user表的IP字段:CREATE INDEX IDX_SINO_USER_IP ON SINO_USER(F_IP2NUMBER(IP));

如果你按照我上面说的创建索引,ORACLE会报错的,具体什么错我忘了,大概是函数没有准备好之类的。

这是因为ORACLE对于自定义函数建索引是有规定的。

回到函数的创建:

create or replace function f_ip2number(ip in varchar2) return varchar2 as

这么来创建是不能用来加索引的,需要加DETERMINISTIC

create or replace function f_ip2number(ip in varchar2) return varchar2 DETERMINISTIC as

OK,现在再来创建索引就OK了。

哈哈,很简单吧。

分享到:
评论

相关推荐

    IP地址库2018024(SQL Server)

    这个IP地址库包含了ISP信息,这意味着用户不仅可以得知IP地址的地理位置,还能了解到该IP地址所属的网络运营商。这对于理解网络连接的质量、速度和稳定性,以及处理与ISP相关的网络问题具有重要意义。 在实际操作中...

    IP地址库20191104(SQL Server)

    同时,它还包含了ISP(Internet Service Provider,互联网服务提供商)信息,这意味着可以追踪到IP地址所属的网络服务商,这对于网络安全分析、广告定向投放、流量监控等场景都有重要作用。 从压缩包子文件的文件...

    全国最新IP地址库SQL版

    全国最新IP地址库 各省份 城市 运营商 SQL脚本版。 可以根据ip判断所属城市。

    IP地址库 SQL Server数据库文件

    IP地址库通常用于存储大量的IP地址信息,可能包括IP地址、所属国家、地区、城市等详细地理位置信息,常用于网络监控、流量分析、安全防护等多种用途。 在描述中提到的“sql2000的mdf文件数据库”,这是SQL Server...

    MYSQL最新IP地址库(全国+全球)

    8. **查询和使用**:在MySQL中,可以通过SQL语句来查询IP地址库,例如,根据IP地址查找对应的城市和运营商信息。对于开发者来说,了解如何设计高效的查询语句以提高性能是非常重要的。 9. **安全和隐私**:虽然IP...

    IP地址库20180710(SQL Server)

    了解一个IP地址所属的ISP,可以揭示网络连接的类型(如家庭宽带、移动数据等),甚至可能推断出用户的上网习惯和偏好。这对于广告定向、网络优化和流量管理有实际价值。 数据库的设计通常包括精心构建的数据结构和...

    利用SQL区分网站域名IP地址归属的方法.pdf

    利用SQL查询时,可以采用BETWEEN语句进行范围查询,从而确定IP地址所属范围。而对于域名查询,如果域名没有直接对应的IP地址,则需要先用dig命令在UNIX主机上查询出域名对应的IP地址,并将其记录到临时文本文件中。...

    点创IP地址查询工具(SQL版) build 101201.rar

    使用这样的数据库,用户可以快速定位到一个IP地址所属的地区,这对于网络安全监控、网站统计分析、防止恶意访问等方面都有重要作用。 点创IP地址查询工具采用ASP源码开发,ASP(Active Server Pages)是微软推出的...

    IP地址库20181219(SQL Server)

    通过查询IP地址库,可以得知一个IP地址所属的国家、地区、城市,甚至精确到运营商,这对于网络服务提供商、企业及研究机构来说具有极高的实用价值。 2. **纯真数据库与淘宝地址库** “纯真数据库”是中国互联网...

    IP地址查询库【mysql】

    通过创建特定的数据表结构,如包含IP地址范围(起始IP和结束IP)以及对应地区的字段,我们可以快速地定位到一个IP地址所属的地理位置。 文件`ip_table.sql`很可能是数据库的建表脚本,它定义了存储IP地址信息的表格...

    IP地址库(Access版)

    例如,查询IP地址“192.168.0.1”所属的国家,可以编写如下SQL语句: ```sql SELECT Country.Name FROM IP_Range INNER JOIN Country ON IP_Range.CountryCode = Country.Code WHERE IP_Range.StartIP ...

    IP地址归属地查询程序(PHP)

    【IP地址归属地查询程序(PHP)】是一种使用PHP编程语言实现的系统,它允许用户查询特定IP地址的归属地信息。在互联网上,IP地址是每个设备连接网络时的唯一标识,通过IP地址,我们可以获取到该地址所属的国家、地区、...

    纯真IP数据库导入MS SQL SERVER

    它主要用于帮助用户快速查询和识别IP地址所属的地理位置。QQWry是纯真IP数据库的一个更新版本,提供了更准确和最新的IP地址数据。 在IT行业中,将这样的IP数据库导入到关系型数据库管理系统(RDBMS)如Microsoft ...

    IP数据库及地址对应

    1. **网络安全**:通过查询IP地址所属的位置或机构,可以帮助识别恶意行为或异常访问。 2. **地理定位服务**:许多网站和服务通过IP地址获取用户的大致位置,以提供更加个性化的服务或内容。 3. **流量分析**:对于...

    纯真IP,根据IP地址找出所属的地区

    首先,我们需要连接到这个数据库,然后执行SQL查询来查找特定IP地址的信息。例如,我们可以创建一个函数,输入IP地址,返回地区和运营商: ```python import pyodbc def find_location(ip): conn_str = "DRIVER={...

    批量查IP地址.rar

    批量查询可以帮助识别IP地址所属的网络运营商。 3. 网络类型:查询可以揭示IP地址是属于公网还是私网,或者是特定网络如教育网、企业网等。 4. 主机名和域名:IP地址可以映射到对应的主机名和域名,这对于网络管理...

    2022版全国最新IP地址库(带经纬度定位)

    这些信息可能包括IP地址所属的国家、地区、城市、运营商,甚至是具体的地理位置坐标。对于iptablefull.xlsx这个Excel文件,我们可以预见到它以表格形式存储了IP地址范围、对应的地理位置信息,以及可能的其他附加...

    根据IP地址获去IP所属省、市、区以及运营商信息

    根据IP地址获去IP所属省、市、区以及运营商 。使用方法,先将IP地址转换为数字,然后根据NUM_START很NUM_END范围查询IP所属区域

    MDB格式 IP 地址库 2008-06-29

    2. 查询:预定义的查询用于快速检索特定类型的IP信息,如查询所有属于某个国家的IP地址或指定网络段的IP范围。 3. 窗体:用户友好的界面,用于查看、搜索和编辑IP地址记录。 4. 报表:统计报告,展示IP地址库的分布...

    ip查询整站IP138界面

    在IT行业中,IP查询是一项基础且重要的功能,它主要用于获取与特定IP地址相关的各种信息。本文将围绕"ip查询整站IP138界面"这一主题,深入探讨IP查询的相关知识点,以及如何实现一个类似的系统。 首先,我们需要...

Global site tag (gtag.js) - Google Analytics