SQLite自动增量非主键字段 [英] SQLite auto-increment non-primary key field
问题描述
是否可以在每次插入时自动增加一个非主键?
Is it possible to have a non-primary key to be auto-incremented with every insertion?
例如,我想要一个日志,其中每个日志条目都有一个主键(供内部使用)和一个修订号(我要自动递增的INT值).
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).
作为一种解决方法,可以使用序列完成此操作,但是我认为SQLite不支持序列.
As a workaround, this could be done with a sequence, yet I believe that sequences are not supported in SQLite.
推荐答案
插入时可以选择max(id)+1.
You can do select max(id)+1 when you do the insertion.
例如:
INSERT INTO Log (id, rev_no, description)
VALUES ((SELECT MAX(id) + 1 FROM log), 'rev_Id', 'some description')
INSERT INTO Log (id, rev_no, description)
VALUES ((SELECT MAX(id) + 1 FROM log), 'rev_Id', 'some description')
请注意,这将在空表上失败,因为将没有id
为0的记录,但是您可以添加第一个虚拟条目或将sql语句更改为此:
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')
INSERT INTO Log (id, rev_no, description)
VALUES ((SELECT IFNULL(MAX(id), 0) + 1 FROM Log), 'rev_Id', 'some description')
这篇关于SQLite自动增量非主键字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!