• 利用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)

  • 相关阅读:
    微信分享 apicloud方式 中遇到的坎
    css之颜色篇
    css总结
    记一些茅塞顿开的事情
    apicloud
    安装MySQL
    智能家居
    java
    数据库设计好不好,分配很重要。
    WP8.1的shell:SystemTray去哪了?
  • 原文地址:https://www.cnblogs.com/you-jia/p/4615100.html
Copyright © 2020-2023  润新知