Android的源码，在数据库中的行数限制 [英] Android sqlite, limit number of rows in database

问题描述

我有新的数据进入应用程序的时候，所以我要限制一个表，比方说，100个记录的行数。我将之形容为一个FIFO队列。每当有新的数据（只有几行的时间）进来的时候，在桌子的'底部'旧数据被刷新出来并删除。由于它的FIFO，我不想手动执行排序，然后删除，然后中插入回来。我想一定是要做到这一点廉价的方式，对不对？
谢谢

I have new data coming into the app all the time, so I want to limit the number of rows in a table to, say, 100 records. I would describe it as a FIFO queue. Whenever there new data (just a few rows a time) coming in, old data at the 'bottom' of the table are flushed out and deleted. Since it's FIFO, I don't want to manually perform a sort, then delete, then insert back in. I guess there must be cheap way to do this, right? Thanks

推荐答案

这样的查询会显示所有recors，最新的第一：

A query like this will show all recors, newest first:

SELECT *
FROM MyTable
ORDER BY Date DESC    -- or some autoincrementing ID column

随着OFFSET子句，可以跳过第一个记录。
这意味着你得到所有记录的除了的第一个100的人，即，你会得到应删除这些记录：

With an OFFSET clause, you can skip the first records. This means that you get all records except the first 100 ones, i.e., you get those records that should be deleted:

SELECT *
FROM MyTable
ORDER BY Date DESC
LIMIT -1 OFFSET 100

您可以使用这个在子查询中实际删除记录：

You can then use this in a subquery to actually delete the records:

DELETE FROM MyTable
WHERE ID IN (SELECT ID
             FROM MyTable
             ORDER BY Date DESC
             LIMIT -1 OFFSET 100)

这篇关于Android的源码，在数据库中的行数限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！