SQLite:使用 ROWID 代替时间戳

Question

我可以使用 ROWID 代替 SQLite 表中的时间戳吗？

我需要获取 SQLite 表的 50 个最新项目，我正在考虑使用单独的时间戳字段，但后来我发现更大的 ROWID 意味着更新的项目，并且 ROWID 已经存在，无需更改架构。

Answer 1

好吧，as it says here (我强调):

The data for rowid tables is stored as a B-Tree structure containing one entry for each table row, using the rowid value as the key. This means that retrieving or sorting records by rowid is fast. Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value.

但是，它也说:

Rowid values may be modified using an UPDATE statement in the same way as any other column value can, either using one of the built-in aliases ("rowid", "oid" or "rowid") or by using an alias created by an integer primary key.

它肯定会更快，但它有点伤害我对“开放”设计的感觉，因为你依赖于实现的特性而不是使其具体化。

话虽如此，同一个链接也这样说:

With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such a column is usually referred to as an "integer primary key". A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER". Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid.

我认为这给了你完美的答案:

在您的表上定义一个 INTEGER 主键并将其用于选择。您将获得使用 ROWID 的速度(因为正如上面所说，它只是一个别名)，并且您将在您正在做的事情的模式中获得可见性。