How MySQL stores IP values

Storing IP values

IP values ​​are generally stored using char or varchar, but when performing searches and statistics, character types are not very efficient. MySQL database has two built-in IP-related functions INET_ATON() and INET_NTOA(), which can convert IP addresses and integer types. After conversion, INT UNSIGNED can be used to store the IP. The converted numbers are continuous, which improves query performance and takes up less space.

CREATE TABLE `tb_ip` (
  `increment_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL COMMENT '姓名',
    `inet_ip` int(10) unsigned NOT NULL COMMENT 'IP',
  PRIMARY KEY (`increment_id`),
  KEY `idx_inet_ip` (`inet_ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ip测试表';
# 插入数据
insert into `tb_ip` (`name`,`inet_ip`) values 
('wang',INET_ATON('192.168.0.1')),('lisi',INET_ATON('192.168.0.2'));
# 相互转换
select INET_ATON('192.168.0.1');
select INET_NTOA(3232235521);

The above is the detailed content of How MySQL stores IP values. For more information, please follow other related articles on the PHP Chinese website!