mysql - 删除mysql中时间戳间隔小于5秒的重复行

Question

我有一台服务器，每次访问时都会将多条记录添加到数据库中。服务器为每次访问执行 2-5 条记录。除了时间戳之外，之间的所有数据都是相同的。我只想为每次访问保留 1 条记录。并删除其他记录。

之前:

+----+----------------------+------+--------+
| id |      timestamp       | user | action |
+----+----------------------+------+--------+
|  1 | 01-01-01-16 03:02:05 |    5 | visit  |
|  2 | 01-01-01-16 03:02:06 |    5 | visit  |
|  3 | 01-01-01-16 03:02:05 |    6 | watch  |
|  4 | 01-01-01-16 03:02:06 |    6 | watch  |
|  5 | 01-01-01-16 03:04:05 |    9 | visit  |
|  6 | 01-01-01-16 03:04:06 |    9 | visit  |
|  7 | 01-01-01-16 03:04:07 |    9 | visit  |
|  8 | 01-01-01-16 03:04:35 |    2 | see    |
+----+----------------------+------+--------+

预期结果:

+----+----------------------+------+--------+
| id |      timestamp       | user | action |
+----+----------------------+------+--------+
| 1, | 01-01-01-16 03:02:05 |    5 | visit  |
| 3, | 01-01-01-16 03:02:05 |    6 | watch  |
| 4, | 01-01-01-16 03:02:06 |    6 | watch  |
| 5, | 01-01-01-16 03:04:05 |    9 | visit  |
| 8, | 01-01-01-16 03:04:35 |    2 | see    |
+----+----------------------+------+--------+

换句话说:如您所见，第 1,2 行是相同的。同一用户，并且间隔只有一秒钟。所以我删除了第 2 行。第 4 行我没有碰，因为它不是访问

我有查询的开头:delete from table where id in(...)

Answer 1

使用join更快地执行查询

DELETE t1 FROM visit t1
INNER JOIN visit t2 ON v1.user = v2.user
WHERE v1.id < v2.id AND
TIMESTAMPDIFF(SECOND, t1.timestamp, t2.timestamp) < 5 AND 
t1.action = 'visit' AND
t2.action = 'visit'