利用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)
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)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29096438/viewspace-1872857/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 如何利用MySQL有效的儲存IP地址MySql
- MySQL如何有效的儲存IP地址MySql
- Mysql 的儲存過程和儲存函式MySql儲存過程儲存函式
- mysql和orcale的儲存過程和儲存函式MySql儲存過程儲存函式
- php呼叫mysql儲存過程和函式的方法(轉)PHPMySql儲存過程函式
- IP地址轉換函式——inet_pton inet_ntop inet_aton inet_addr inet_ntoa函式
- MySQL 儲存過程和函式MySql儲存過程函式
- MySQL儲存過程和函式MySql儲存過程函式
- mysql儲存函過程和儲存函式都屬於儲存程式MySql儲存函式
- MySQL入門--儲存過程(PROCEDURE)和儲存函式(FUNCTION)MySql儲存過程儲存函式Function
- 網路地址處理函式用法inet_aton inet_ntoa inet_addr inet_network函式
- MySQL 5.5 建立儲存過程和函式MySql儲存過程函式
- MySQL4:儲存過程和函式MySql儲存過程函式
- (9)mysql 中的儲存過程和自定義函式MySql儲存過程函式
- MySQL 儲存函式及呼叫MySql儲存函式
- 函式索引的儲存函式索引
- mysql儲存過程procedure、函式function的用法MySql儲存過程函式Function
- MySQL儲存過程 (即函式)MySql儲存過程函式
- mySql 儲存過程與函式MySql儲存過程函式
- 儲存過程和函式的區別儲存過程函式
- MySQL建立隨機生成電話的儲存函式MySql隨機儲存函式
- mysql儲存過程基本函式(轉)MySql儲存過程函式
- 我的MYSQL學習心得(10) : 自定義儲存過程和函式MySql儲存過程函式
- 【MySQL】MySQL(三)儲存過程和函式、觸發器、事務MySql儲存過程函式觸發器
- 儲存函式儲存函式
- MySQL優化---儲存過程和儲存函式-1-轉自部落格園MySql優化儲存過程儲存函式
- mysql儲存地理資訊的方法MySql
- MySQL入門系列:儲存程式(二)之儲存函式簡介MySql儲存函式
- 利用mysqldump只匯出資料庫的表結構、儲存過程和函式MySql資料庫儲存過程函式
- MySQL自定義函式與儲存過程MySql函式儲存過程
- SQL中儲存過程和函式的區別SQL儲存過程函式
- 儲存過程與儲存函式儲存過程儲存函式
- 取IP地址的方法
- MySQL儲存毫秒資料的方法MySql
- mysql儲存過程及日期函式實踐MySql儲存過程函式
- (Oracle)儲存過程、儲存函式和包的相關知識與例項Oracle儲存過程儲存函式
- 深入mysql建立自定義函式與儲存過程的詳解MySql函式儲存過程
- 可以利用的一些系統儲存過程、檢視、函式儲存過程函式