Storing IP Address Data In A MySQL Database
Sorry about the recent problems with the site. I had quite a large database crash before I moved the site over to the new host which caused a lot of posts to be lost. I do have a backup, but I have decided to make a fresh start. I did however use the backup to [...]
Sorry about the recent problems with the site. I had quite a large database crash before I moved the site over to the new host which caused a lot of posts to be lost. I do have a backup, but I have decided to make a fresh start. I did however use the backup to rewrite the most popular posts from the old site.
Right, on to the post. I have decided to add a fun new section to Celeb O Rama. It’s going to be a classic Hot or Not poll where you can vote on a new celebrity every day. One problem I had was restricting the voting to once per day. The easiest way is to record the users IP address in a database, and then check for their IP address if they try to vote again.
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 numbers 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:
[mysql]
INSERT INTO ip_log (ip) VALUES(INET_ATON(’127.0.0.1′));
[/mysql]
If you are just checking the IPs in the database like this:
[mysql]
SELECT ip FROM ip_log WHERE ip = INET_ATON(’127.0.0.1′);
[/mysql]
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().
[mysql]
SELECT INET_NTOA( ip ) FROM ip_log
[/mysql]
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.
Discussion: 6 Comments » add a comment
Hi,
Very clear your article, I was just looking for ways to store IP addresses in a MySQL database.
Here
http://bafford.com/2009/03/09/mysql-performance-benefits-of-storing-integer-ip-addresses/
it says
“(Storing IPv6 addresses in the database is going to be a bit more difficult, as MySQL doesn’t have a native 16-byte-wide data type.)”
So what is the actual type/length required in the db ?
INT or BIGINT ?
Thank you
Hi,
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.
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.
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.
great article, what is the maximum length for an ip address, thanks, Sky Cards
Depends on what you mean by length. The largest IPv4 address would be to assume 255.255.255.255, 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.