限制SQLite表的最大行数

Question

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

举个例子，如果限制是100，并且当前表格中有100行记录，当插入另一个操作时，最旧的行将自动删除，以便始终保持最多100行。是否有一种方法来配置sqlite表格来完成这个功能？或者我是否需要运行一个cron作业？ 澄清编辑：在任何给定的时刻，我想显示表格的最后100个（例如）操作/事件（行）。

- Jorge Israel Peña

Answer 1

另一种解决方法是预先创建100行，而不是使用INSERT，使用UPDATE来更新最旧的行。
假设表中有一个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'); 

- Nick Dandoulakis

---

我猜我漏掉了什么，但这样做不是总是只更新最后一行吗？我想我在原帖中忘记提到，但我想展示例如最近的5个操作/事件，无论它们有多旧，而不仅仅是最后一个。谢谢。 - Jorge Israel Peña

@Blaenk：假设您的更新将每行的日期时间字段设置为当前时间，则应按时间顺序循环遍历这些行。 - Drew Hall

哦，然后当然还要更新日期时间字段，这样它就成为最新的行了，对吧？我明白了。嗯，这很有趣。有没有一种简单的方法预先创建一定数量的“虚拟”行？ - Jorge Israel Peña

@Blaenk，我不知道你是否可以在SQLite中使用查询插入100个虚拟行。当然，你可以用代码来实现。关于获取最后N行，请看我的编辑。 - Nick Dandoulakis

虽然您的虚拟行生成功能运行良好，但它将为所有行创建相同的日期，破坏了仅更新最旧记录的方法（使用您的查询将更新所有记录）。对我来说，像UPDATE logs SET time = DATETIME(DATETIME('now'), '+'||rowid||' minutes');这样的语句解决了这个问题。 - tribe84

非常感谢，这个答案对我很有帮助 - 我正在寻找在 SQLite 中创建默认值的虚拟行。 - Vada Poché

Answer 2

你可以创建一个触发器，当有INSERT时触发，但更好的方法可能是定期运行（例如每周一次）的计划任务，从表中删除记录。

- Mitch Wheat

---

哦，好的，这就是我想到的（cron），但我想确保没有针对这种情况的内置机制。 - Jorge Israel Peña

如果您熟悉触发器，能否提供一个在此情况下有用的示例？也就是说，如果它们不太复杂，如果太复杂，不用担心。 - Jorge Israel Peña

Answer 3

有几种方法可以将表格限制为100行。下面的代码仅显示5行，已在SQLite版本3.7.9中测试。

所有这些代码都依赖于SQLite处理数据类型声明的一种怪异方式（至少对我来说是怪异的）。SQLite允许您插入无意义的内容，例如3.14159和“wibble”到裸整数列中。但是只允许您将整数插入到声明为“integer primary key”或“integer primary key autoincrement”的列中。

外键约束

使用外键约束到有效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)
);

- Mike Sherrill 'Cat Recall'