Which MySQL datatype to use for an IP address?
How to store an IP in mySQL
I want to get the IP address from
$_SERVER['REMOTE_ADDR'] and some other
$_SERVER variables, which datatype is the right one for this?
Verified Answer (139 Votes) ✓
Since IPv4 addresses are 4 byte long, you could use an
UNSIGNED) that has exactly 4 bytes:
`ipv4` INT UNSIGNED
INSERT INTO `table` (`ipv4`) VALUES (INET_ATON("127.0.0.1")); SELECT INET_NTOA(`ipv4`) FROM `table`;
For IPv6 addresses you could use a
'INSERT INTO `table` (`ipv6`) VALUES ("'.mysqli_real_escape_string(inet_pton('2001:4860:a005::68')).'")' 'SELECT `ipv6` FROM `table`' $ipv6 = inet_pton($row['ipv6']);
Answer #2 (47 Votes)
You have two possibilities (for an IPv4 address) :
varchar(15), if your want to store the IP address as a string
integer(4 bytes), if you convert the IP address to an integer
3229614095for the IP I used before
The second solution will require less space in the database, and is probably a better choice, even if it implies a bit of manipulations when storing and retrieving the data (converting it from/to a string).
Answer #3 (5 Votes)
For IPv4 addresses, you can use VARCHAR to store them as strings, but also look into storing them as long integesrs
INT(11) UNSIGNED. You can use MySQL's
INET_ATON() function to convert them to integer representation. The benefit of this is it allows you to do easy comparisons on them, like