Geek Answers Handbook

How to save and find IP address

I have 4 sources of IP addresses, I want to store them in SQL Server and allow ranges that can be classified by the source code of the country, which should be copied to the list of exceptions by country.

For this, I have 2 tables.

IPAddressRange CountryCode

What I need to know if this data was returned to the client and then cached for a quick request, which is the best way to store the returned data for a specific IP address within ranges. I want to know if the incoming IP address is included in the list.

The reason the list is in db is because of the simple storage.

The reason I want to cache, then use the data on the client, is because I heard that IP lookups are faster in the trie structure. So, I think I need to get the list from db, store in cache in a very fast structure.

Any help in A) An SQL structure for storing addresses and b) Code for finding IP addresses.

I know a solution for a code project that has a code algorithm for searching, not sure how to mix it with the storage aspect.

Ideally, without using a third-party library. The code must be on our own server.

+3
source share
7 answers

I did a filter by country exactly as you describe.

, , , SQL. IP- , (, ) , , .

:

, , CSV , SQL, . - , .

, .

- , .

+3

, IP- IPV4, . 2 , , . , . , , . - , , - , .

+1

IPv4 (uint #). IPv6 ( #). SQL, . , , , .

IPAddress, , , .

0

, , , , , IP-, ( /). , btree , ( btrees ). 4 IP , A/B/C " ", NULL, , 32- , , ( ).

0

IPv6 unsigned integer (ulong #)

IPv6- 128- (16 ), 8, . IP.

< >

0

, IPv4 . varchar ( ) - int.

IPv4 IP- , , INET_ATON ( , , #, ).

, IP-, .

LIMIT ( SELECT TOP 1 MSSQL), , .

SELECT TOP 1 networkidorwhatever, IPNumber, IPNumberUpperBoundOrWhateverYouCallIt 
FROM networks 
WHERE IPNumber <= IPNUMBERTOQUERY ORDER BY IPNumber DESC

, <= IP, , , IP- .

, IPNumber.

IPv6 , .

0

For IPv4, as a rule, the database administrator will recommend 4 tinyint fields, but you do ranges that are more useful than previously provided. In this case, you must save the start IP address and the end IP address for the range. Then just make a comparison.

0
source

More articles:


All Articles