限制Sqlite DB中记录的数量

Question

限制Sqlite DB中记录的数量

sqlsqlitetriggers

7

我想要实现的条件是，sqlite数据库仅保留最近的1000条记录。每个记录都有时间戳。

一种显而易见的低效逻辑是检查总记录数。如果超过1000，则删除超出范围的记录。

然而，我必须在每次插入时进行此检查，这使得事情变得高度低效。

更好的逻辑是什么？我们可以用触发器做些什么吗？

一些相关问题遵循相同的逻辑，我在SO上发布了它们：- 从数据库中删除最旧的记录 SQL查询以删除两年前的记录

- Rohan

2

你有没有考虑到这样一种情况：当表格达到1000条记录时，每次插入都必须执行删除和最终计数（someId），这是很耗费资源的。更好的方法是让记录增长，并定期清理旧记录。 - danisius

沿着这条线，维护小表大小的定期作业可能符合您的要求。此外，考虑将记录移动到归档表而不是删除它们。你永远不知道什么时候需要查看它们。 - Dan Bracuk

@danisius - 你提出的情况在任何时候都是可能的，因为一旦记录数量达到1000条，每次插入都会出现这种情况。 - Rohan

@DanBracuk 我肯定可以考虑归档，这也是一个好主意，但我正在努力寻找一种在数据库中而不是通过连接到数据库的应用程序来实现它的方法。我正在使用Flex，所以如果我倾向于使用基于计时器的定期作业来尝试归档记录，那将使事情变得更糟。 - Rohan

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- nevermind · Accepted Answer

您可以使用隐式“rowid”列来实现这一点。

假设您不以不同的方式手动删除行：

DELETE FROM yourtable WHERE rowid < (last_row_id - 1000)

您可以使用API函数或max(rowid)来获取最后一行的rowid。

如果您不需要确切地存储1000条记录（例如只想清理旧记录），则无需在每次插入时执行此操作。在程序中添加计数器，例如每100次插入执行一次清理。

更新：

无论是在每次插入还是在每次选择时，都会付出性能成本。因此，选择取决于您有更多的是INSERTs还是SELECTs。

如果您不太关心性能，则可以使用以下触发器来保留不超过1000条记录：

CREATE TRIGGER triggername AFTER INSERT ON tablename BEGIN
     DELETE FROM tablename WHERE timestamp < (SELECT MIN(timestamp) FROM tablename ORDER BY timestamp DESC LIMIT 1000);
END

在时间戳列上创建唯一索引也是个好主意（如果它还不是PK的话）。另外请注意，SQLITE仅支持FOR EACH ROW触发器，因此在批量插入许多记录时，暂时禁用触发器是值得的。

如果有太多的INSERT操作，则数据库方面无法做太多事情。您可以通过添加触发器条件（例如AFTER INSERT WHEN NEW.rowid % 100 = 0）来实现较少的触发器调用。对于选择操作，只需使用LIMIT 1000（或创建适当的视图）。

我无法预测这将快多少。最好的方法就是测量您特定情况下的性能增益。