如何在 SQLite 中使用序列? [英] How can I use sequences in SQLite?

问题描述

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

I'm writing a PHP-based web application that should work with multiple database systems. The most important are MySQL and SQLite but PostgreSQL and others would also be nice. For this I try to use as portable SQL as possible. Where this isn't possible, I have defined some meta words in my queries that are handled by my DB layer and converted to platform-specific SQL commands.

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

I'm now trying to add sequences support. Every DBMS handles sequences differently, there is no common way to write them in SQL. I have read and understood the way PostgreSQL does it. I have found an interesting solution for MySQL that uses MyISAM tables to escape the isolation constraints of a transaction. After all, sequences are not rolled back with the transaction they're used in and that's exactly what I want. Sequences are supposed to be multi-user safe.

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

Now I haven't found a solution for SQLite. It lacks built-in sequence support. It doesn't provide ways to store data outside a running transaction. My current implementation is to lock the table far enough to do a SELECT MAX(...) and use that value. But I want to get rid of that entirely. In SQLite, this approach requires locking the whole database!

有人知道用 SQLite 解决这个问题吗?

Does anybody know a solution for this with SQLite?

推荐答案

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

I would use lastInsertRowID. This returns the rowid of the last inserted data (which equals the INTEGER PRIMARY KEY value of that row). You won't need any sequence then.

这篇关于如何在 SQLite 中使用序列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！