ช่วงนี้รับงานเขียนโปรแกรมเพื่อเก็บข้อมูลอุปกรณ์เน็ตเวิร์ค เพื่อตรวจสอบสถานะการใช้งาน และเก็บสถิติไว้ ข้อมูลอย่างหนึ่งที่เก็บคือ IP Address ของเครื่อง
ปัญหาที่เจอคือเวลาสร้างรายชื่ออุปกรณ์ แล้วต้องการเรียง IP Address จากมากไปน้อย ผลลัพธ์ที่แสดงออกมาจะไม่ถูกต้องตามที่ต้องการ
ตัวอย่างโครงสร้าง table ที่เก็บข้อมูล
mysql> DESCRIBE devices; +-----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+----------------+ | device_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | device_ip | varchar(15) | YES | | NULL | | +-----------+------------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
ตัวอย่างข้อมูลที่เก็บ
mysql> SELECT * FROM devices; +-----------+-----------------+ | device_id | device_ip | +-----------+-----------------+ | 1 | 192.168.3.9 | | 2 | 192.168.10.20 | | 3 | 192.168.1.1 | | 4 | 192.168.200.254 | | 5 | 192.168.5.132 | +-----------+-----------------+ 5 rows in set (0.00 sec)
ตัวอย่างผลลัพธ์จากการเรียง IP Address โดยใช้ ORDER BY
mysql> SELECT * FROM devices ORDER BY device_ip; +-----------+-----------------+ | device_id | device_ip | +-----------+-----------------+ | 3 | 192.168.1.1 | | 2 | 192.168.10.20 | | 4 | 192.168.200.254 | | 1 | 192.168.3.9 | | 5 | 192.168.5.132 | +-----------+-----------------+ 5 rows in set (0.00 sec)
ผลลัพธ์ที่ได้ เนื่องจากฟิลด์ device_ip ถูกกำหนดเป็น VARCHAR ทำให้การใช้ ORDER BY จะเป็นเรียงตามตัวอักษร คือเรียง 1, 2, 3
เพื่อให้การผลลัพธ์การเรียงถูกต้อง จำเป็นต้องแปลง IP Address จาก VARCHAR ให้เป็น ค่าตัวเลข โดยใช้ฟังก์ชั่น INET_ATON ใน MySQL
ดูวิธีการใช้ฟังก์ชั่น INET_ATON ใน MySQL
mysql> HELP INET_ATON; Name: 'INET_ATON' Description: Syntax: INET_ATON(expr)
Given the dotted-quad representation of a network address as a string, returns an integer that represents the numeric value of the address. Addresses may be 4- or 8-byte addresses.
URL: http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html
Examples:
mysql> SELECT INET_ATON('209.207.224.40');
-> 3520061480
การเรียง IP Address โดยใช้ฟังก์ชั่น INET_ATON แปลงค่า
mysql> SELECT * FROM devices ORDER BY INET_ATON(device_ip); +-----------+-----------------+ | device_id | device_ip | +-----------+-----------------+ | 3 | 192.168.1.1 | | 1 | 192.168.3.9 | | 5 | 192.168.5.132 | | 2 | 192.168.10.20 | | 4 | 192.168.200.254 | +-----------+-----------------+ 5 rows in set (0.01 sec)
ผลลัพธ์ที่ได้จะเรียงได้อย่างถูกต้อง
ฟังก์ชั่น MySQL ที่เกี่ยวข้อง
ฟังก์ชั่น INET_ATON แปลงจาก IP Address เป็นตัวเลข
mysql> SELECT INET_ATON('192.168.1.1');
+--------------------------+
| INET_ATON('192.168.1.1') |
+--------------------------+
| 3232235777 |
+--------------------------+
1 row in set (0.00 sec)
ฟังก์ชั่น INET_NTOA แปลงจากตัวเลขเป็น IP Address
mysql> SELECT INET_NTOA('3232235777');
+-------------------------+
| INET_NTOA('3232235777') |
+-------------------------+
| 192.168.1.1 |
+-------------------------+
1 row in set (0.00 sec)
ฟังก์ชั่น PHP ที่เกี่ยวข้อง
ฟังก์ชั่น ip2long แปลงจาก IP Address เป็นตัวเลข
print ip2long('192.168.1.1');
// return 3232235777
ฟังก์ชั่น long2ip แปลงจากตัวเลขเป็น IP Address
print long2ip('3232235777');
// return 192.168.1.1