Which MySQL datatype to use for an IP address? [duplicate]

Question

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)?

Answer 1

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']); 

Answer 2

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.