限制 Sqlite 数据库中的记录数
我在这里尝试实现的条件是，sqlite 数据库仅保存最近的 1000 条记录.我有每条记录的时间戳.立即出现的低效逻辑之一是检查记录总数.如果它们超过 1000，那么只需删除掉在外围的那些.
What I'm trying to implement here is a condition wherein a sqlite database holds only the most recent 1000 records. I have timestamps with each record. One of the inefficient logic which strikes right away is to check the total number of records. If they exceed 1000, then simply delete the ones which fall out of the periphery.
但是，我必须对每个 INSERT 执行此检查，这会使事情变得非常低效.
However, I would have to do this check with each INSERT which makes things highly inefficient.
什么是更好的逻辑?我们可以用触发器做点什么吗?
What could be a better logic? Can we do something with triggers?
一些与我想到的相同逻辑的相关问题发布在SO上:-
Some related questions which follow the same logic I thought of are posted on SO:-
从数据库中删除最旧的记录
SQL 查询以删除超过两年的记录
推荐答案
您可以使用隐式 "rowid" 列.
You can use an implicit "rowid" column for that.
假设您不以不同方式手动删除行:
Assuming you don't delete rows manually in different ways:
DELETE FROM yourtable WHERE rowid < (last_row_id - 1000)
您可以使用 API 函数 或作为 max(rowid)
You can obtain last rowid using API function or as max(rowid)
如果您不需要恰好 1000 条记录(例如，只想清理旧记录)，则没有必要在每次插入时都这样做.在您的程序中添加一些计数器并执行清理 f.i.每 100 次插入一次.
If you don't need to have exactly 1000 records (e.g. just want to cleanup old records), it is not necessary to do it on each insert. Add some counter in your program and execute cleanup f.i. once every 100 inserts.
无论如何，您在每次插入或每次选择时支付性能.因此，选择取决于您拥有的更多:INSERT 或 SELECT.
Anyway, you pay performance either on each insert or on each select. So the choice depends on what you have more: INSERTs or SELECTs.
如果你没有那么多的插入来关心性能，你可以使用以下触发器来保持不超过 1000 条记录:
In case you don't have that much inserts to care about performance, you can use following trigger to keep not more than 1000 records:
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 触发器，因此当您批量插入许多记录时，暂时禁用触发器是值得的.
Creating unique index on timestamp column should be a good idea too (in case it isn't PK already). Also note, that SQLITE supports only FOR EACH ROW triggers, so when you bulk-insert many records it is worth to temporary disable the trigger.
如果 INSERT 太多，您在数据库方面无能为力.您可以通过添加诸如 AFTER INSERT WHEN NEW.rowid % 100 = 0 之类的触发条件来降低触发调用的频率.对于选择，只需使用 LIMIT 1000(或创建适当的视图).
If there are too many INSERTs, there isn't much you can do on database side. You can achieve less frequent trigger calls by adding trigger condition like AFTER INSERT WHEN NEW.rowid % 100 = 0. And with selects just use LIMIT 1000 (or create appropriate view).
我无法预测那会快多少.最好的方法就是衡量在您的特定情况下您将获得多少性能.
I can't predict how much faster that would be. The best way would be just measure how much performance you will gain in your particular case.
相关文章