限制 SQLite 中表中的记录数 [英] Limit number of records in a table in SQLite

问题描述

我想在 SQLite 中实现一个最多存储 100 条记录的表.

I want to implement in SQLite a table that stores up to 100 records.

我正在寻找的行为是该表存储记录，当它已满并添加新记录时，它会删除最旧的记录并写入新记录.

The behavior that I am looking for is that the table stores records and when it is full and a new record is added, it deletes the oldest record and writes the new one.

我正在阅读 SQLite 文档，但没有找到我要找的内容.

I was reading through the SQLite documentation but I don't find what I am looking for.

有什么想法吗?

抱歉，我忘了解释一些重要的事情.每条记录都有一个 ID AUTOINCREMENT 字段.这就是我如何知道哪些记录是最先写入的.

Sorry, I forgot to explain something important. Each record has an ID AUTOINCREMENT field. This is how I know which records were written first.

我实在想不出如何只存储 100 条记录的解决方案.

I really can't come up with a solution on how to store only 100 records.

推荐答案

对于小表，无论如何不建议您指定键，因此默认情况下它是在 rowid 上索引的.
因此 rowid 定义了添加记录的顺序.

For small tables it is not recommended that you have specified keys anyway, so by default it is indexed on rowid.
Thus rowid defines the order in which the records were added.

对于添加的每一行:
SELECT rowid FROM TheTable limit 1;
并删除它！
简单本身.
即

For each row added:
SELECT rowid FROM TheTable limit 1;
and delete it!
Simplicity itself.
i.e.

delete from TheTable where rowid in (SELECT rowid FROM TheTable limit 1);

因此，对于在前端添加的每条记录，您在后端删除第一条记录.

Thereby, for each record added at the front end, you remove the first record at the back end.

对于具有一个或多个索引的表，只需忽略它们并使用 rowid 排序.

For tables which do have one or more indices just ignore them and order using rowid.

delete from TheTable where rowid in (SELECT rowid FROM TheTable order by rowid asc limit 1);

回答这个问题让我可以使用这种技术来改变我自己的项目，以限制最近使用"文件列表中的文件数量.

Answering this question allowed me to use this technique to alter my own project, to limit the number of files in a "recently used" file list.

这篇关于限制 SQLite 中表中的记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！