SQL 从数据库中删除最旧的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3667085/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete oldest records from database
提问by phm
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.
我有一个包含 1000 条记录的数据库。我正在尝试创建一个 SQL 语句，因此如果记录数增长到 1000 以上，那么最旧的记录将被删除（即超过 1000 条的新记录“替换”最旧的记录）。我正在使用 SQLite，但我认为通常的 SQL 语法适合这里。
回答by AlexanderMP
If you use an auto-increment field, you can easily write this to delete the oldest 100 records:
如果您使用自动增量字段，您可以轻松地编写以下内容来删除最旧的 100 条记录：
DELETE FROM mytable WHERE id IN (SELECT id FROM mytable ORDER BY id ASC LIMIT 100)
Or, if no such field is present, use ROWID:
或者，如果不存在此类字段，请使用ROWID：
DELETE FROM mytable WHERE ROWID IN (SELECT ROWID FROM mytable ORDER BY ROWID ASC LIMIT 100)
Or, to leave only the latest 1000 records:
或者，只留下最近的 1000 条记录：
DELETE FROM mytable WHERE ROWID IN (SELECT ROWID FROM mytable ORDER BY ROWID DESC LIMIT -1 OFFSET 1000)
回答by StuartLC
Assuming that your table has a Primary Key and a column with a timestamp indicating when the record was inserted), you can use a query along the lines of
假设您的表有一个主键和一个带有时间戳的列，该列指示插入记录的时间），您可以使用以下行的查询
delete from tableToDeleteFrom
where tablePK in
(select tablePK
from tableToDeleteFrom
where someThresholdDate <= @someThresholdDate)
回答by Hagai
For delete all records except the first record (min/max id) you can use:
要删除除第一条记录（最小/最大 ID）以外的所有记录，您可以使用：
SET @ls AS INT
SELECT @ls = MIN(id) FROM DATA
DELETE FROM DATA WHERE id <> @ls
