bitscn.com
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  duplicates: 0  warnings: 0
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  duplicates: 0  warnings: 0
mysql> select * from jackbillow;
+------------+------+
| ip         | name |
+------------+------+
| 3232235976 | a    |
| 3362004721 | b    |
|  408494875 | c    |
| 1690836502 | d    |
+------------+------+
4 rows in set (0.00 sec)
mysql> select * from jackbillow where ip = inet_aton('192.168.1.200');
+------------+------+
| ip         | name |
+------------+------+
| 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)
当前很多应用都适用字符串char(15)来存储ip地址（占用16个字节），利用inet_aton()和inet_ntoa()函数，来存储ip地址效率很高，适用unsigned int 就可以满足需求，不需要使用bigint，只需要4个字节，节省存储空间，同时效率也高很多。
如果ip列有索引，可以使用下面方式查询：
mysql> select inet_aton('100.200.30.22');                            
+----------------------------+
| inet_aton('100.200.30.22') |
+----------------------------+
|                 1690836502 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select * from jackbillow where ip=1690836502;
+------------+------+
| ip         | name |
+------------+------+
| 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') |
+--------------------------+
|               3232235776 |
+--------------------------+
1 row in set (0.00 sec)
mysql> select inet_aton('192.168.1.255');                
+----------------------------+
| inet_aton('192.168.1.255') |
+----------------------------+
|                 3232236031 |
+----------------------------+
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)
bitscn.com