mysql - 在MySQL中按限制和条件删除记录

Question

我在一个 MySQL 数据库中有一个名为 log 的表。它用于记录用户操作。当它变得很大时，我想删除一些记录。

SELECT `userid`, `timestamp` 
FROM `log`
ORDER `timestamp` ASC

输出是

userid    timestamp
     2    120000
     3    123333
     1    123456
     1    124444
     2    125555
     2    126666
     1    127777
     1    128888
     2    129999
     3    130000
     1    131111

我想做的是 - 我想为每个用户只保留最后 3 条记录。所以，我需要删除每个用户的第 4、5、...、n 条记录。根据上面的例子，desire输出为

userid    timestamp
     3    123333
     2    125555
     2    126666
     1    127777
     1    128888
     2    129999
     3    130000
     1    131111

我知道可以使用 LIMIT 删除记录。

 DELETE FROM `log` LIMIT 3

只删除 3 条记录。根本达不到我想要的结果。

我试过的是

DELETE FROM
`log`
WHERE `userid` IN (
SELECT `userid` FROM (SELECT `userid`, COUNT(1) AS C
FROM `log`
GROUP BY `userid`
HAVING C > 3) CountTable ) LIMIT 3

这不是我想要的。

Answer 1

试试这个:

DELETE l FROM `log` l 
LEFT JOIN (SELECT l.userid, l.timestamp
           FROM (SELECT l.userid, l.timestamp, 
                        IF(@lastUserId = @lastUserId:=userid, @Idx:=@Idx+1, @Idx:=0) rowNumber 
                 FROM `log` l, (SELECT @lastUserId:=0, @Idx:=0) A
                 ORDER BY l.userid, l.timestamp DESC
                ) AS A
           WHERE rowNumber < 3
          ) AS A ON l.userid = A.userid AND l.timestamp = A.timestamp 
WHERE A.userid IS NULL

编辑:

DELETE l FROM `log` l 
WHERE NOT EXISTS (
          SELECT 1
          FROM (SELECT l.userid, l.timestamp, 
                       IF(@lastUserId = @lastUserId:=userid, @Idx:=@Idx+1, @Idx:=0) rowNumber 
                FROM `log` l, (SELECT @lastUserId:=0, @Idx:=0) A
                ORDER BY l.userid, l.timestamp DESC
               ) AS A
          WHERE l.userid = A.userid AND l.timestamp = A.timestamp AND rowNumber < 3
         )

检查 SQL FIDDLE DEMO

输出

| USERID | TIMESTAMP |
|--------|-----------|
|      3 |    123333 |
|      2 |    125555 |
|      2 |    126666 |
|      1 |    127777 |
|      1 |    128888 |
|      2 |    129999 |
|      3 |    130000 |
|      1 |    131111 |