在下一个` `INSERT‘语句中使用插入的ID和’`INSERT‘语句中的插入或IGNORE`

Question

我使用sqlit3.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

只有在实际插入了某些行时，罗威德()才能工作。

您可能总是需要选择，但可以将它们作为子查询执行：

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 = ?)
       );