Mysql 中存储IP地址

本文介绍IP字段在mysql数据表中的优化。 创建测试表 mysql create table hosts( - `id` int(8) not null auto_increment, - `ip` int unsigned not null, - `hostname` varchar(30) not null, - `desc` varchar(100) , - PRIMARY KEY(`id`)) ENGINE=InnoDB;Q

本文介绍IP字段在mysql数据表中的优化。

创建测试表

mysql> create table hosts( 
 -> `id` int(8) not null auto_increment,
 -> `ip` int unsigned not null,
 -> `hostname` varchar(30) not null,
 -> `desc` varchar(100) , 
 -> PRIMARY KEY(`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

查看表结构

mysql> desc hosts
 -> ;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| ip | int(10) unsigned | NO | | NULL | |
| hostname | varchar(30) | NO | | NULL | |
| desc | varchar(100) | YES | | NULL | |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

插入测试数据

mysql> insert into hosts (`ip`,`hostname`,`desc`) values (inet_aton('192.168.1.106'),'test','this is test host.');
Query OK, 1 row affected (0.03 sec)

mysql> select * from hosts
 -> ;
+----+------------+----------+--------------------+
| id | ip | hostname | desc |
+----+------------+----------+--------------------+
| 1 | 3232235882 | test | this is test host. |
+----+------------+----------+--------------------+
1 row in set (0.00 sec)

查询

mysql> select inet_ntoa(ip) from hosts;
+---------------+
| inet_ntoa(ip) |
+---------------+
| 192.168.1.106 |
+---------------+
1 row in set (0.00 sec)

原文地址：Mysql 中存储IP地址, 感谢原作者分享。