Best way to store IP Address into Database System

In this post, I will explain effective storage of IP-Address into Database System.

Now let’s talk about four part IP-Address.

E.g, 152.145.10.25

Generally, many database developers or database designer store this IP-Address into one column which is VARCHAR(15).

What do you think? Is this effective storage of IP-Address?.

Now imagine you are playing with 10,0,00,00 online customers in which all this IP-Address of customer is required for analysis.

If we stored this IP-Address into one column, then you have to perform string manipulation for finding a particular range of IP-Address.

This kind of string operation is very costly and not advisable.


Effective storage of IP-Address is very simple:

Just divide your full length IP-Address into four part.

E.g.

Full IP-Address : 152.145.10.25

Four Part :

152

145

10

25

Now create four tinyint/smallint column to store this four different part.

column like,

ip1 tinyint

ip2 tinyint

ip3 tinyint

ip4 tinyint

 

The full IP-Address split into four different integer part.

Now task is very easy:

You can also apply index on all this part and perform range analysis very easily.

Yes, I agree that during storage you require to split and store into four different columns, but this is only a one time process after that you can easily use different part for different analysis.

During select, you can also concat this four part and make full IP-Address for display purpose or application can perform concat operation.

This is about the best way to store IP-Address into database system.

Other Related Articles :

Anvesh Patel

Leave a Reply

2 Comments on "Best way to store IP Address into Database System"

Notify of
avatar
Sort by:   newest | oldest | most voted
Ron
Guest

Ouch/yuck, using 4 separate fields for an IP address? That’s silly.

Use a single unsigned int field and the inet_ntoa() and inet_aton() functions as needed.

wpDiscuz