限制sqlite表的最大行数

Question

我希望实现一种“活动日志”表，其中用户执行的操作存储在 sqlite 表中，然后呈现给用户，以便他们可以看到他们所做的最新活动。然而，自然地，我觉得没有必要保留每一个历史记录，所以我想知道是否有一种方法可以配置表以在达到最大设置限制后开始修剪旧行。

例如，如果限制为 100，即表中当前有多少行，则当插入另一个操作时，最旧的行将自动删除，以便始终最多有 100 行。有没有办法配置 sqlite 表来执行此操作？或者我必须运行 cron 作业吗？

澄清编辑：在任何给定时刻，我想显示表格的最后 100 个（例如）操作/事件（行）。

Answer 1

另一种解决方案是预先创建 100 行，并使用 UPDATE 来更新最旧的行，而不是 INSERT。
假设该表有一个datetime 字段，查询

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

就可以完成这项工作。

编辑：显示最后 100 个条目

SELECT * FROM logtable
ORDER BY datetime DESC
LIMIT 100

更新：这里是使用连接操作创建 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'); 

Answer 2

您可以创建一个在 INSERT 上触发的 trigger ，但更好的方法可能是只需有一个定期运行（例如每周一次）并从表中删除记录的预定作业。

Answer 3

有几种方法可以将表限制为 100 行。 （为简洁起见，下面的代码中有 5 行。）在 SQLite 版本 3.7.9 中测试。

所有这些代码都依赖于 SQLite 处理数据类型声明的一种怪癖。 （无论如何，这对我来说似乎很奇怪。）SQLite 允许您将像 3.14159 和 'wibble' 这样的无意义插入到一个裸整数列中。但它允许您仅将整数插入声明为整数主键或整数主键自动增量的列中。

外键约束

对有效 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');

第六次插入失败，并显示“错误：外键约束失败”。

我不认为使用自动增量是完全安全的。在其他平台上，回滚会在序列中留下间隙。如果不使用自动增量，则可以通过从“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() 约束

下面的主键约束保证 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)
);