如何在 SQLite 中使用序列？

Question

我正在编写一个基于 PHP 的 Web 应用程序，该应用程序应该与多个数据库系统一起使用。最重要的是 MySQL 和 SQLite，但 PostgreSQL 和其他也不错。为此，我尝试尽可能使用可移植的 SQL。如果这是不可能的，我在查询中定义了一些元词，这些元词由我的数据库层处理并转换为特定于平台的 SQL 命令。

我现在正在尝试添加序列支持。每个 DBMS 处理序列的方式都不同，没有通用的方法用 SQL 编写它们。我已经阅读并理解了 PostgreSQL 的做法。我发现了一个有趣的 MySQL 解决方案它使用 MyISAM 表来逃避事务的隔离约束。毕竟，序列不会随着它们所使用的事务而回滚，而这正是我想要的。序列应该是多用户安全的。

现在我还没有找到SQLite的解决方案。它缺乏内置的序列支持。它不提供在正在运行的事务之外存储数据的方法。我当前的实现是将表锁定得足够远以执行 SELECT MAX(...) 并使用该值。但我想完全摆脱它。在 SQLite 中，这种方法需要锁定整个数据库！

有人知道 SQLite 的解决方案吗？

Answer 1

只需创建一个常规计数器表即可。创建序列 foo 时，执行 do

create table foo(value int);
insert into foo(value) values(0);

然后，当请求下一个计数器值时，执行 do

update foo set value=value+1;

虽然事务中止时会回滚，但它是多用户安全的：没有两个用户会提交相同的号码。 sqlite 使用数据库锁实现并发，因此第二个写入器无论如何都会阻塞（不仅仅是因为序列更新，还因为它想要进行的其他更改）。

Answer 2

我会使用lastInsertRowID。这将返回最后插入数据的 rowid（等于该行的 INTEGER PRIMARY KEY 值）。
那么你就不需要任何序列了。

Answer 3

您可以模拟一个序列，首先创建一个具有自动递增主键列的表。

CREATE TABLE G_SEQUENCE(
   id INTEGER AUTO_INCREMENT PRIMARY KEY
);

将所需序列数插入该表 n 次。如果您担心性能，那么您可以一次缓存 100 个序列。

BEGIN TRANSACTION;
insert into G_SEQUENCE default values; 
...
insert into G_SEQUENCE default values; 
COMMIT;

如果您的序列表变得太大（这是一个很好的问题），您可以轻松获取最大 id，并删除小于它的所有内容。