原理

相对字符串存储，使用无符号整数来存储有如下的好处：

  • 节省空间（数据存储空间、索引存储空间）
  • 便于使用范围查询（BETWEEN…AND）且效率更高

通常，在保存IPv4地址时，一个IPv4最小需要7个字符，最大需要15个字符，所以，使用VARCHAR(15)即可。MySQL在保存变长的字符串时，还需要额外的一个字节来保存此字符串的长度。而如果使用无符号整数来存储，只需要4个字节即可。

另外还可以使用4个字段分别存储IPv4中的各部分，但是通常这不管是存储空间和查询效率应该都不是很高（可能有的场景适合使用这种方式存储）。

使用无符号整数来存储也有缺点：

  • 不便于阅读
  • 插入查询需要转换类型

对于转换来说，MySQL提供了相应的函数来把字符串格式的IP转换成整数INET_ATON，以及把整数格式的IP转换成字符串的INET_NTOA。如下所示：

  1. mysql> select inet_aton('192.168.0.1');
  2. +--------------------------+
  3. | inet_aton('192.168.0.1') |
  4. +--------------------------+
  5. |               3232235521 |
  6. +--------------------------+
  7. 1 row in set (0.00 sec)
  9. mysql> select inet_ntoa(3232235521);
  10. +-----------------------+
  11. | inet_ntoa(3232235521) |
  12. +-----------------------+
  13. | 192.168.0.1           |
  14. +-----------------------+
  15. 1 row in set (0.00 sec)

测试

创建测试表：

  1. drop table if exists test_ip;
  2. create table test_ip
  3. (
  4.     ip int unsigned
  5. );

插入数据时要将IP字符串转换为整数：

  1. insert into test_ip (ip)
  2. values (inet_aton('192.168.1.1')),
  3.        (inet_aton('192.168.1.2')),
  4.        (inet_aton('192.168.1.3')),
  5.        (inet_aton('192.168.1.4'));

插入后是这样的：

  1. mysql> select * from test_ip;
  2. +------------+
  3. | ip         |
  4. +------------+
  5. | 3232235777 |
  6. | 3232235778 |
  7. | 3232235779 |
  8. | 3232235780 |
  9. +------------+

查询数据使用inet_ntoa函数将整数转换为IP字符串：

  1. mysql> select inet_ntoa(ip) from test_ip;
  2. +---------------+
  3. | inet_ntoa(ip) |
  4. +---------------+
  5. | 192.168.1.1   |
  6. | 192.168.1.2   |
  7. | 192.168.1.3   |
  8. | 192.168.1.4   |
  9. +---------------+

可以使用大于、小于、between实现高效的范围查询：

  1. select inet_ntoa(ip) from test_ip
  2. where ip > inet_aton('192.168.1.2');
  4. select inet_ntoa(ip) from test_ip
  5. where ip between inet_aton('192.168.1.2') and inet_aton('192.168.1.4');