利用mysql的inet_aton()和inet_ntoa()函数存储IP地址的方
网络编程 2021-07-05 15:27www.168986.cn编程入门
当前很多应用都适用字符串char(15)来存储IP地址(占用16个字节),利用i_aton()和i_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(i_aton('192.168.1.200'), 'A'), (i_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(i_aton('24.89.35.27'), 'C'), (i_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 = i_aton('192.168.1.200');
+------------+------+
| ip | name |
+------------+------+
| 3232235976 | A |
+------------+------+
1 row in set (0.00 sec)
mysql> select i_ntoa(ip) from jackbillow;
+----------------+
| i_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个字节),利用i_aton()和i_ntoa()函数,来存储IP地址效率很高,适用unsigned int 就可以满足需求,不需要使用bigint,只需要4个字节,节省存储空间,效率也高很多。
如果IP列有索引,可以使用狼蚁网站SEO优化方式查询
mysql> select i_aton('100.200.30.22');
+----------------------------+
| i_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 i_ntoa(ip),name from jackbillow where ip=1690836502;
+---------------+------+
| i_ntoa(ip) | name |
+---------------+------+
| 100.200.30.22 | D |
+---------------+------+
1 row in set (0.00 sec)
对于LIKE操作,可以使用狼蚁网站SEO优化方式
mysql> select i_ntoa(ip) from jackbillow;
+----------------+
| i_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 i_aton('192.168.1.0');
+--------------------------+
| i_aton('192.168.1.0') |
+--------------------------+
| 3232235776 |
+--------------------------+
1 row in set (0.00 sec)
mysql> select i_aton('192.168.1.255');
+----------------------------+
| i_aton('192.168.1.255') |
+----------------------------+
| 3232236031 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select i_ntoa(ip) from jackbillow where ip between 3232235776 and 3232236031;
+---------------+
| i_ntoa(ip) |
+---------------+
| 192.168.1.200 |
| 192.168.1.100 |
| 192.168.1.20 |
+---------------+
3 rows in set (0.00 sec)
mysql> select i_ntoa(ip) from jackbillow where ip between i_aton('192.168.1.0') and i_aton('192.168.1.255');
+---------------+
| i_ntoa(ip) |
+---------------+
| 192.168.1.200 |
| 192.168.1.100 |
| 192.168.1.20 |
+---------------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> insert into jackbillow values(i_aton('192.168.1.200'), 'A'), (i_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(i_aton('24.89.35.27'), 'C'), (i_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 = i_aton('192.168.1.200');
+------------+------+
| ip | name |
+------------+------+
| 3232235976 | A |
+------------+------+
1 row in set (0.00 sec)
mysql> select i_ntoa(ip) from jackbillow;
+----------------+
| i_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个字节),利用i_aton()和i_ntoa()函数,来存储IP地址效率很高,适用unsigned int 就可以满足需求,不需要使用bigint,只需要4个字节,节省存储空间,效率也高很多。
如果IP列有索引,可以使用狼蚁网站SEO优化方式查询
mysql> select i_aton('100.200.30.22');
+----------------------------+
| i_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 i_ntoa(ip),name from jackbillow where ip=1690836502;
+---------------+------+
| i_ntoa(ip) | name |
+---------------+------+
| 100.200.30.22 | D |
+---------------+------+
1 row in set (0.00 sec)
对于LIKE操作,可以使用狼蚁网站SEO优化方式
mysql> select i_ntoa(ip) from jackbillow;
+----------------+
| i_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 i_aton('192.168.1.0');
+--------------------------+
| i_aton('192.168.1.0') |
+--------------------------+
| 3232235776 |
+--------------------------+
1 row in set (0.00 sec)
mysql> select i_aton('192.168.1.255');
+----------------------------+
| i_aton('192.168.1.255') |
+----------------------------+
| 3232236031 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select i_ntoa(ip) from jackbillow where ip between 3232235776 and 3232236031;
+---------------+
| i_ntoa(ip) |
+---------------+
| 192.168.1.200 |
| 192.168.1.100 |
| 192.168.1.20 |
+---------------+
3 rows in set (0.00 sec)
mysql> select i_ntoa(ip) from jackbillow where ip between i_aton('192.168.1.0') and i_aton('192.168.1.255');
+---------------+
| i_ntoa(ip) |
+---------------+
| 192.168.1.200 |
| 192.168.1.100 |
| 192.168.1.20 |
+---------------+
3 rows in set (0.00 sec)
编程语言
- 甘肃哪有关键词排名优化购买方式有哪些
- 甘肃SEO如何做网站优化
- 河南seo关键词优化怎么做电话营销
- 北京SEO优化如何做QQ群营销
- 来宾百度关键词排名:提升您网站曝光率的关键
- 卢龙关键词优化:提升您网站排名的策略与技巧
- 山东网站优化的注意事项有哪些
- 四川整站优化怎样提升在搜索引擎中的排名
- 疏附整站优化:提升网站性能与用户体验的全新
- 海南seo主要做什么工作售后服务要做到哪些
- 荣昌百度网站优化:提升您网站的搜索引擎排名
- 河北seo网站排名关键词优化如何做SEO
- 江西优化关键词排名推广售后保障一般有哪些
- 古浪SEO优化:提升你的网站可见性
- 西藏网站排名优化怎么把网站排名在百度首页
- 如何提升阳东百度快照排名:详尽指南