database 限制sqlite表的最大行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2035670/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Limit an sqlite Table's Maximum Number of Rows
提问by Jorge Israel Pe?a
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 个（例如）动作/事件（行）。
采纳答案by Nick Dandoulakis
Another solution is to precreate 100 rows and instead of INSERTuse UPDATEto update the oldest row.
Assuming that the table has a datetimefield, the query
另一种解决方案是预先创建100行并且代替INSERT使用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:
更新：这是一种使用连接操作创建 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');
回答by Mitch Wheat
回答by Mike Sherrill 'Cat Recall'
There are a couple of ways to constraina 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 keyor integer primary key autoincrement.
所有这些代码都依赖于 SQLite 处理数据类型声明的一种怪癖。（无论如何，这对我来说似乎很古怪。）SQLite 允许您将 3.14159 和 'wibble' 之类的无意义插入到一个裸整数列中。但它允许您仅将整数插入到声明的列中integer primary keyor integer primary key autoincrement。
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 thinkUsing 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)
);
