SQLite 自增非主键字段

是否可以让非主键在每次插入时自动递增?

例如，我想要一个日志，其中每个日志条目都有一个主键(供内部使用)和一个修订号(我希望自动递增的 INT 值).

作为一种解决方法，这可以通过序列来完成，但我相信 SQLite 不支持序列.

解决方案

插入时可以做select max(id)+1.

例如:

INSERT INTO Log (id, rev_no, description)VALUES ((SELECT MAX(id) + 1 FROM log), 'rev_Id', 'some description')

请注意，这将在空表上失败，因为不会有 id 为 0 的记录，但您可以添加第一个虚拟条目或将 sql 语句更改为:

INSERT INTO Log (id, rev_no, description)值 ((SELECT IFNULL(MAX(id), 0) + 1 FROM Log), 'rev_Id', 'some description')

Is it possible to have a non-primary key to be auto-incremented with every insertion?

For example, I want to have a log, where every log entry has a primary key (for internal use), and a revision number ( a INT value that I want to be auto-incremented).

As a workaround, this could be done with a sequence, yet I believe that sequences are not supported in SQLite.

解决方案

You can do select max(id)+1 when you do the insertion.

For example:

INSERT INTO Log (id, rev_no, description) VALUES ((SELECT MAX(id) + 1 FROM log), 'rev_Id', 'some description')

Note that this will fail on an empty table since there won't be a record with id is 0 but you can either add a first dummy entry or change the sql statement to this:

INSERT INTO Log (id, rev_no, description) VALUES ((SELECT IFNULL(MAX(id), 0) + 1 FROM Log), 'rev_Id', 'some description')