Mysql必读利用mysql的inet_aton()和inet_ntoa()函数存储IP地址的方法分享

概述介绍《Mysql必读利用mysql的inet_aton()和inet_ntoa()函数存储IP地址的方法分享》开发教程，希望对您有用。

@H_301_1@《MysqL必读利用MysqL的inet_aton()和inet_ntoa()函数存储IP地址的方法分享》要点：
本文介绍了MysqL必读利用MysqL的inet_aton()和inet_ntoa()函数存储IP地址的方法分享，希望对您有用。如果有疑问，可以联系我们。

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) 总结

以上是内存溢出为你收集整理的Mysql必读利用mysql的inet_aton()和inet_ntoa()函数存储IP地址的方法分享全部内容，希望文章能够帮你解决Mysql必读利用mysql的inet_aton()和inet_ntoa()函数存储IP地址的方法分享所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。