今天在项目开发时，遇到需要在数据库中存储 ip 地址，那么应该选用何种数据类型更加高效呢？
如果存储的是IPV4地址，可以选择使用INT UNSIGNED，然后借助 MySQL 自带的 INET_ATON() 和 INET_NTOA()来存取数据；
如果存储的是IPV6地址，可以选择使用VARBINARY()，然后借助 INET6_ATON()和INET6_NTOA() (MySQL5.6+支持)方法存取数据。

针对IPv4地址

mysql> select inet_aton('127.0.0.1');
+------------------------+
| inet_aton('127.0.0.1') |
+------------------------+
|             2130706433 |
+------------------------+
1 row in set (0.00 sec)

mysql> select inet_ntoa(2130706433);
+-----------------------+
| inet_ntoa(2130706433) |
+-----------------------+
| 127.0.0.1             |
+-----------------------+
1 row in set (0.01 sec)

数据存取

  • 存数据

    INSERT INTO `ip_addresses` (`ip_address`)
    VALUES (INET_ATON('127.0.0.1'));

  • 取数据

    SELECT id, INET_NTOA(`ip_address`) AS ip
    FROM `ip_addresses`;

    Python实现

对于上面的代码，如果我们不想使用内置的MySQL方法，也可以在应用层使用自己封装的方法：

import socket, struct
def ip2long(ip):  
    return struct.unpack("!L",socket.inet_aton(ip))[0]  
def long2ip(longip):  
    return socket.inet_ntoa(struct.pack('!L', longip))  
if __name__ == '__main__':  
    print('local ip address to long is %s'%ip2long('127.0.0.1'))  
    print('local ip address to long is %s'%ip2long('255.255.255.255'))  
    print('local ip address long to ip is %s'%long2ip(2130706433))  
    print('local ip address long to ip is %s'%long2ip(4294967295))

更新

我们还可以使用 ipaddress 库对 ip 地址进行 int 和 str 之间的互转。代码如下：

>>> import ipaddress

>>> int(ipaddress.ip_address('127.0.0.1'))
2130706433
>>> str(ipaddress.ip_address(2130706433))
'127.0.0.1'

针对IPv6地址

MySQL 提供内置函数inet6_aton()来存储和检索IPv6地址。敲黑板，不要把IPv6地址存储为整数，因为数字格式的IPv6地址需要比UNSIGNED BIGINT更多的字节。所以下面的函数返回VARBINARY(16)数据类型。让我们看一个例子。

mysql> select hex(inet6_aton('127.0.0.1'));
+---------------------------------+
| hex(inet6_aton('127.0.0.1'))    |
+---------------------------------+
| 7F000001                        |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select hex(inet6_aton('2001:0db8:85a3:0000:0000:8a2e:0370:7334'));
+---------------------------------------------------------------------------+
| hex(inet6_aton('2001:0db8:85a3:0000:0000:8a2e:0370:7334'))                |
+---------------------------------------------------------------------------+
| 20010DB885A3000000008A2E03707334                                          |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select inet6_ntoa(unhex('20010DB885A3000000008A2E03707334'));
+----------------------------------------------------------------------------+
| inet6_ntoa(unhex('20010DB885A3000000008A2E03707334'))                      |
+----------------------------------------------------------------------------+
| 2001:db8:85a3::8a2e:370:7334                                               |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

注意

假设你正在编写查找以ip地址为127.0.0.1连接的用户，可能写出如下的查询语句：

SELECT name FROM user WHERE inet_ntoa(ipaddress)='127.0.0.1';

请注意，此查询不会使用在ipaddress列上创建的索引，因为我们在SQL执行期间修改了索引列，它也会逐行将整数转换为真实的IP地址。所以要想让索引生效应该：

SET @ip = inet_aton('127.0.0.1');
SELECT name FROM user WHERE ipaddress = @ip;

或者
SELECT name FROM user WHERE ipaddress = inet_aton('127.0.0.1');

参考链接