利用MySQL的inet_aton()和inet_ntoa()函数存储IP地址的方法分享
2022-11-12 09:25:13
内容摘要
这篇文章主要为大家详细介绍了利用MySQL的inet_aton()和inet_ntoa()函数存储IP地址的方法分享，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术
文章正文
这篇文章主要为大家详细介绍了利用MySQL的inet_aton()和inet_ntoa()函数存储IP地址的方法分享，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！
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: 0mysql> 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: 0mysql> 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)注：关于利用MySQL的inet_aton()和inet_ntoa()函数存储IP地址的方法分享的内容就先介绍到这里，更多相关文章的可以留意
代码注释
作者：喵哥笔记
