利用mysql的inet_aton（）和inet_ntoa（）函数存储IP地址的方法

法

原⽂：

当前很多应⽤都适⽤字符串char(15)来存储IP地址（占⽤16个字节），利⽤inet_aton()和inet_ntoa()函数，来存储IP地址效率很⾼，适⽤unsigned int 就可以满⾜需求，不需要使⽤bigint，只需要4个字节，节省存储空间，同时效率也⾼很多

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)