MySQL - 高效删除除最后N个记录外的所有记录

Question

我最近开始学习SQL，现在被要求对一个表执行简单的删除操作，只保留每个用户的最后100个记录。我正在研究最佳方法（更有效的方式）来完成此操作，并找到了一些可能的解决方案（SQL查询：删除表中除最新N条记录外的所有记录？，从SQL数据库表中删除除前n条之外的全部记录），但是很难选择一个基于效率的方案。因此，我在这里寻求您的帮助。

下面是名为“access”的表格，其中记录着用户的访问日志。

access:
- id (autoincrement) - primary
- userid (integer 11) - key
- refer (varchar 100)
- date (date/time)

我的想法是在同一用户进入系统并插入新的日志之前，每次删除用户ID中的旧记录。

我尝试了下面的代码，但是遇到错误：这个版本的MySQL还不支持'LIMIT & IN/ALL/ANY/SOME subquery'

DELETE FROM
  access
WHERE
  id NOT IN (
    SELECT id FROM access WHERE userid = 10 ORDER BY id DESC LIMIT 100
  );

请问，您能给我提供一些解决方案吗？谢谢！

- Guybrush

---

你是否在记录插入时间的任何列中存储了时间戳？如果是这样，你可以使用这个时间戳值来删除过去的记录。 - srikanth r

我真的不会使用MyISAM。数据完整性比连续的ID更重要。 - Strawberry

抱歉耽搁了，我是在我的时区凌晨2点发布的这个问题... @srikanthr，我有一个自增的ID，所以我认为依靠它会比日期/时间字段更容易... - Guybrush

@Strawberry，引擎是InnoDB，但是我刚开始使用MySQL，所以请提供更多关于您评论的信息，我不理解... - Guybrush

Answer 1

我不是一个 Mysql 专家，不确定为什么它在 Mysql 中不被允许。尝试像这样做：

DELETE a
FROM   access a
       INNER JOIN (SELECT id
                   FROM   access
                   WHERE  userid = 10
                   ORDER  BY id DESC 
                   LIMIT 100) b
               ON a.id <> b.id 

注意：正如评论中所提到的那样，这可能不是高效的。

- Pரதீப்

---

这将执行笛卡尔积。效率不是很高。 - Gurwinder Singh

@GurV - 这并不意味着它没有用处。 - Pரதீப்

@GurV - 他也无法解决这个问题，所以这至少给了他一个解决它的机会。 - Pரதீப்

Answer 2

尝试使用 DELETE JOIN：

delete a from access a left join (
    select id
    from access
    order by id desc limit 1000
) b on a.id = b.id
where b.id is null;

如果您想保留给定用户（例如123）的前1000条记录而不进行删除：

delete a from access a left join (
    select id
    from access
    where userid = 123
    order by id desc limit 1000
) b on a.id = b.id
where b.id is null;

如果您只想删除用户123的行，但保留该用户的前1000行：

delete a from access a left join (
    select id
    from access
    where userid = 123
    order by id desc limit 1000
) b on a.id = b.id
where b.id is null
and a.userid = 123;

- Gurwinder Singh

---

GurV，你的解决方案没有按用户ID过滤，我不知道在哪里实现它... 请问你能更新一下你的答案吗？谢谢。 - Guybrush

谢谢！我会进行测试...请问您对这种方法有何看法：“DELETE FROM access WHERE id NOT IN (SELECT id FROM (SELECT id FROM access WHERE userid = 10 ORDER BY id DESC LIMIT 100) foo) AND userid = 10;” 我在这里找到了：https://dev59.com/ZnRB5IYBdhLWcg3wl4IQ？ - Guybrush

它将执行与我的上一个查询相同的操作。这是一种黑客方式，而不是标准方式。 - Gurwinder Singh

好的，这就是我想知道的。谢谢！ - Guybrush

请问最后一个问题... 为什么您使用了 "where b.id is null"？ - Guybrush

由于我们使用了左连接，联接结果中那些不在子查询部分的行将具有 b.id 为空的值。这是为了模拟“NOT IN”子句。 - Gurwinder Singh