home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> การเขียนโปรแกรม >> MySQL

MySQL: แปลง IP Address จาก VARCHAR เป็น INTEGER ด้วย INET_ATON

การแปลง IPv4 Address ระหว่างสตริงและตัวเลขด้วย INET_ATON / INET_NTOA

MySQL มีฟังก์ชัน built-in สำหรับแปลง IP address (IPv4) ระหว่างรูปแบบ dotted-quad string และ unsigned integer 32-bit

สร้างตารางและข้อมูล

mysql> create table DemoTable1404 (
    -> IpAddress varchar(40)
    -> );
Query OK, 0 rows affected (1.02 sec)

mysql> insert into DemoTable1404 values('192.168.120.0');
mysql> insert into DemoTable1404 values('192.168.120.20');
mysql> insert into DemoTable1404 values('224.0.0.0');

ข้อมูลต้นฉบับ

+----------------+
| IpAddress      |
+----------------+
| 192.168.120.0  |
| 192.168.120.20 |
| 224.0.0.0      |
+----------------+

คำสั่งแปลงเป็น INTEGER (INET_ATON)

mysql> select inet_aton(IpAddress) from DemoTable1404;

ผลลัพธ์

+----------------------+
| inet_aton(IpAddress) |
+----------------------+
| 3232266240           |
| 3232266260           |
| 3758096384           |
+----------------------+

คำสั่งแปลงกลับเป็น STRING (INET_NTOA)

mysql> select inet_ntoa('3232266240');

ผลลัพธ์

+-------------------------+
| inet_ntoa('3232266240') |
+-------------------------+
| 192.168.120.0           |
+-------------------------+

หลักการคำนวณ

IPv4 = 4 octets (a.b.c.d) → Integer = a×256³ + b×256² + c×256¹ + d×256⁰

192.168.120.0 = 192×16777216 + 168×65536 + 120×256 + 0
             = 3221225472 + 11010048 + 30720 + 0
             = 3232266240

ข้อดีของการเก็บเป็น INTEGER

  • ประหยัดพื้นที่: 4 bytes (INT UNSIGNED) vs 15 bytes (VARCHAR(15))
  • เร็วกว่า: เปรียบเทียบตัวเลขได้โดยตรง ใช้ index ได้ดี
  • Range query: WHERE ip_int BETWEEN inet_aton('192.168.1.0') AND inet_aton('192.168.1.255')
  • Sort ให้ถูกต้องตามลำดับ IP (VARCHAR เรียงแบบ lexicographic ผิด: 192.168.1.10 < 192.168.1.2)

IPv6: INET6_ATON / INET6_NTOA (MySQL 5.6+)

SELECT INET6_ATON('2001:db8::1');   -- VARBINARY(16)
SELECT INET6_NTOA(INET6_ATON('::1')); -- ::1

เก็บเป็น VARBINARY(16) หรือ BINARY(16)

Schema แนะนำ

CREATE TABLE ip_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  ip_int INT UNSIGNED NOT NULL,  -- IPv4
  -- ip_bin VARBINARY(16),        -- IPv6
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ip (ip_int)
);