我有一个表包含"用户会话",另一个表用于指示系统中的违规行为。一种可能的违规行为是让用户连续登录超过12小时。
表格如下:
notification:
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| host_id | int(11) | NO | MUL | NULL | |
| alert_id | int(11) | NO | MUL | NULL | |
| event_start | datetime | NO | | NULL | |
| time_noticed | datetime | NO | | NULL | |
| info | varchar(45) | YES | | NULL | |
| seen | int(11) | NO | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
login:
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(45) | NO | | NULL | |
| host_id | int(11) | NO | MUL | NULL | |
| start | datetime | NO | | NULL | |
| end | datetime | NO | | NULL | |
| last | int(11) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
我想要的结果表条件是:
login.last=1,login.host_id=X,结束>开始+12小时
并确保我之前没有报告此实例:
login.host_id=notification.host_id,notification.alert_id=Y、 登录开始=notification.event_start,login.username=notification.info
(换句话说,我不想在同一主机上得到同一用户的另一份报告,在同一时间开始的同一违规行为)
SELECT DISTINCT username, start
FROM login
LEFT OUTER JOIN notification ON
(login.host_id = notification.host_id
AND login.start = notification.event_start
AND login.username = notification.info)
WHERE
end>DATE_ADD(start,INTERVAL 12 HOUR)
AND login.host_id=$host_id
AND last=1
AND login.id IS NULL
此查询应该使用"不存在"类型的搜索(请尝试EXPLAIN),这有利于查询性能。也许值得在通知'host_id,event_started,info'上设置UNIQUE KEY,并使用INSERT IGNORE