Best way to store IP in database?
PhpMysqlPhp Problem Overview
What is the best field type and length for storing IP addresses in a MySQL database?
What about for IPv6?
Php Solutions
Solution 1 - Php
Store the ip as a INT(11) UNSIGNED, then use the INET_ATON and INET_NTOA functions to store/retrieve the ip address.
Sample code:
INSERT table(ip) VALUES (INET_ATON('192.168.0.1')); /*ip = 3232235521*/
SELECT INET_NTOA(ip) As IPAddress FROM table; /*IPAddress = 192.168.0.1*/
Solution 2 - Php
It depends on what you want to do with it, but probably the simplest way would be to represent it as a string. And https://stackoverflow.com/questions/166132/maximum-length-of-the-textual-representation-of-an-ipv6-address">this question covers how many characters would be required to handle it. (It's 45).
Solution 3 - Php
If you want support both IPv6 and IPv4 store it as BINARY(16) and convert IP address before inserting it to database. For example, in PHP (langugage commonly used with MySQL) you can do this with inet_pton() function.
Solution 4 - Php
IPv6 addresses are 128 bit (16 byte) so you need a field large enough to store that. Also you may need a field to indicate whether or not the IP is IPv4 or IPv6 (::192.168.4.10 in IPv6 is numerically the same as 192.168.4.10 in IPv4, but depending on your application you may need to differentiate between the two).
If you need to store subnets you might want to store the first address, the CIDR mask, and the calculated upper address (broadcast address) of the subnet. This would help in searches so you could do queries like this
SELECT * FROM networks WHERE lowerBound<=MYIP AND upperBound>=MYIP
If you are using subnets you should also calculate that lower bound yourself and not just rely on the user doing it correctly (pseudo code):
lowerBound = AND(networkAddress, subnetMask)
upperBound = OR(lowerBound, complement(subnetMask))
This applies for both IPv4 and IPv6.
Solution 5 - Php
If you plan on searching the database by IP address then an INT would be much faster to query. If you are just storing for display (e.g. in a log output) then a string would be better since you don't need to convert it back and forth.
Solution 6 - Php
I fully agree with Scrum Meister that the best way is to use INT(11) UNSIGNED with storage/retrieval in the INET functions, but sometimes if you're not going to be querying by subnet you may opt for VARCHAR(45)