Computer >> Hướng Dẫn Máy Tính >  >> Lập Trình >> MySQL

Phân tích IP address trong MySQL với SUBSTRING_INDEX để đếm theo network

Tách phần network của IP address và đếm tần suất với SUBSTRING_INDEX

Hàm SUBSTRING_INDEX(str, delim, count) trả về chuỗi con trước dấu phân cách delim lần thứ count. Dùng để tách 3 octet đầu của IPv4 (network prefix /24).

1. Tạo bảng và chèn IP addresses

mysql> CREATE TABLE DemoTable (SystemIpAddress TEXT);
mysql> INSERT INTO DemoTable VALUES
    ('192.168.130.67'), ('192.168.130.87'), ('192.168.131.47'),
    ('192.168.134.50'), ('192.168.131.12');

2. Xem dữ liệu

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  |
+-----------------+

3. Truy vấn: Tách 3 octet đầu, nhóm và đếm

mysql> SELECT SUBSTRING_INDEX(tbl.SystemIpAddress, '.', 3) AS Network, COUNT(*) AS Total
    FROM DemoTable tbl
    GROUP BY SUBSTRING_INDEX(tbl.SystemIpAddress, '.', 3)
    ORDER BY Total DESC
    LIMIT 5;

4. Kết quả

+----------------------------------------------+-------+
| SUBSTRING_INDEX(tbl.SystemIpAddress, '.', 3) | Total |
+----------------------------------------------+-------+
| 192.168.130                                  | 2     |
| 192.168.131                                  | 2     |
| 192.168.134                                  | 1     |
+----------------------------------------------+-------+

Giải thích SUBSTRING_INDEX:

  • SUBSTRING_INDEX('192.168.130.67', '.', 3) → '192.168.130' (trước dấu chấm thứ 3)
  • count > 0: Đếm từ trái sang phải
  • count < 0: Đếm từ phải sang trái (ví dụ: -1 lấy octet cuối)

Ứng dụng thực tế:

  • Phân tích traffic theo subnet /24
  • Phát hiện DDoS từ cùng network
  • Geo-location grouping
  • Log analysis, security audit

Mở rộng - Lấy octet cuối (host):

SUBSTRING_INDEX(SystemIpAddress, '.', -1)  -- 67, 87, 47, 50, 12

Lưu ý: IPv6 phức tạp hơn do có :: nén. Cần logic riêng hoặc dùng UDF.