MySQL inet aton函数,MySQL IP转数字函数 INET_ATON() INET_NTOA()

223 total views, 1 views today

在MySQL中IP地址可以使用varchar字段类型存储，但是若查找某个IP区间的IP地址就很麻烦。

但是通过 INET_ATON() 函数将IP地址转换为数字(二进制数字格式)就能实现IP地址比较的功能。

inet_aton() converts the Internet host address cp from the IPv4 numbers-and-dots notation into binary form

INET_ATON() 将IP地址转换为二进制。 ATON= address to number (个人猜测，为了好记)。

mysql> SELECT inet_aton('10.0.2.103');

+-------------------------+

| inet_aton('10.0.2.103') |

+-------------------------+

| 167772775 |

+-------------------------+

1 row in set (0.00 sec)

1

2

3

4

5

6

7

mysql>SELECTinet_aton('10.0.2.103');

+-------------------------+

|inet_aton('10.0.2.103')|

+-------------------------+

|167772775|

+-------------------------+

1rowinset(0.00sec)

INET_NTOA() 将二进制转为IP地址。NTOA = number to address。

mysql> SELECT INET_NTOA(167772775);

+----------------------+

| INET_NTOA(167772775) |

+----------------------+

| 10.0.2.103 |

+----------------------+

1 row in set (0.00 sec)

1

2

3

4

5

6

7

mysql>SELECTINET_NTOA(167772775);

+----------------------+

|INET_NTOA(167772775)|

+----------------------+

|10.0.2.103|

+----------------------+

1rowinset(0.00sec)

例子：使用 inet_aton()函数将IP地址转换成数字存入表中，然后查找某个范围内的IP地址

创建表并存入转换后的IP地址

create table tab_ips(ip_address int);

insert into tab_ips values(inet_aton('10.0.2.99'));

insert into tab_ips values(inet_aton('10.0.2.100'));

insert into tab_ips values(inet_aton('10.0.2.101'));

insert into tab_ips values(inet_aton('10.0.2.103'));

insert into tab_ips values(inet_aton('10.0.2.104'));

select * from tab_ips;

+------------+

| ip_address |

+------------+

| 167772771 |

| 167772772 |

| 167772773 |

| 167772775 |

| 167772776 |

+------------+

5 rows in set (0.00 sec)

转成IP地址

select inet_ntoa(ip_address) from tab_ips;

+-----------------------+

| inet_ntoa(ip_address) |

+-----------------------+

| 10.0.2.99 |

| 10.0.2.100 |

| 10.0.2.101 |

| 10.0.2.103 |

| 10.0.2.104 |

+-----------------------+

5 rows in set (0.00 sec)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

createtabletab_ips(ip_addressint);

insertintotab_ipsvalues(inet_aton('10.0.2.99'));

insertintotab_ipsvalues(inet_aton('10.0.2.100'));

insertintotab_ipsvalues(inet_aton('10.0.2.101'));

insertintotab_ipsvalues(inet_aton('10.0.2.103'));

insertintotab_ipsvalues(inet_aton('10.0.2.104'));

select *fromtab_ips;

+------------+

|ip_address|

+------------+

|167772771|

|167772772|

|167772773|

|167772775|

|167772776|

+------------+

5rowsinset(0.00sec)

转成IP地址

selectinet_ntoa(ip_address)fromtab_ips;

+-----------------------+

|inet_ntoa(ip_address)|

+-----------------------+

|10.0.2.99|

|10.0.2.100|

|10.0.2.101|

|10.0.2.103|

|10.0.2.104|

+-----------------------+

5rowsinset(0.00sec)

查找在 10.0.2.99 与 10.0.2.103之间的IP地址

select inet_ntoa(ip_address) from tab_ips where ip_address>inet_aton('10.0.2.99') and ip_address

+-----------------------+

| inet_ntoa(ip_address) |

+-----------------------+

| 10.0.2.100 |

| 10.0.2.101 |

+-----------------------+

2 rows in set (0.02 sec)

1

2

3

4

5

6

7

8

selectinet_ntoa(ip_address)fromtab_ipswhereip_address>inet_aton('10.0.2.99')andip_address

+-----------------------+

|inet_ntoa(ip_address)|

+-----------------------+

|10.0.2.100|

|10.0.2.101|

+-----------------------+

2rowsinset(0.02sec)