限制sqlite表的最大行数 [英] Limit an sqlite Table's Maximum Number of Rows

问题描述

我想实现一种活动日志表，其中用户执行的操作存储在sqlite表中，然后呈现给用户，以便他们可以看到他们已经完成的最新活动。然而，自然，我不觉得有必要保持每一个位的历史，所以我想知道是否有一种方法配置表开始修剪较旧的行一旦达到最大设置限制。

例如，如果限制为100，并且这是表中当前有多少行，则当插入另一个操作时，将自动删除最早的行，以便总是有最多100行。有没有办法配置sqlite表来做这个？还是我要运行cron工作？我对sqlite很新，所以很抱歉，如果这是一个愚蠢的问题哈哈。

谢谢我感谢你们能给我的任何帮助。

澄清编辑：在任何给定时刻，我想显示表格的最后100个（例如）操作/事件（行）。<另一种解决方案是预处理100行，而不是 INSERT 使用<$

c $ c> UPDATE 以更新最旧的行。

假设表具有 datetime 字段， / p>

  UPDATE ... 
 WHERE datetime =（SELECT min（datetime）FROM logtable）
  
 
 
 编辑： 
 >显示最后100个条目 
  SELECT * FROM logtable 
 ORDER BY datetime DESC 
 LIMIT 100 
  
 更新：这里是一种创建130 dummyrows by 
  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'）; 
  
 
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.

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? I'm pretty new to sqlite so I'm sorry if this is a stupid question haha.

Thanks I appreciate any help you guys can give me.

Clarification Edit: At any given moment, I would like to display the last 100 (for example) actions/events (rows) of the table.
解决方案
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
UPDATE ...
WHERE datetime = (SELECT min(datetime) FROM logtable)
can do the job.

Edit: display the last 100 entries
SELECT * FROM logtable
ORDER BY datetime DESC
LIMIT 100
Update: here is a way to create 130 "dummy" rows by using join operation:
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'); 


                        
这篇关于限制sqlite表的最大行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！