Storing IP address in MySQL database (IPv4 AND IPv6)
Solution 1
To store an IPv4 you can use an INT UNSIGNED
, while for a IPv6 you need a decimal(39,0),
to store an ip in the table you can use the function INET_ATON
:
INSERT INTO table (ipcol) VALUES (INET_ATON('192.168.0.10'));
and retrieve it back with the function INET_NTOA
:
SELECT INET_NTOA(ipcol) AS ip FROM table;
This answered existing before MySQL IPv6 support; users should be made aware that MySQL now natively supports IPv6: https://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html
Solution 2
I use VARBINARY(16) for the data type and use the MySQL function INET_ATON()
to insert the IP number (which I later read using the reverse function, INET_NTOA()
.
Solution 3
There is no integral type in MySQL big enough to store an IPv6 address. The most compact way to store it is as something like BINARY(16)
. If you just need to store and retrieve addresses and you don't need to perform logical operations on them (e.g. netmask operations to query for which IP addresses come under a covering prefix) then that will be enough. If you need to do logical or bit operations, you will need to be fancier: you will need to store IPv6 addresses in two separate 64-bit integer columns.
Andy
A teenager with a passion for all things computers! I have cerebral palsy so computers have been my best, and I've got the most out of them. I've taught myself AutoIt, Java, and a bit of most web languages, including PHP and HTML, as now own about six domains and I'm in the process of starting my own business. I don't confess to know everything about anything and I know I have A LOT to learn but I do what I can and try to help out where I can. I think I'm the biggest 'Guru' of the family!
Updated on June 03, 2022Comments
-
Andy almost 2 years
Ok, now I'm aware that similar questions have probably been asked a million times but I'm a real novice at this and I'd really appreciate your help here.
Basically, I want to store the visitors IP address in a MySQL for later retrieval and verification. I firstly need to know what type of field I need to use to store the IP address in. I would also like to make the system compatible with IPv6 addresses if possible.
Thanks in advance