mysql - 从 mysql 表中匹配不同的列 sport 和 dport

Question

我有一个包含 UDP 数据包的表，正在存储以供分析。我想搜索不是由我的服务器请求的数据包。
例如，如果我从源端口 106 和目标端口 32767 收到一个 UDP 数据包，但我从未从我的 IP 地址发送数据包

这是表结构。

mysql>show columns from packets;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| stime  | datetime         | YES  |     | NULL    |       | 
| sip    | int(10) unsigned | YES  |     | NULL    |       | 
| dip    | int(10) unsigned | YES  |     | NULL    |       | 
| sport  | smallint(6)      | YES  |     | NULL    |       | 
| dport  | smallint(6)      | YES  |     | NULL    |       | 
| in_out | tinyint(4)       | YES  |     | NULL    |       | 
| id     | smallint(6)      | YES  |     | NULL    |       | 
+--------+------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> select * from packets limit 5;
+---------------------+------------+------------+-------+-------+--------+-------+
| stime               | sip        | dip        | sport | dport | in_out | id    |
+---------------------+------------+------------+-------+-------+--------+-------+
| 2012-12-03 20:59:45 |  167837953 | 3406507329 |  1029 |    53 |      0 | 32767 | 
| 2012-12-03 20:59:45 | 3406507329 |  167837953 |    53 |  1029 |      1 | 32767 | 
| 2012-12-03 20:59:46 | 3406507329 |  167837953 |    53 |  1029 |      1 | 32767 | 
| 2012-12-03 20:59:46 | 2399874369 |  167837953 |    53 | 32321 |      1 |  3000 | 
| 2012-12-03 21:00:02 | 2064330049 |  167837953 |    53 |  3349 |      1 |  1207 | 
+---------------------+------------+------------+-------+-------+--------+-------+
5 rows in set (0.00 sec)

在上面的例子中，最后两个数据包是我应该能够通过查询找到的未经请求的数据包。基本上，如果我得到一个与 (source,dest,sourceport,dstport,id) 的元组不匹配的数据包 - 我需要在 Mysql 表中列出所有这些行。

谢谢维杰

Answer 1

应该这样做:

SELECT *
FROM packets
LEFT JOIN packets_2 ON (
    packets.sip = packets_2.dip
    AND packets.dip = packets_2.sip
    AND packets.sport = packets_2.dport
    AND packets.dport = packets_2.sport
    AND packets.id = packets_2.id
    AND packets.stime <= packets_2.stime -- I think this makes sense
)
WHERE packets_2.id IS NULL