Mysql必读利用mysql的inet_aton()和inet_ntoa()函数存储IP地址的
发布时间:2020-12-12 01:00:04 所属栏目:MySql教程 来源:网络整理
导读:《Mysql必读利用mysql的inet_aton()和inet_ntoa()函数存储IP地址的方法分享》要点: 本文介绍了Mysql必读利用mysql的inet_aton()和inet_ntoa()函数存储IP地址的方法分享,希望对您有用。如果有疑问,可以联系我们。 mysql create table jackbillow (ip int u
《Mysql必读利用mysql的inet_aton()和inet_ntoa()函数存储IP地址的方法分享》要点: 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) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |