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