代码之家 › 专栏 › 技术社区 › Jorge Israel Peña

限制SQLite表的最大行数

database-table sqlite3 database-design sqlite database

Jorge Israel Peña · 技术社区 · 15 年前

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

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

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

3 回复 | 直到 7 年前

Nick Dandoulakis 15 年前

另一种解决方案是预先创建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');

Mitch Wheat 15 年前

你可以创建一个 trigger 这会在插入时触发,但更好的方法可能是简单地安排一个定期运行的作业(比如一周运行一次)并从表中删除记录。

Mike Sherrill 'Cat Recall' 12 年前

有几种方法可以约束一张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');

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

我不认为使用自动增量是完全安全的。在其他平台上,回滚会在序列中留下间隙。如果不使用自动增量,可以通过从“行编号”中选择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)
);