[大数据]mysql 存储ip 且 ip 分段

unsigned 重要

CREATE TABLE order (
id int(20) NOT NULL AUTO_INCREMENT,
ip int(10) unsigned DEFAULT NULL COMMENT ‘数量’,
iphou int(10) unsigned DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

INSERT INTO user (id, name, ip) VALUES
(2, ‘Abby’, inet_aton(‘192.168.1.1’)),
(3, ‘Daisy’, inet_aton(‘172.16.11.66’)),
(4, ‘Christine’, inet_aton(‘220.117.131.12’));

inet_aton 必须是现在 新增函数

insert into order (id, ip, iphou) values(‘1’,‘3232235777’,‘3232235777’);
insert into order (id, ip, iphou) values(‘3’,‘2886732610’,‘2886732610’);
insert into order (id, ip, iphou) values(‘4’,‘3698688780’,‘3698690585’);

SELECT INET_ATON(‘220.117.138.25’)

SELECT id,INET_NTOA(ip) AS ip,INET_NTOA(iphou) AS iphou FROM order WHERE ip<=INET_ATON(‘220.117.138.1’) AND iphou>=INET_ATON(‘220.117.138.1’);

数据库存储 10位整数。