利用mysql的inet_aton()和inet_ntoa()函数存储IP地址
mysql> create table jackbillow (ip int unsigned, name char(1));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into jackbillow values(inet_aton('192.168.1.200'), 'A'), (inet_aton('200.100.30.241'), 'B');
Query OK, 2 rows affected (0.00 sec)
Records: 2
mysql> insert into jackbillow values(inet_aton('24.89.35.27'), 'C'), (inet_aton('100.200.30.22'), 'D');
Query OK, 2 rows affected (0.00 sec)
Records: 2
mysql> select * from jackbillow;
+------------+------+
| ip
+------------+------+
| 3232235976 | A
| 3362004721 | B
|
| 1690836502 | D
+------------+------+
4 rows in set (0.00 sec)
mysql> select * from jackbillow where ip = inet_aton('192.168.1.200');
+------------+------+
| ip
+------------+------+
| 3232235976 | A
+------------+------+
1 row in set (0.00 sec)
mysql> select inet_ntoa(ip) from jackbillow;
+----------------+
| inet_ntoa(ip)
+----------------+
| 192.168.1.200
| 200.100.30.241 |
| 24.89.35.27
| 100.200.30.22
+----------------+
4 rows in set (0.00 sec)
如果IP列有索引，可以使用下面方式查询：
mysql> select inet_aton('100.200.30.22');
+----------------------------+
| inet_aton('100.200.30.22') |
+----------------------------+
|
+----------------------------+
1 row in set (0.00 sec)
mysql> select * from jackbillow where ip=1690836502;
+------------+------+
| ip
+------------+------+
| 1690836502 | D
+------------+------+
1 row in set (0.00 sec)
mysql> select inet_ntoa(ip),name from jackbillow where ip=1690836502;
+---------------+------+
| inet_ntoa(ip) | name |
+---------------+------+
| 100.200.30.22 | D
+---------------+------+
1 row in set (0.00 sec)
对于LIKE操作，可以使用下面方式：
mysql> select inet_ntoa(ip) from jackbillow;
+----------------+
| inet_ntoa(ip)
+----------------+
| 192.168.1.200
| 200.100.30.241 |
| 24.89.35.27
| 100.200.30.22
| 192.168.1.100
| 192.168.1.20
| 192.168.2.20
+----------------+
7 rows in set (0.00 sec)
mysql> select inet_aton('192.168.1.0');
+--------------------------+
| inet_aton('192.168.1.0') |
+--------------------------+
|
+--------------------------+
1 row in set (0.00 sec)
mysql> select inet_aton('192.168.1.255');
+----------------------------+
| inet_aton('192.168.1.255') |
+----------------------------+
|
+----------------------------+
1 row in set (0.00 sec)
mysql> select inet_ntoa(ip) from jackbillow where ip between 3232235776 and 3232236031;
+---------------+
| inet_ntoa(ip) |
+---------------+
| 192.168.1.200 |
| 192.168.1.100 |
| 192.168.1.20
+---------------+
3 rows in set (0.00 sec)
mysql> select inet_ntoa(ip) from jackbillow where ip between inet_aton('192.168.1.0') and inet_aton('192.168.1.255');
+---------------+
| inet_ntoa(ip) |
+---------------+
| 192.168.1.200 |
| 192.168.1.100 |
| 192.168.1.20
+---------------+
3 rows in set (0.00 sec)
整型字段的比较比字符串效率高很多，这也符合一项优化原则：字段类型定义使用最合适（最小），最简单的数据类型。
a.b.c.d 的ip number是：
a *
