当前位置:七道奇文章资讯数据防范MySQL防范
日期:2012-06-02 15:05:00  来源:本站整理

操纵mysql的inet_aton()和inet_ntoa()函数存储IP地址的办法分享[MySQL防范]

赞助商链接



  本文“操纵mysql的inet_aton()和inet_ntoa()函数存储IP地址的办法分享[MySQL防范]”是由七道奇为您精心收集,来源于网络转载,文章版权归文章作者所有,本站不对其观点以及内容做任何评价,请读者自行判断,以下是其具体内容:
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 se
  以上是“操纵mysql的inet_aton()和inet_ntoa()函数存储IP地址的办法分享[MySQL防范]”的内容,如果你对以上该文章内容感兴趣,你可以看看七道奇为您推荐以下文章:
  • 操纵MySQL加密函数保护Web网站敏感数据的办法分享
  • 操纵mysql的inet_aton()和inet_ntoa()函数存储IP地址的办法分享
  • 若何操纵MySQL加密函数保护网站敏感数据
  • 操纵MySQL加密函数保护Web网站敏感数据
  • <b>若何操纵MySQL加密函数保护Web网站敏感数据</b>
  • 本文地址: 与您的QQ/BBS好友分享!
    • 好的评价 如果您觉得此文章好,就请您
        0%(0)
    • 差的评价 如果您觉得此文章差,就请您
        0%(0)

    文章评论评论内容只代表网友观点,与本站立场无关!

       评论摘要(共 0 条,得分 0 分,平均 0 分) 查看完整评论
    Copyright © 2020-2022 www.xiamiku.com. All Rights Reserved .