How to use SQL query IP address is IP Section

2010-07-25  来源:本站原创  分类:Database  人气:225 

Recently, a friend said he was content to do the registration statistics, there are two tables, one is the user registry, one is the IP section of the table corresponds to the city. Need to register the user's IP query to the corresponding cities, so that the number of people registered in the city.

Actually no, the key is the IP address and IP segment matching.

At first, I use the BETWEEN, the result is clearly not acceptable.

Finally, I think with the function of the form directly to the IP 192.168.0.1 to binary numbers by 256, then between (or "<" AND ">")。

In fact, think of ways to achieve more on the simple. Here to function Tieshang Lai, a friend in need can secretly lazy ^ _ ^

1, the user registry

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

2, IP address ranges table

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

Note: This is my simplified two tables, do not think the production database table .....

3, Create Function

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, the SQL query

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 City 5
2 199.168.200.21 192.168.255.255 200.168.105.10 City 4
3 199.168.200.20 192.168.255.255 200.168.105.10 City 4
4 192.168.200.20 192.168.21.1 192.168.225.10 City 3
5 192.168.1.21 192.168.1.1 192.168.1.35 City 2
6 192.168.1.20 192.168.1.1 192.168.1.35 City 2
7 192.168.0.50 192.168.0.1 192.168.0.255 City 1

I believe we all took note of where the problem lies, and on, and implementation efficiency.

Solution:

Method One, plus redundant field

In the two tables with redundant field, when the statistical query to query directly from the redundant field;

Method 2, add function index

IP fields as sino_user table: CREATE INDEX IDX_SINO_USER_IP ON SINO_USER (F_IP2NUMBER (IP));

If you, as I said above, create the index, ORACLE will get an error, and what the specific error I forgot about that function is not ready or something.

This is because the custom function for the construction of ORACLE index is provided.

Back to the function of creation:

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

So to create the index can not be used to increase, and needs DETERMINISTIC

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

OK, now come back to create an index on OK.

Haha, it is very simple.

相关文章
  • How to use SQL query IP address is IP Section 2010-07-25

    Recently, a friend said he was content to do the registration statistics, there are two tables, one is the user registry, one is the IP section of the table corresponds to the city. Need to register the user's IP query to the corresponding cities, so

  • perl ip address get mac address under the 2011-04-14

    Sometimes need a host to get its mac address ip address, this can be achieved, but this must be the same ip address ip address within the Ethernet. In the perl module Net:: ARP can achieve this function. But it's arp_lookup function only from the loc

  • Ip address mysql string comparison problem for 2010-07-17

    Ip database of innocence today, in comparison to the ip address the issue of matching spent a lot of time, the effect is not always ideal. Inadvertently see there is this php function: ip2long, is to switch to growing integer string ip. So subconscio

  • One IP protocol Xiangjie: IP address of the knowledge 2010-07-21

    IP is the TCP / IP protocol suite lies at the core of the agreement. All of the TCP, UDP, ICMP and IGMP for IP packet data format for transmission. IP provides unreliable, connectionless datagram delivery service. Not reliable (unreliable) means that

  • How to configure the Windows command line IP address 2010-09-02

    Taken from the network: How to configure the Windows command line system IP address is sometimes a problem, do not see the local network connection to connect, network card work or normal. This is no way to modify a local The IP address. Because usua

  • Through the Windows command line to automatically set the Windows IP Address 2010-09-11

    Recent books I used to get F students to do some lab tests. Internet IP address with the office, and laboratory testing of the IP address is different. So I go back and forth between the two buildings, I often need manual adjustment of this PC's IP a

  • Backup and restore with the DOS command IP address of the Method 2011-04-12

    Often see the "expert" whenever they like to open a CMD command prompt window and enter a bunch of commands, people look confused, we are also able to become so-called expert, ha ha. Today, we teach using DOS commands to backup and restore the I

  • 命令行实现MAC与IP地址绑定 ip mac绑定 如何绑定mac地址 2014-10-15

    一般手上没有软件,就可以使用这命令行实现MAC与IP地址绑定 为什么要绑定IP呢?你指定的IP能上外网不就可以了吗?之所以要绑定IP,是因为他会会改IP.比如我本机上的IP是192.168.1.11此IP已经在防火墙上面做了设定不可以上网,但我要是知道有一个IP是192.168.1.30的IP能上网,那我不会改把192.168.1.11换成192.168.1.30就可以上网了吗?所以绑定IP就是为了防止他改IP. 因为网卡的MAC地址是全球唯一的跟我们的身份证一样,他一但改了,就不认了.那如何绑

  • Sql query on the ip range of writing 2010-12-02

    We met in the project such a problem, corresponding to the query ip address, using the google's map library, Gallery into which we put in the database, construct a table such -- Create table create table T_GGMAP_IP ( IPSTART NUMBER not null, IPEND NU

  • Tencent's IP address query interface (JS) 2010-03-22

    Tencent's IP address query interface (JS) Tencent's IP address query interface is good, IP address of query accuracy is high, IP address lookup is also easy to use, this is coming from elsewhere reproduced, the test found a small BUG, IP address look

  • IP address query command 2010-11-08

    IP address query command: ipconfig Details: ipconfig / all or ipconfig-all

  • ID. IP address. Phone number query entry 2010-11-26

    The three addresses, the return is xml, xml you only need to read about it. 1: Query ID http://www.youdao.com/smartresult-xml/search.s?type=id&q=140729198910260038 2: query IP address http://www.youdao.com/smartresult-xml/search.s?type=ip&q=222.37

  • View sql server ip address and port number 2011-04-18

    View SQL Server 2005 port number, we can go to install the SQL Server service on the machine, through the configuration tool SQL Server Configuration Manager (SSCM) in come. If the SQL Server on a remote server, this approach will not work. However,

  • JAVA query by IP address of Step-By-Step (pure IP address database) 2010-07-07

    1. First of all, we need a free IP address database. Pure IP address database is a good choice (see annex) 2. IPParser.java library to read the pure address. (See Annex) The IPParser tool does not require any data cache (to avoid the large memory foo

  • ORACLE modify how to use the IP address 2010-08-11

    Perhaps, this is the problem many people encounter a few days ago for the IP, leading to ORACLE start it up. First of all, to know a little. ORACLE not use a dynamic IP, you must use a static IP. Start - "Run enter CMD, enter the command IPCONFIG / A

  • ip address check python module written anti- 2010-02-02

    Search by IP address anti-attribution to the python module business logic from the other C + + module development has been completed. #include <Python.h> #include "iplookup.h" static il_db_t _g_db; ipitem ip_lookup(const char *query) { con

  • In the Filter driver to get IP address of the kernel 2010-04-18

    Project Development Filter Sometimes drivers need to get effective Unicast addresses, for example used to send data. Originally thought that the kernel need to be forced to complete the OID that was found OID_GEN_NETWORK_LAYER_ADDRESSES not support q

  • Reprinted: oracle SQL query used classic 2010-06-03

    oracle SQL query used classical 1, see table space name and size select t.tablespace_name, round (sum (bytes / (1024 * 1024)), 0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;

  • Just find the IP address of the corresponding regional database 2010-06-15

    Project because, download the We7 CMS installation version, (would like to download the source code to join something and actually found that source group, but also a year pay ¥ 100, halo, What kind of GP open reading ah !!!), downloaded Next app_dat

  • World ip address above 2010-08-03

    http://hi.baidu.com/chenshake/blog/item/f1eb0230921c0c90a8018e72.html http://www.wardking.com/blog/read.php/153.htm Received today a friend sent a small essay, saying that access to the China Internet Network IP address of all carriers of the distrib