SQLite插入或忽略并返回原始rowid_

Question

我花了一些时间阅读SQLite docs，这里有关于堆栈溢出和this thing的各种问题和答案，但还没有得到完整的答案。

我知道没有办法对SQLite执行类似INSERT OR IGNORE INTO foo VALUES(...)的操作并取回原始行的rowid，最接近它的是INSERT OR REPLACE，但这会删除整个行并插入一个新行，从而得到一个新的rowid。

示例表：

CREATE TABLE foo(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    data TEXT
);

现在我可以做：

sql = sqlite3.connect(":memory:")
# create database
sql.execute("INSERT OR IGNORE INTO foo(data) VALUES(?);", ("Some text.", ))
the_id_of_the_row = None
for row in sql.execute("SELECT id FROM foo WHERE data = ?", ("Some text.", )):
    the_id_of_the_row = row[0]

但理想的情况是：

the_id_of_the_row = sql.execute("INSERT OR IGNORE foo(data) VALUES(?)", ("Some text", )).lastrowid

将行插入表并返回rowid，或者忽略已存在的行并只获取rowid的最佳方法是什么（读：最有效）？效率很重要，因为这种情况经常发生。

是否有方法INSERT OR IGNORE并返回与忽略行进行比较的行的rowid？这很好，因为它的效率和插入一样高。

Answer 1

对我来说最有效的方法是在两个单独的步骤中insert or ignore值和selectrowid。我在data列上使用了unique约束来加速选择并避免重复。

sql.execute("INSERT OR IGNORE INTO foo(data) VALUES(?);" ("Some text.", ))
last_row_id = sql.execute("SELECT id FROM foo WHERE data = ?;" ("Some text. ", ))

select语句没有我想象的那么慢。这是由于SQLite自动为unique列创建索引。

Answer 2

INSERT OR IGNORE适用于您不关心记录的标识的情况；在这种情况下，目标只是让一些具有该特定值的记录。

如果要知道是否插入了新记录，必须手动检查：

the_id_of_the_row = None
for row in sql.execute("SELECT id FROM foo WHERE data = ?", ...):
    the_id_of_the_row = row[0]
if the_id_of_the_row is None:
    c = sql.cursor()
    c.execute("INSERT INTO foo(data) VALUES(?)", ...)
    the_id_of_the_row = c.lastrowid

至于效率：当SQLite检查data列中是否有重复项时，它必须执行与对SELECT执行的查询完全相同的查询，并且一旦完成此操作，访问路径就在缓存中，因此性能不应成为问题。在任何情况下，执行两个独立的INSERT/SELECT查询都需要（按任意顺序，您的代码和我的代码都可以工作，但您的代码更简单）。