Mysql 如何高效存储IPv4 地址

在MySQL存储数据时，我们需要优先考虑数据的类型和数据容量以及最重要的是数据使用时的性能问题。

所以我们应该尽量选择可以正确存储数据的最小数据类型。最小的数据类型通常占用更少的磁盘、内存和CPU缓存，而且处理时需要的CPU周期也更少。

人们经常使用VARCHAR(15) 来存储IP 地址，然而，它们实际是32位无符号整数，不是字符串。用小数点分成4段表示方法只是为了阅读容易。所以应该用无符号整数存储IP地址。

以下内容采自《MySQL高性能书籍》第四章节

MySQL中整型有很多种: TINYINT , SMALLINT , MEDIUMINT, INT ,BIGINT 。 分别使用8 ，16 ，24，32，64 位存储空间。可以存储的值的范围从-2^(N-1) 到 2^(N-1)-1 ,其中N是存储空间的位数。

整数类型有可选的UNSIGNED 属性，表示不允许负值，这可以使得整数的上限提高一倍。例如 TINYINT  UNSIGNED可以存储的范围是0~255 ， 而 TINYINT的存储范围是 -128 ~127 

有符号和无符号类型使用的相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型。

整数计算一般使用64位的bigint 整数，即时是在32位环境也是这样。

MySQL可以为整数类型指定宽度，例如 INT(11)， 这对大多数应用是没有意义的。这是不会限制值的合法范围，只是规定了MYSQL 的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，INT(1) 和 INT(20) 是相同的。

• MySQL对于IP的存储提供了两个函数

• -- ADDRESS TO NUM
  SELECT INET_ATON('192.168.0.1');
  -- NUM TO ADDRESS
  SELECT INET_NTOA(3232235521);

• 举个实际业务例子，脚本如下

-- CREATE TABLE
DROP TABLE t_ip;
CREATE TABLE `t_ip` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(15) NOT NULL COMMENT 'IP地址',
  `ip_address_num` bigint(1) NOT NULL COMMENT 'IP NUM',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8  ;


-- INSERT DATA
INSERT INTO t_ip(ip_address , ip_address_num) 
VALUES
( '192.168.0.1' , INET_ATON('192.168.0.1')) ,
( '192.168.0.2' , INET_ATON('192.168.0.2')) ,
( '192.168.0.3' , INET_ATON('192.168.0.3')) ,
( '192.168.0.4' , INET_ATON('192.168.0.4')) ,
( '192.168.0.5' , INET_ATON('192.168.0.5')) ,
( '192.168.0.6' , INET_ATON('192.168.0.6')) ,
( '192.168.0.7' , INET_ATON('192.168.0.7')) ,
( '192.168.0.8' , INET_ATON('192.168.0.8')) ,
( '12.68.0.1' , INET_ATON('12.68.0.1')) ,
( '12.68.0.2' , INET_ATON('12.68.0.2')) ,
( '12.68.0.3' , INET_ATON('12.68.0.3')) ,
( '12.68.0.4' , INET_ATON('12.68.0.4')) ,
( '12.68.0.5' , INET_ATON('12.68.0.5')) ,
( '12.68.0.6' , INET_ATON('12.68.0.6')) ,
( '12.68.0.7' , INET_ATON('12.68.0.7')) ,
( '12.68.0.8' , INET_ATON('12.68.0.8'));

-- DEMO SELECT
SELECT *,INET_NTOA(ip_address_num) AS FORMATIP FROM t_ip 
WHERE ip_address_num > INET_ATON('192.168.0.3') 
AND ip_address_num < INET_ATON('192.168.0.10');

• 运行结果