删除行：没有单个成员拥有超过 x 条记录

Question

我有一个如下结构的表：

CREAT TABLE `member_logins` (
    `id` bigint(10) unsigned not null auto_increment,
    `member_id` mediumint(8) unsigned not null,
    `date_created` datetime not null,
    PRIMARY KEY(`id`)
) ENGINE=InnoDB;

我只想为每个成员保留 50 次登录记录。所以我正在寻找一种方法来删除每个成员的行，任何超过最近 50 行的行。

编辑：我应该提到...这将是一个每晚的 cron 作业。不是需要实时完成的事情。

Answer 1

  DELETE FROM member_logins
  WHERE id in(
       SELECT ID
       FROM (SELECT 
               ID,member_id, 
               IF( @prev <> member_id, @rownum := 1, @rownum := @rownum+1 ) AS  rank, 
               @prev := member_id,date_created 
               FROM member_logins t 
               JOIN (SELECT @rownum := NULL, @prev := 0) AS r 
            ORDER BY t.member_id,t.date_created desc) as tmp
        where tmp.rank > 2)

修复了查询。对样本数据进行了测试，它对我有用。

我加载用于测试的示例数据

id  member_id   date_created
1   1   2/26/2011 12:00:00 AM
2   1   5/26/2011 12:00:00 AM
3   1   4/26/2011 12:00:00 AM
4   2   5/26/2011 12:00:00 AM
5   2   3/26/2011 12:00:00 AM
6   2   4/26/2011 12:00:00 AM

而不是 50，我在查询中使用前 2 行进行测试。

所以我的查询应该删除排名 > 的所有行每个 member_id 组中的 2 个行按 date_created desc 排序。

运行删除查询后的输出：

id  member_id   date_created
2   1   5/26/2011 12:00:00 AM
3   1   4/26/2011 12:00:00 AM
4   2   5/26/2011 12:00:00 AM
6   2   4/26/2011 12:00:00 AM

您可以看到 ID 为 1 和 5 的行已被删除。这些是排名 > 的行。每个member_id组中有2个

Answer 2

这将是每晚的 cron 作业

不 - 不是解决问题的正确方法 - 批处理作业很难管理和测试，并且对于大多数面向互联网的站点来说，没有每日停机时间。最好分散负载，并且仅在需要时运行代码，即当用户登录时......

DELETE FROM member_logins
WHERE member_id=?
ORDER BY id DESC
LIMIT 50,10;

使用member_id 上的索引会更有效

Answer 3

PsuedoCode - 这很可能会进入存储过程或程序逻辑：

Set @MemberCount = Select Count(member_ID) from member_logins where member_id = @memberid

While (@MemberCount >= 50)
Begin
 Set @Id = Select Min(id) from member_logins where member_id = @memberid
 Delete from member_logins where id = @Id
 Set @MemberCount = @MemberCount - 1
End

现在插入新的成员登录记录。

Answer 4

DELETE  m
FROM member_logins AS m
  JOIN ( SELECT DISTINCT member_id
         FROM member_logins
       ) AS md
    ON  md.member_id = m.member_id
    AND m.date_created <
        ( SELECT mx.date_created
          FROM ( SELECT *
                 FROM member_logins
               ) AS mx
          WHERE mx.member_id = md.member_id
          ORDER BY mx.date_created DESC
          LIMIT 1 OFFSET 49
        ) 

子查询

  JOIN ( SELECT DISTINCT member_id
         FROM member_logins
       ) AS md
    ON  md.member_id = m.member_id

可以替换为 member 表中的简单 SELECT：

  JOIN member AS md
    ON  md.member_id = m.member_id