SQLite AUTOINCREMENT 非主键列

Question

我有以下 SQLite 表:

CREATE TABLE podcast_search (
        _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
        search TEXT NOT NULL UNIQUE
)

每当用户在表中插入/更新一行时，我都想在表的末尾对该行进行排序。因此，如果我插入以下值:

_id | search | sort
===================
  1 | foo    | 1
  2 | bar    | 2
  3 | quiz   | 3

然后更新 1来自 foo 的行至 foo2 ，值应如下所示:

_id | search | sort
===================
  2 | bar    | 2
  3 | quiz   | 3
  1 | foo2   | 4

我已经实现了这一点:

CREATE TABLE podcast_search (
        _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
        search TEXT NOT NULL UNIQUE, 
        update_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)
CREATE TRIGGER update_date_update_trigger
    AFTER UPDATE ON podcast_search FOR EACH ROW
        BEGIN
            UPDATE podcast_search
                SET update_date = CURRENT_TIMESTAMP
                WHERE _id = OLD._id;
        END

但是，我的单元测试需要 1000ms为了可靠地排序，在插入/更新操作之间休眠，这种延迟量对于单元测试来说非常烦人。

我以为我可以实现一个矢量时钟，但似乎 AUTOINCREMENT值仅存在于主键列。 SQLite 是否提供任何其他 AUTOINCREMENT或 AUTOINCREMENT - 类似的选项？

我在 Android P 上运行它，但这应该是一个通用的 SQLite 问题。

更新

我现在使用 sort INTEGER NOT NULL UNIQUE列和 SELECT -ing 该列中最大的行并在 INSERT/UPDATE 之前手动递增它:

CREATE TABLE podcast_search (
    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
    search TEXT NOT NULL UNIQUE,
    sort INTEGER NOT NULL UNIQUE
)

SELECT sort from podcast_search ORDER BY sort DESC

任一增量 sort在应用程序代码中，或将其设置为 0
我可以在 TRIGGER 中这样做吗？反而？

Answer 1

I thought I could implement a vector clock instead, but it seems that AUTOINCREMENT values only exist for primary key columns. Does SQLite offer any other AUTOINCREMENT or AUTOINCREMENT-like option?

它们实际上不是 AUTOINCREMENT 值，而是带有 AUTOINCREMENT 的列将是 的别名。行ID 柱子;不是因为 AUTOINCREMENT 已被编码，而是因为 INTEGER PRIMARY KEY 已被编码。

AUTOINCREMENT 所做的所有编码都是添加一个约束，即自动生成的值必须大于任何其他现有或使用的值。只有当 行ID 存在 9223372036854775807 的值。在这种情况下，尝试使用自动生成的 rowid 插入新行(即没有为 rowid 列或其别名指定值)将导致 SQLITE_FULL 错误。

如果没有 AUTOINCREMENT，并且当最高 rowid 为 9223372036854775807(rowid 的最高可能值)时，将尝试使用一个自由值，该值显然低于 9223372036854775807。
SQLite Autoincrement

您可能希望注意链接页面的第一行:-

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

从你的描述看不出有什么需要。

所以你想要的是一种为要排序的列分配一个值的方法，该值比该列的最高当前值大 1，因此它成为最新的排序目的，一个检索 max(the_column) + 1 的子查询会做你想做的事。这可能在 UPDATE、TRIGGER 或 INSERT 中。

rowid = max(rowid) + 1基本上是 SQLite 为 rowid 分配值的方式，除非在将 1 添加到 max(rowid) 和从表 中获得的相应表的值中的较大者时使用 AUTOINCREMENT sqlite_sequence (仅在使用 AUTOINCREMENT 时才会存在)。它正在引用和维护 sqlite_sequence 招致处罚。

例如，您可以使用以下内容(这消除了对附加列和附加索引的需要):-

-- SETUP THE DATA FOR TESTING
DROP TABLE IF EXISTS podcast_searchv1;

CREATE TABLE IF NOT EXISTS podcast_searchv1 (
        _id INTEGER NOT NULL PRIMARY KEY, 
        search TEXT NOT NULL UNIQUE
);

INSERT INTO podcast_searchv1 (search) 
VALUES('foo'),('bar'),('guide')
;

-- Show original data
SELECT * FROM podcast_searchv1;

-- DO THE UPDATE
UPDATE podcast_searchv1 SET search = 'new value', _id = (SELECT max(_id) + 1 FROM podcast_searchv1) WHERE search = 'foo';

-- Show the changed data
SELECT * FROM podcast_searchv1;

结果是:-



进而 :-