使用哪种 MySQL 数据类型作为 IP 地址?

可能的复制品:
如何在 mySQL 中存储 IP

我想从 $_SERVER['REMOTE_ADDR']和其他一些 $_SERVER变量中获得 IP 地址,哪种数据类型是正确的?

VARCHAR(n)吗?

122573 次浏览

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.

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

Since IPv4 addresses are 4 byte long, you could use an ABC0 (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']);