Which MySQL datatype to use for an IP address?

04/19/2020 19:00:01

Possible Duplicate:
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?

Is it VARCHAR(n)?

Verified Answer (139 Votes)

02/27/2011 19:00:29

Since IPv4 addresses are 4 byte long, you could use an INT (UNSIGNED) that has exactly 4 bytes:

`ipv4` INT UNSIGNED

And INET_ATON and INET_NTOA to convert them:

INSERT INTO `table` (`ipv4`) VALUES (INET_ATON("127.0.0.1"));
SELECT INET_NTOA(`ipv4`) FROM `table`;

For IPv6 addresses you could use a BINARY instead:

`ipv6` BINARY(16)

And use PHP’s inet_pton and inet_ntop for conversion:

'INSERT INTO `table` (`ipv6`) VALUES ("'.mysqli_real_escape_string(inet_pton('2001:4860:a005::68')).'")'
'SELECT `ipv6` FROM `table`'
$ipv6 = inet_pton($row['ipv6']);
139

Answer #2 (47 Votes)

02/27/2011 18:57:12

You have two possibilities (for an IPv4 address) :

  • a varchar(15), if your want to store the IP address as a string
    • 192.128.0.15 for instance
  • an integer (4 bytes), if you convert the IP address to an integer
    • 3229614095 for 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).

About those manipulations, see the ip2long() and long2ip() functions, on the PHP-side, or inet_aton() and inet_ntoa() on the MySQL-side.

47

Answer #3 (5 Votes)

02/27/2011 18:57:57

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 BETWEEN queries

INET_ATON() MySQL function

5
3
Hack Hex uses Stack Exchance API by the Stack Exchange Inc. to scrape questions/answers under Creative Commons license.