利用mysql的inet_aton()和inet_ntoa()函数存储IP地址的方法分享
来源:互联网 发布:新浪财经数据下载 编辑:程序博客网 时间:2024/05/18 16:14
当前很多应用都适用字符串char(15)来存储IP地址(占用16个字节),利用inet_aton()和inet_ntoa()函数,来存储IP地址效率很高,适用unsigned int就可以满足需求,不需要使用bigint,只需要4个字节,节省存储空间,同时效率也高很多
mysql> create table jackbillow (ip int unsigned, namechar(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 whereip=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 between3232235776 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 betweeninet_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> create table jackbillow (ip int unsigned, namechar(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 whereip=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 between3232235776 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 betweeninet_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)
0 0
- 利用mysql的inet_aton()和inet_ntoa()函数存储IP地址的方法分享
- 利用mysql的inet_aton()和inet_ntoa()函数存储IP地址的方法分享
- 利用mysql的inet_aton()和inet_ntoa()函数存储IP地址
- 利用mysql的inet_aton()和inet_ntoa()函数存储IP地址
- 利用mysql的inet_aton()和inet_ntoa()函数存储IP地址
- 利用mysql的inet_aton()和inet_ntoa()函数存储IP地址
- mysql inet_aton和inet_ntoa存储ip地址
- MySQL的IP存储 INET_ATON INET_NTOA
- Mysql的IP处理函数inet_aton()和inet_ntoa()
- Mysql的IP处理函数inet_aton()和inet_ntoa()
- Mysql的IP处理函数inet_aton()和inet_ntoa()
- [MySQL]IP处理函数inet_aton()和inet_ntoa()
- mysql 使用inet_aton和inet_ntoa处理ip地址数据
- mysql 使用inet_aton和inet_ntoa处理ip地址数据
- mysql 使用inet_aton和inet_ntoa处理ip地址数据
- mysql利用INET_ATON或INET_NTOA比较ip
- IP处理函数inet_aton()和inet_ntoa()
- IP处理函数inet_aton()和inet_ntoa()
- in与exist , not in与not exist 的区别
- MySQL中SYSDATE()和NOW()的区别
- 利用memcached构建高性能的Web应用程序(转)
- Sql2005数据库的复制
- MySQL 性能优化的经验分享
- 利用mysql的inet_aton()和inet_ntoa()函数存储IP地址的方法分享
- Memcached在Asp.NET中的使用
- MySQL 之 tinyint、smallint、mediumint、int、bigint 的 unsigned
- 01-Remoting之序列化(BinaryFormatter,SoapFormatter,XmlSerializer)
- 查看mysql数据库连接数、并发数相…
- 关闭窗口前弹出对话框
- Mysql学习之路——day1
- WinService的安装与卸载
- 关于open函数的在不创建新文件时,打开不存在的文件的测试