如何使用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了。

哈哈，很简单吧。