最主要看的是兩個函式資料的轉換
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; |