滚动 SQL 表中的行 [英] Rolling rows in SQL table

问题描述

我想创建一个不超过 n 行数据的 SQL 表.插入新行时，我希望删除最旧的行，以便为新行腾出空间.

I'd like to create an SQL table that has no more than n rows of data. When a new row is inserted, I'd like the oldest row removed to make space for the new one.

在 SQLite 中是否有一种典型的处理方法?

Is there a typical way of handling this within SQLite?

是否应该使用一些外部(第三方)代码来管理它?

Should manage it with some outside (third-party) code?

推荐答案

扩展 Alex 的回答，并假设您在名为 serial 的表 t 上有一个递增的、非重复的串行列，可用于确定行:

Expanding on Alex' answer, and assuming you have an incrementing, non-repeating serial column on table t named serial which can be used to determine the relative age of rows:

 CREATE TRIGGER ten_rows_only AFTER INSERT ON t
   BEGIN
     DELETE FROM t WHERE serial <= (SELECT serial FROM t ORDER BY serial DESC LIMIT 10, 1);
   END;

当您的行数少于 10 行时，这将不起作用，并且当 INSERT 将您推到 11 行时，它将 DELETE 最低序列.

This will do nothing when you have fewer than ten rows, and will DELETE the lowest serial when an INSERT would push you to eleven rows.

更新

这是一个稍微复杂的情况，您的表记录列中的行的年龄"，可能包含重复项，例如跟踪插入的 TIMESTAMP 列次.

Here's a slightly more complicated case, where your table records "age" of row in a column which may contain duplicates, as for example a TIMESTAMP column tracking the insert times.

sqlite> .schema t
CREATE TABLE t (id VARCHAR(1) NOT NULL PRIMARY KEY, ts TIMESTAMP NOT NULL);
CREATE TRIGGER ten_rows_only AFTER INSERT ON t
  BEGIN
    DELETE FROM t WHERE id IN (SELECT id FROM t ORDER BY ts DESC LIMIT 10, -1);
  END;

这里我们理所当然地认为我们不能使用 id 来确定相对年龄，因此我们删除了按时间戳排序的前 10 行之后的所有内容.(SQLite 对共享相同 ts 的行强加任意顺序).

Here we take for granted that we cannot use id to determine relative age, so we delete everything after the first 10 rows ordered by timestamp. (SQLite imposes an arbitrary order on rows sharing the same ts).

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