f MySQL 的IP 儲存及處理 ~ 迪貝之家

Pages

MySQL 的IP 儲存及處理

 最主要看的是兩個函式資料的轉換

INET_NTOA() IP數字轉文字

INET_ATON() IP文字轉數字

mysql> insert into hostipn select INET_ATON(IP) from hostip;

Query OK, 7 rows affected (0.01 sec)

Records: 7  Duplicates: 0  Warnings: 0


mysql> select ip from hostipn;

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

| ip         |

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

| 2130706433 |

| 3232235522 |

| 3232235530 |

| 3232235778 |

| 3232235786 |

| 4294967295 |

|  352321537 |

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

7 rows in set (0.00 sec)


mysql> select inet_ntoa(ip) from hostipn;

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

| inet_ntoa(ip)   |

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

| 127.0.0.1       |

| 192.168.0.2     |

| 192.168.0.10    |

| 192.168.1.2     |

| 192.168.1.10    |

| 255.255.255.255 |

| 21.0.0.1        |

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

7 rows in set (0.00 sec)


VARCHAR SORTING透過substr_index 的解法

mysql> SELECT ip FROM hostip ORDER BY INET_ATON(ip);

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

| ip              |

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

| 21.0.0.1        |

| 127.0.0.1       |

| 192.168.0.2     |

| 192.168.0.10    |

| 192.168.1.2     |

| 192.168.1.10    |

| 255.255.255.255 |

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

7 rows in set (0.02 sec)


mysql> SELECT ip FROM hostip

    -> ORDER BY

    -> SUBSTRING_INDEX(ip,'.',1)+0,

    -> SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',-3),'.',1)+0,

    -> SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',-2),'.',1)+0,

    -> SUBSTRING_INDEX(ip,'.',-1)+0;

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

| ip              |

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

| 21.0.0.1        |

| 127.0.0.1       |

| 192.168.0.2     |

| 192.168.0.10    |

| 192.168.1.2     |

| 192.168.1.10    |

| 255.255.255.255 |

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

7 rows in set (0.01 sec)


SUBSTRING_INDEX 函視也被用來解決domain name的排序問題

COOKBOOK : 

7.10. Sorting Dotted-Quad IP Values in Numeric Order

Page 263(291)

=================================================

mysql> create table hostipn ( ip INT UNSIGNED);-- INT儲存格式
Query OK, 0 rows affected (0.01 sec)
mysql> desc hostipn
-> ;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| ip | int(10) unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

# hostip.sql
# Sorting IP numbers (leftmost segment most significant)
# This is based on pulling apart the numbers into four segments and
# converting each to a number so that the sort occurs in numeric order
DROP TABLE IF EXISTS hostip;
CREATE TABLE hostip
(
ip VARCHAR(64)
);
INSERT INTO hostip (ip)
VALUES
('127.0.0.1'),
('192.168.0.2'),
('192.168.0.10'),
('192.168.1.2'),
('192.168.1.10'),
('255.255.255.255'),
('21.0.0.1')
;
SELECT * FROM hostip ORDER BY ip;