Storing IP Address Data In A MySQL Database

/ Misc / by Paul Robinson / 6 Comments
This post was published back on August 26, 2008 and may be outdated. Please use caution when following older tutorials or using older code. After reading be sure to check for newer procedures or updates to code.

Today we are going to take a look at one of the most efficient ways to store IP addresses in a MySQL database.

You may think that you would store an IP address as a VARCHAR(15) field and you could, but this is not the most efficient way to store IPs. Instead you could use an INT. Please remember though that you must use an INT UNSIGNED or IPs with the first octet higher than 127 will not be stored properly.

To use an INT field you must convert the IP address to an integer as you cannot store the periods in an INT field. To do this we use a MySQL command called INET_ATON(). All you need to do is provide an IP address and it will be converted to a numeric value. The IP address may be a 32-bit or 128-bit addresses.

An example command might be this:

If you are just checking the IPs in the database like this:

then you do not need to decode the IP address as you encode the one you are looking for instead, as in the example.

However you may need to decode to address if you need to make reports for something such as an admin that shows who accessed it or you may be making a forum and want to show the IP address of the user to the admin. To decode you use the INET_NTOA() command. The usage is similar to INET_ATON().

If you want to limit your fields for a little more optimisation then the IPv4 encoded length is 32-bit and the IPv6 length is 128-bit. That’s 4 byte and 16 byte.

If you have any questions drop me a comment, and I’ll do what I can to help.


Author’s gravatar


I believe the MySQL field type for storing IPv6 address types is Decimal. Since BIGINT doesn’t have enough storage. Your best bet would probably be DECIMAL(39). I haven’t tested it, but you can give it a try.

Also INET_ATON & INET_NOTA may not work (I think) with IPv6 addresses. I did find this on how to create a INET ATON6 & INET_NOTA6 function.

I hope that helps in some way.

Author’s gravatar

Thank you for the answer.
So for now can I go ahead with storing only IPv4 addresse as IPv6 are very, very rarely used ?

I suppose that for your webiste you do this.

Author’s gravatar

Yep for the minute IPv6 is quite rare. Taking a quick look at my Apache logs I can’t see a IPv6 address. So yeah I think using IPv4 is safe for the moment.

Yes I do use IPv4 for this site.

Author’s gravatar

Depends on what you mean by length. The largest IPv4 address would be to assume, that would be the largest address in chars & number. That would may not be possible as it’s a subnet mask but it would probably be best to assume that IP regardless.

Older Comments
Newer Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

I'll keep your WordPress site up-to-date and working to its best.

Find out more