在下一个“INSERT”语句中使用与“INSERT OR IGNORE”一起插入的ID

Question

我使用sqlite 3.7，并且有一个模式看起来像：

CREATE TABLE IF NOT EXISTS subjects (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
CREATE TABLE IF NOT EXISTS predicates (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
    predicate TEXT NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS objects (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
    object BLOB NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS statements (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
    subject INTEGER NOT NULL REFERENCES subjects(id) ON DELETE RESTRICT, 
    predicate INTEGER NOT NULL REFERENCES predicates(id) ON DELETE RESTRICT, 
    object INTEGER NULL REFERENCES objects(id) ON DELETE RESTRICT
);

要在statements中插入一行，我首先插入或忽略predicate和object，然后检索ids:

INSERT OR IGNORE INTO predicates(predicate) VALUES (?)

SELECT predicates.id FROM predicates WHERE predicates.predicate = ?

最终执行

INSERT INTO statements(subject, predicate, object) VALUES (?, ?, ?)

我的问题是：有没有办法直接一次性完成这些（准备好的）陈述

INSERT OR IGNORE INTO predicates(predicate) VALUES (?);
SELECT predicates.id FROM predicates WHERE predicates.predicate = ?;
INSERT OR IGNORE INTO objects(object) VALUES (?);
SELECT predicates.id FROM objects WHERE objects.object = ?;
INSERT INTO statements(subject, predicate, object) VALUES (?, ?, ?);

Answer 1

last_insert_rowid（）仅在实际插入某行时有效。

您可能总是需要SELECTs，但您可以将它们作为子查询：

INSERT OR IGNORE INTO predicates(predicate) VALUES (?);
INSERT OR IGNORE INTO objects(object) VALUES (?);
INSERT INTO statements(subject, predicate, object)
VALUES (?,
        (SELECT id FROM predicates WHERE predicate = ?),
        (SELECT id FROM objects WHERE object = ?)
       );