从数据库中删除最旧的记录 [英] Delete oldest records from database

问题描述

我有一个包含 1000 条记录的数据库.我正在尝试创建一个 SQL 语句，因此如果记录数增长到 1000 以上，那么最旧的记录将被删除(即超过 1000 条的新记录替换"最旧的记录).我正在使用 SQLite，但我认为通常的 SQL 语法适合这里.

I have a database with 1000 records. I am trying to create an SQL statement so if the number of records grows above 1000, then the oldest records are deleted (i.e. the new records above 1000 'replace' the oldest records). I am using SQLite, but I assume the usual SQL syntax will fit here.

推荐答案

如果你使用自增字段，你可以很容易地写这个来删除最旧的 100 条记录:

If you use an auto-increment field, you can easily write this to delete the oldest 100 records:

DELETE FROM mytable WHERE id IN (SELECT id FROM mytable ORDER BY id ASC LIMIT 100)

或者，如果没有这样的字段，使用ROWID:

Or, if no such field is present, use ROWID:

DELETE FROM mytable WHERE ROWID IN (SELECT ROWID FROM mytable ORDER BY ROWID ASC LIMIT 100)

或者，只留下最近的 1000 条记录:

Or, to leave only the latest 1000 records:

DELETE FROM mytable WHERE ROWID IN (SELECT ROWID FROM mytable ORDER BY ROWID DESC LIMIT -1 OFFSET 1000)

这篇关于从数据库中删除最旧的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！