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 se
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)
