限制一个sqlite表的最大行数

I am looking to implement a sort of 'activity log' table where actions a user does are stored in a sqlite table and then presented to the user so that they can see the latest activity they have done. However, naturally, I don't feel it is necessary to keep every single bit of history, so I am wondering if there is a way to configure the table to start pruning older rows once a maximum set limit is reached.

我希望实现一种“活动日志”表，用户所做的操作存储在一个sqlite表中，然后呈现给用户，以便他们可以看到他们所做的最新活动。但是，自然地，我不认为有必要保留每一段历史，所以我想知道是否有一种方法来配置这个表，以便在达到最大设置限制时开始删除旧的行。

For example, if the limit is 100, and that's how many rows there currently are in the table, when another action is inserted, the oldest row is automatically removed so that there are always a maximum of 100 rows. Is there a way to configure the sqlite table to do this? Or would I have to run a cron job?

例如，如果限制是100，这是表中当前有多少行，当插入另一个操作时，将自动删除最老的行，以便始终有最大100行。是否有一种方法可以配置sqlite表来实现这一点?还是说我必须做一个cron的工作?

Clarification Edit: At any given moment, I would like to display the last 100 (for example) actions/events (rows) of the table.

澄清编辑:在任何给定的时刻，我都希望显示表的最后100个动作/事件(行)。

3 个解决方案

#1

Another solution is to precreate 100 rows and instead of INSERT use UPDATE to update the oldest row.
Assuming that the table has a datetime field, the query

另一个解决方案是预创建100行，而不是插入使用UPDATE更新最老的行。假设该表有一个datetime字段，查询。

UPDATE ...
WHERE datetime = (SELECT min(datetime) FROM logtable)

can do the job.

能做这份工作。

Edit: display the last 100 entries

编辑:显示最后100个条目

SELECT * FROM logtable
ORDER BY datetime DESC
LIMIT 100

Update: here is a way to create 130 "dummy" rows by using join operation:

更新:这里有一种方法可以使用join操作创建130个“虚拟”行:

CREATE TABLE logtable (time TIMESTAMP, msg TEXT);
INSERT INTO logtable DEFAULT VALUES;
INSERT INTO logtable DEFAULT VALUES;
-- insert 2^7 = 128 rows
INSERT INTO logtable SELECT NULL, NULL FROM logtable, logtable, logtable,
   logtable, logtable, logtable, logtable;
UPDATE logtable SET time = DATETIME('now');

#2

There are a couple of ways to constrain a table to 100 rows. (For brevity, 5 rows in the code below.) Tested in SQLite version 3.7.9.

有两种方法可以将表约束为100行。(为了简便起见，下面的代码中有5行。)在SQLite版本3.7.9中测试。

All this code relies on a kind of quirk in the way SQLite handles data type declarations. (It seems quirky to me, anyway.) SQLite lets you insert nonsense like 3.14159 and 'wibble' into a bare integer column. But it lets you insert only integers into a column declared integer primary key or integer primary key autoincrement.

所有这些代码都依赖于SQLite处理数据类型声明的一种怪癖。(不管怎么说，我觉得这很奇怪。)SQLite允许您将3.14159和“wibble”之类的废话插入到纯整数列中。但是它允许您只将整数插入到声明为整型主键或整型主键自动递增的列中。

FOREIGN KEY constraint

外键约束

Use a foreign key constraint to a table of valid id numbers to guarantee that the id numbers are in the range you want. Foreign key constraints work even on autoincrementing columns.

对有效id号表使用外键约束，以确保id号在您想要的范围内。外键约束甚至适用于自动递增的列。

pragma foreign_keys=on;
create table row_numbers (n integer primary key);

insert into row_numbers values (1);
insert into row_numbers values (2);
insert into row_numbers values (3);
insert into row_numbers values (4);
insert into row_numbers values (5);

create table test_row_numbers (
  row_id integer primary key autoincrement,
  other_columns varchar(35) not null,
  foreign key (row_id) references row_numbers (n)
);

insert into test_row_numbers (other_columns) values ('s');
insert into test_row_numbers (other_columns) values ('s');
insert into test_row_numbers (other_columns) values ('s');
insert into test_row_numbers (other_columns) values ('s');
insert into test_row_numbers (other_columns) values ('s');

Sixth insert fails with "Error: foreign key constraint failed".

第六次插入失败，“错误:外键约束失败”。

I don't think Using an autoincrement is entirely safe. On other platforms, a rollback would leave a gap in the sequence. If you don't use an autoincrement, you can safely insert rows by picking the id number out of "row_numbers".

我不认为使用自动增量是完全安全的。在其他平台上，回滚将在序列中留下一个空白。如果不使用自动增量，则可以通过从“row_numbers”中选择id号来安全地插入行。

insert into test_row_numbers values
(
  (select min(n) 
   from row_numbers 
   where n not in 
     (select row_id from test_row_numbers)), 
  's'
);

CHECK() constraint

CHECK()约束

The primary key constraint below guarantees the id numbers will be integers. The CHECK() constraint guarantees the integers will be in the right range. Your application might still have to deal with gaps caused by rollbacks.

下面的主键约束保证id号是整数。CHECK()约束确保整数在正确的范围内。您的应用程序可能仍然需要处理由回滚引起的间隙。

create table test_row_numbers (
  row_id integer primary key autoincrement,
  other_columns varchar(35) not null,
  check (row_id between 1 and 5)
);

#3

You could create a trigger that fires on INSERT, but a better way to approach this, might be to simply have a scheduled job that runs periodically (say once a week) and deletes records from the table.

您可以创建一个在INSERT时触发的触发器，但是更好的方法可能是简单地有一个定期运行的计划作业(比如一周一次)并从表中删除记录。

#1