MySQL: นับ IP Address เฉพาะ 3 Octet แรกด้วย SUBSTRING_INDEX

คำอธิบาย

ใช้ SUBSTRING_INDEX(str, delim, count) ดึงส่วนของ IP Address 3 Octet แรก (Network Prefix) แล้ว GROUP BY นับจำนวน

ขั้นตอนการสร้างตารางและข้อมูลตัวอย่าง

mysql> create table DemoTable ( 
    SystemIpAddress text ); 
Query OK, 0 rows affected (0.58 sec)

การเพิ่มข้อมูล IP Address

mysql> insert into DemoTable values('192.168.130.67'); 
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable values('192.168.130.87'); 
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('192.168.131.47'); 
Query OK, 1 row affected (0.31 sec)
mysql> insert into DemoTable values('192.168.134.50'); 
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('192.168.131.12'); 
Query OK, 1 row affected (0.21 sec)

แสดงข้อมูลทั้งหมด

mysql> select * from DemoTable;

ผลลัพธ์:

+-----------------+
| SystemIpAddress |
+-----------------+
| 192.168.130.67  |
| 192.168.130.87  |
| 192.168.131.47  |
| 192.168.134.50  |
| 192.168.131.12  |
+-----------------+
5 rows in set (0.00 sec)

คิวรี่ GROUP BY 3 Octet แรก

mysql> select substring_index(tbl.SystemIpAddress, '.', 3), count(*) as Total 
from DemoTable tbl 
group by substring_index(tbl.SystemIpAddress, '.', 3) 
order by Total desc 
limit 5;

ผลลัพธ์

+----------------------------------------------+-------+
| substring_index(tbl.SystemIpAddress, '.', 3) | Total |
+----------------------------------------------+-------+
| 192.168.130                                  | 2     |
| 192.168.131                                  | 2     |
| 192.168.134                                  | 1     |
+----------------------------------------------+-------+
3 rows in set (0.00 sec)

คำอธิบาย SUBSTRING_INDEX

SUBSTRING_INDEX(str, delim, count)
-- count > 0: ส่วนซ้ายของ delimiter ครั้งที่ count
-- count < 0: ส่วนขวาของ delimiter ครั้งที่ |count|

ประยุกต์ใช้

• วิเคราะห์ Network Traffic ตาม Subnet
• จัดกลุ่ม User ตาม IP Range
• Detect DDoS จาก Subnet เดียว

MySQL 8.0+: Generated Column + Index

ALTER TABLE DemoTable 
ADD COLUMN net_prefix VARCHAR(15) 
GENERATED ALWAYS AS (SUBSTRING_INDEX(SystemIpAddress, '.', 3)) STORED,
ADD INDEX idx_net_prefix (net_prefix);